By: Jeremy Kadlec | Updated: 2007-10-31 | Comments | Related: > Dynamic Management Views and Functions
Problem
When troubleshooting a potential SQL Server performance problem, it is difficult to know if the code is problematic without being able to review all of the code. You could ask the Developer for the code you suspect, run Profiler to capture code, leverage a third party tool for the data collection or try to leverage the native T-SQL commands to review the code. Historically tracking down the code was available with sp_who2, DBCC INPUTBUFFER and fn_get_sql. With the introduction of SQL Server 2005, is a simpler means with more bells and whistles available with the dynamic management views and functions?
Solution
In earlier tips (SQL Server statements currently running with fn_get_sql and SQL Server Command Line Tools To Manage Your Server), we outlined how SQL Server 2000 has two main mechanisms for finding the input for a spid (system process identifier). These commands are DBCC INPUTBUFFER and fn_get_sql. First, DBCC INPUTBUFFER has an inherent issue in SQL Server 2000 because the limitation on the input buffer was 255 and most code exceeds that length. With SQL Server 2005, the EventInfo column returned was expanded to 4000, which may be greater than some of the code that is being issued against your SQL Servers and fulfill your need, but probably not all code. Second, with fn_get_sql in SQL Server 2000 the entire input buffer was returned. So if pages of code were issued, then pages of code would be returned with the fn_get_sql command. This was a very handy command that became an "ace in the hole" if you were having problems tracking down problematic code. Let's take a look at 2 examples:
|
SQL Server 2000 Examples |
|
DBCC INPUTBUFFER |
| DBCC INPUTBUFFER (<spid>) GO |
|
fn_get_sql |
| DECLARE @Handle binary(20)
SELECT @Handle = sql_handle SELECT * |
| *** NOTE *** - In both of these examples, replace <spid> with the spid number you are interested in analyzing. |
When moving forward with SQL Server 2005, DBCC INPUTBUFFER will continue to be supported and was even enhanced as mentioned above. Although the fn_get_sql command is still available in SQL Server 2005 it is marked as deprecated in a future release of SQL Server and needs to be converted to use the sys.dm_exec_sql_text dynamic management view. Let's dig into the sys.dm_exec_sql_text dynamic management view to take a look a some of the functionality that has been historically available as well as some new opportunities.
|
All Buffered Code For A Single Active SPID |
| DECLARE @spid int SET @spid = <spid> SELECT er.session_id,er.status, er.command, DB_NAME(database_id) AS 'DatabaseName', user_id, st.text FROM sys.dm_exec_requests AS er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st WHERE er.session_id = @spid; GO |
|
*** NOTE *** - Replace <spid> with the spid number you are interested in analyzing. |
|
All Buffered Code For All Active Sessions |
| SELECT er.session_id, er.status, er.command, DB_NAME(database_id) AS 'DatabaseName', user_id AS 'UserName', SUBSTRING(st.text, (er.statement_start_offset/2)+1, ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_requests AS er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st; GO |
|
Buffered Code With Top 5 Max Worker Time |
| SELECT TOP 5 qs.max_worker_time, DB_NAME(st.DBID) AS 'DatabaseName', st.ObjectID, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.max_worker_time DESC; GO |
|
Buffered Code With Top 5 Max Elapsed Time |
| SELECT TOP 5 qs.max_elapsed_time, DB_NAME(st.DBID) AS 'DatabaseName', st.ObjectID, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.max_elapsed_time DESC; GO |
|
Buffered Code With Top 5 Max Logical Writes |
| SELECT TOP 5 qs.max_logical_writes, DB_NAME(st.DBID) AS 'DatabaseName', st.ObjectID, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.max_logical_writes DESC; GO |
|
All Buffered Code With Top 5 Physical Reads |
| SELECT TOP 5 qs.max_physical_reads, DB_NAME(st.DBID) AS 'DatabaseName', st.ObjectID, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.max_physical_reads DESC; GO |
Next Steps
- When you have the need to figure out what a particular spid of code is doing, consider the sys.dm_exec_sql_text dynamic management view as a viable means to answer that question.
- As you can see with the sys.dm_exec_sql_text dynamic management view you have a number of options when querying the data. The options outlined in this tip are just the tip of the iceberg. Depending on your needs, you could customize these scripts to really drill into the specific performance problems in order to focus your tuning efforts.
- Check out these related tips:
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-10-31
