Yes, stagnant does denote water that is dirty. I would expect a stagnant pond to be covered in algae or fungal blooms. I would expect the water to look somewhat brown from sediment. I would expect bugs. A dead lake would mean that fish can no longer survive in it, so I would expect no fish, no turtles, no frogs.
I would not expect the water to look clean and nice like the lake in the picture you included. I would describe the lake in the picture as still, calm, clean, and clear. If there truly is no river bringing fresh water into the lake, you could call it "a lake with no inlet". In that case, I would expect that the water is coming from an underground spring, so we could call it a "spring-fed lake". "Spring-fed" has positive connotations, and if I was told we were going to a spring-fed lake, I would expect to see a lake like the one in your photograph.