In all the talk, the key reason to instrument is being overlooked, which is the after-the-fact benefit. Tools may help anticipate or identify current problems, but they are of little...
We're moving away from stored procedures in part because there is no such feature. There's simply no way of identifying problems with the current system.
Go ahead and set your two terabyte database to autogrow by 10% and enjoy the wonderful performance this video suggests (as you look for a new job)...I dare you.