Jen asked a lot of interesting questions and I have to confess, I couldn’t really answer all of them without hesitation. One of her questions/tasks was to name three DMVs you use on a regular basis. Luckily I wasn’t the only one who said: “I can’t”. It was Brent Ozar (blog | twitter), the well-known community-contributor, MVP and MCM.
So, his reply regarding the DMVs was:
Name three DMVs you use on a regular basis. – I can’t. I’ve had the same scripts in use for years, and even though I use it at least three times a day, I can never remember if it’s sys.dm_db_index_usage_from_behind or whatever. That’s what script files are for. If you’re typing the DMV name by hand multiple times per day, you’re doing it wrong.
This answer made me feel a bit better and I got the idea to write this post.
To answer now Jen’s question: I cannot name the three DMVs I use regularly, but I can name the three scripts/procedures I’m using. These are:
- Glenn Berry’s Diagnostic Information queries (you’ll find them on his blog)
- Brent Ozar’s Blitz Script
- Adam Machanic’s Who is Active
To have Glenn’s script quick at hand or to call the two procedures immediately, I use the SQL Snippets functionality from the SSMS Tools Pack by Mladen Prajdić (blog|twitter). For me, this is one of the most useful features, since I can call complete code blocks just by typing in a shortcut and hit enter.
If you have the SSMS Tools Pack installed, just go to the SQL Snippets option
Here you can define your snippets and put your sql code, custom scripts etc. pp. in it.
If I now want to open the Diagnostic queries in my query window, I just type glenn28_new and I get the whole script:
I’m calling the Blitz script and Who is Active in a similar way. Wia for example just opens
but with wia2 I get
EXEC sp_whoisactive @show_sleeping_spids = 2
This is just a quick example how I call my scripts I use on a regular basis.