CODEWITHSUNDEEP
sql-serversql-server-2005
shashi
shashi

Reputation: 4696

Query consuming CPU on DB server

My db server is is having continuous high CPU load for the past few days. While investigating this, I was looking at the currently executing requests using the query 

SELECT session_id,
       request_id,
       Db_name(database_id),
       start_time,
       status,
       command,
       Substring(txt.TEXT, ( statement_start_offset / 2 ) + 1,
       ( ( CASE statement_end_offset
       WHEN -1 THEN Datalength(txt.TEXT)
       ELSE statement_end_offset
                                                                   END
           - statement_start_offset ) / 2 ) + 1) AS statement_text,
       wait_type,
       wait_time,
       blocking_session_id,
       percent_complete,
       cpu_time,
       reads,
       writes,
       logical_reads,
       row_count
FROM   sys.dm_exec_requests
       CROSS APPLY sys.Dm_exec_sql_text([sql_handle]) AS txt
WHERE  session_id <> @@SPID
       AND session_id > 50

Most of the time I find that apart from the regular queries sent by application server, there are these weird S queries which seem to be consuming a decent slice of CPU time. e.g.

Screen shot from query analyzer I took yesterday for the above query

They do not appear on the SQL profiler. Anybody has ideas what are they and what should be done about them?

Upvotes: 3

Views: 343

Answers (1)

Martin Smith
Martin Smith

Reputation: 453563

My guess is that if you capture the whole of txt.text along with statement_start_offset statement_end_offset that you will find that there are some circumstances where the offset columns can both turn out to be 0 and so the displayed statement_text is truncating to only show you the first character of the SELECT query.

DECLARE @text nvarchar(max); 
SET @text = 'SELECT .....';
DECLARE @statement_start_offset INT; 
SET @statement_start_offset = 0;
DECLARE @statement_end_offset INT;
SET @statement_end_offset = 0;


SELECT 
       Substring(@text, ( @statement_start_offset / 2 ) + 1,
       ( ( CASE @statement_end_offset
       WHEN -1 THEN Datalength(@text)
       ELSE @statement_end_offset END
           - @statement_start_offset ) / 2 ) + 1) AS statement_text

I couldn't find any indication of when statement_end_offset would return 0 rather than -1 though.

Upvotes: 2

Related Questions