A Microsoft SQL Server DMV/DMF cheat-sheet with a list of DMV&#39;s every SQL DBA should know...

Friday, May 25, 2012

A Microsoft SQL Server DMV/DMF cheat-sheet with a list of DMV's every SQL DBA should know...

SQL Canada (@SQLCAN) - A Microsoft SQL Server DMV/DMF Cheat-Sheet

I am asked this question by Microsoft SQL Server customers again and again: “What dynamic management views (DMV) or dynamic management functions (DMF) should I use?” The short answer is, as many as you need. The detailed answer is, well, it depends. There are well over 150 DMV/DMFs spread across 20 some categories in 2012 now, and it is a daunting challenge trying to remember all these. I don’t think I have used all of these, I don’t think I ever will. The use of these really stems from what issues you are trying to deal with or what information you need from SQL Server. Since the use for most DMVs is scenarios based, I am listing here DMV/DMFs that I think every DBA should get familiar with. Now I am not saying this is an exhaustive list, but I find it a good starting point.

...

Using DMV/DMFs

Instead of just giving you random SQL statements against the DMV/DMFs listed above, I am going to walk through the DMV and build the corresponding SQL statement based on information I want to collect.

For starters we will look to answer the following question: who is connected to SQL Server? We have in particular two DMVs to give us that information; sys.dm_exec_connections and sys.dm_exec_sessions. The sys.dm_exec_connections returns information for only user connections, whereas sys.dm_exec_sessions returns information for both user connections and system sessions. There is one-to-one relationship between the two DMVs. When looking at just the sys.dm_exec_sessions we can identify the system sessions by looking at the session_id column; values greater than or equal to 51 are user connections and be low are system processes.

...

..."