Identifying the input buffer in SQL Server 2000 vs SQL Server 2005

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:

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