I cannot name the 3 DMVs I use on a regular basis, but….

Did you recognize Jen McCown’s  blog post “Are you a functional DBA” last week? Well, without John Sansom’s Something for the weekend I’d probably missed Jens post.

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:

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

SSMSAddin_1

Here you can define your snippets and put your sql code, custom scripts etc. pp. in it.

SSMSAddin_2

If I now want to open the Diagnostic queries in my query window, I just type glenn28_new and I get the whole script:

SSMSAddin_4

I’m calling the Blitz script and Who is Active in a similar way.  Wia for example just opens
EXEC sp_whoisactive
,

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.

Advertisements

Über Dirk Hondong

A MS server and ms sql server admin guy from germany. want to improve my skills a little bit, sharing my daily experience
Dieser Beitrag wurde unter DMVs, SQL Server, SSMS Tools Pack abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Eine Antwort zu I cannot name the 3 DMVs I use on a regular basis, but….

  1. Jen McCown schreibt:

    Jen here. As I’ve said, I don’t expect every DBA to be able to answer all the questions. But midlevel DBAs should be able to answer many of them. And for the record, I would’ve liked your answer to the DMV question, had you given it in an interview….then we would’ve expolored how you use those scripts and when, what you do with the information, and so on…

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s