A great one is Hotel Dusk: Room 215. It's extremely text/plot heavy but its one of the best point and click "adventure" type DS games I've played. It's a mystery game and you play an ex-detective who ends up staying at the Hotel Dusk... A hotel that has, as it turns out, a lot of mysterious connections to your characters past and a lot of weird superstitions surrounding around it as well (like the room that supposedly grant's wishes).t Its has this whole "noir" look and feel going on and you play the game holding the DS on its side as if you're reading a book... anyway, great game but hardly anyone has heard of it.
Another good one if you like Logic puzzles is Professor Clayton and the Curious Village... It's point and click mystery game but the you advance the game through logic puzzles. Usually these logic puzzles are completely unrelated to the game though and its usually stuff like "Well if you can figure out this puzzle my grandfather told me, I'll tell you where she went". I wish the puzzles had more to do with the game. That said though, the story is great and if you like logic puzzles, get it
Edit:
Hotel Dusk Trailer for those who have never heard of it