Query consuming CPU on DB server

Question

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?

The command column tells that it is a SELECT statement. But have a look at the Statement_Text column - It just says S. Nothing else. And then look at the cpu_time, logical_reads etc. Also this S does not seem to appear in the SQL profiler. I am wondering how to debug this further. — shashi
– shashi, Commented Dec 10, 2011 at 22:20
Try capturing the whole of txt.text. Pretty sure your substring code will turn out to be at fault. — Martin Smith
– Martin Smith, Commented Dec 10, 2011 at 23:07
I use Derek Dieter's sp_who3 for this type of investigation. Your substring code is a bit different from his. : sqlserverplanet.com/dmv-queries/… — brian
– brian, Commented Dec 11, 2011 at 4:24
@sassyboy - I see you accepted my answer so I assume my guess was correct? Did you find anything special about the queries with statement_end_offset=0? — Martin Smith
– Martin Smith, Commented Dec 11, 2011 at 12:42
@MartinSmith For the time being I havent been able to dig deep into it. After switching to txt.text, I was able to find out the query for which the substring S was showing. Thus currently I am working on reducing the load on the db server and need to finish that ASAP. Once that is done, I will dig deeper and try and find out what exactly was happening and will share the learnings. Thanks for showing me the right direction. — shashi
– shashi, Commented Dec 11, 2011 at 14:08

Martin Smith · Accepted Answer · 2011-12-11 00:06:32Z

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.

Collectives™ on Stack Overflow

Query consuming CPU on DB server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related