|
|
|
|
Adam Machanic, SQL Server Practice Lead for The Pythian Group, shares his experiences with programming, performance tuning, and optimizing SQL Server 2000, 2005, and 2008, in conjunction with related technologies such as .NET.
As with all of the blog posts I keep meaning to write -- I keep a list and given the infrequency with which I've been posting lately, it's getting quite large -- this script has been on the queue for quite some time. So here I find myself with a spare moment right on the cusp of the new year, and figured what better way to end the year than with a script that, at least for me, has been quite useful these last few months. The driving force behind my writing this script is that I found myself endlessly calling sp_who2 'active' to see who was doing what on servers I needed to take a look at. Then I would have to sort through the results, and end up calling DBCC INPUTBUFFER to take a look at the SQL being used. This was a serious pain, and I finally caved a few months back and decided to end the madness once and for all with the help of some DMVs. The following script primarily uses the sys.dm_exec_requests view, and finds all "active" requests -- i.e., those that are running, about to start running, or suspended. It also finds some other useful information, including the host name, login name, the start time of the batch, and whether or not the batch is currently blocked. In the outer query I use the sys.dm_exec_sql_text function to get the text of the SQL that all of the active requests are running, in addition to the SQL being run by the blocking sessions, if applicable. This way I don't have to do two lookups to chase down what's blocking what. You'll notice that I use FOR XML PATH in the subqueries that pull the SQL text. This gives us a nice little bonus: instead of copying the text out of the cell in SSMS and pasting it somewhere else, you can simply click on it -- and it maintains whatever formatting, including white space and carriage returns, that it originally had. This is much, much nicer than getting the batch on a single line. The only problem is that certain characters, such as greater-than and less-than, get "entitized" when the text is converted to XML. This means that some queries won't be able to be run without a bit of editing. A small price to pay for nicer output, in my opinion. If anyone out there has a solution for the entitization, please let me know! The only way I know to solve it is to convert back to VARCHAR, and that defeats the whole purpose... Anyway, thanks all for a great 2007. Here's to an even better 2008! Without further ado, the script: SELECT
x.session_id,
x.host_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
(
SELECT
text AS [text()]
FROM sys.dm_exec_sql_text(x.sql_handle)
FOR XML PATH(''), TYPE
) AS sql_text,
COALESCE(x.blocking_session_id, 0) AS blocking_session_id,
(
SELECT
p.text
FROM
(
SELECT
MIN(sql_handle) AS sql_handle
FROM sys.dm_exec_requests r2
WHERE
r2.session_id = x.blocking_session_id
) AS r_blocking
CROSS APPLY
(
SELECT
text AS [text()]
FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
) AS blocking_text
FROM
(
SELECT
r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id,
SUM(r.reads) AS totalReads,
SUM(r.writes) AS totalWrites,
SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
WHERE r.status IN ('running', 'runnable', 'suspended')
GROUP BY
r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id
) x
Enjoy!
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Adam Machanic
Adam Machanic is the SQL Server Practice Lead for The Pythian Group, a leading provider of wholly and partially outsourced remote administration of the enterprise database tier, for over 100 customers around the world. He has been involved in dozens of SQL Server implementations for both high-availability OLTP and large-scale data warehouse applications, and has optimized data access layer performance for several data-intensive applications. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "Expert SQL Server 2005 Development" (Apress, 2007) and "Inside SQL Server 2005: Query Tuning and Optimization" (Microsoft Press, 2007). Adam regularly speaks at user groups, community events, and conferences on a variety of SQL Server and .NET-related topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified IT Professional (MCITP).
|
|
|
|
|