One of the most common problems facing a DBA or developer in troubleshooting performance is identifying what processes, and by association, what queries are causing the slowdown or symptoms. The first step used to be run SP_Who or SP_WHO2 and pour through the data. There are also a number of great customizations to this stored procedure available across the internet.

This can be time consuming, however, both due to the volume of data, and the potential extra pressure to TEMPDB if run during an already critical time (yes SP_Who2 sorts in TempDB). For those of you who have not yet explored the new Dynamic Management Views in SQL Server 2005 and 2008, they are by far my favorite new feature in 2005 and my second favorite feature in SQL Server 2008. The new SQL debugger is my favorite in 2008. The volume and diversity of system, performance, and configuration information that has been pre-catalogued for us as database professionals is terrific, especially when you need this information quickly and concisely.

I’m sure most of you have heard about these views by now and have either embraced them or are “getting to it in between fires”. I have in this post the first of several installments designed to introduce you to these views and where they can help you. Let’s evolve from our days of SP_Who2 and move forward using the views and a new T-SQL feature called CROSS APPLY. Remember, all things old will eventually be deprecated.

The goal of this example is simple. We are going to run two sample queries and then see how we can quickly gather information about all of our running processes. There is one DMV and one DMF (Dynamic Management Function) we will use in this example.

The sys.dm_exec_Requests view brings back much of the information in SP_who and more. We will limit that information to the most applicable as you will see below. The functions sys.dm_exec_sql_text will be “Cross Applied” to return the text of the process in question. Let’s take a look at an example

--View SQL Statement Text for Each Request

SELECT Session_ID, Status, Database_Id, Stat.Text

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle) as Stat

I recommend using a waitfor delay in another connection to illustrate the example if you are testing this on a machine with minimal or no other connection. Something simple like this will suffice.

--WAITFOR Query to Show Multiple Statements

SELECT *

FROM sys.databases

WAITFOR DELAY '00:00:45' -- Will Delay 45 Seconds

Applying this simple query will give you instant insight into what is going on in your environment and what queries are running. If you’re like most great DBAs, you’re already familiar with some of the problem children and potentially impactful queries running in your environment. This should help as a quick way to find out if your hunch is right.

I will be expanding on this topic as we move through the different sets of DMV’s over the coming weeks. Stay tuned and email me with any questions at ajorgensen@pragmaticworks.com