Most of what I wanted to say has been covered by Rai, Karcist and tiger. Overall I'd say low-A to high-B tier. Figure I should add in some things, though.
Start with using it as a follow-up move. Bomb > D-tilt, FF F-air > D-tilt, and jab > D-tilt at low % all work fairly well. D-tilt in itself isn't a very good KO move, but it isn't below average. Fresh ones can take out enemies, while overused ones act as a gtfo move. Against any heavy characters and well-trained-in-DI players, it won't kill before at least 160%(200 is over exaggerating, methinks, unless you literally have done nothing but depend on it the whole match), and on lightweights who aren't Squirtle(fire resistance leaves U-tilt and Charge Shots as better options), depending on all factors, may not KO until well over 130%. Whilst F-smash is a better KO move, I find it harder to land in most cases. Charge Shot > U-tilt > D-tilt > F-smash > B-air in terms of how easy it is to land, in my opinion.
Using D-tilt on low ceiling stages depend on who works better on the stage. My usual CPs consist of Brinstar, Halberd, and Battlefield. Obviously Snake is good on Halberd, several characters are good on BF, and Meta Knight and Wario work well on Brinstar. It boils down to your playstyle in the end. I've taken Snakes to Halberd and Warios/MKs to Brinstar and BF and came out on top due to how I played. If you can learn to balance out your D-tilt with your other KO and damage-racking moves, you should be good to go in situations that demand D-tilt.
As Rai said, it's good for edgeguarding. Just as an example, I've played a Lucario who ***** me everywhere but the ledge. I ended up winning by Z-airing every attempt to recover over the stage, then catching him with D-tilt as he went for the ledge. It is also a very good move on stages characters can rise from, like Delfino, Halberd, Brinstar and pretty much any stage with platforms. The range is fairly good and most people won't suspect it unless you start spamming it. I mix in D-smashes to juke them, works wonders unless it's a MK, in which case I dance around with Bombs and throw out more D-tilts/D-airs.