Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments   |   Related Tips: More > 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
FROM master.dbo.sysprocesses
WHERE spid = <spid>

SELECT *
FROM ::fn_get_sql(@Handle)
 

*** 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



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools