Please explain code used to identify currently executing SQL statements

Question

Post reply

Please explain code used to identify currently executing SQL statements

DBADave

SSChampion

Points: 10962
More actions
October 2, 2008 at 12:10 pm

#193947

To find the exact SQL statement executed at a point in time I can run the script below, which I found in BOL and various blogs. Can someone explain why statement_start_offset is divided by 2 and then 1 is added and statement_start_offset is divided by 2 and then 1 is added? If statement_start_offset is the number of characters into the currently executing batch or stored procedure at which the currently executing statement starts and statement_end_offset is the number of characters into the currently executing batch or stored procedure at which the currently executing statement ends, why not just subtract statement_start_offset from statement_end_offset? I'm certain there is a good explanation, but I can't figure it out.
SELECTa.session_id,
SUBSTRING(c.text, (a.statement_start_offset/2)+1,
((CASE a.statement_end_offset
WHEN -1 THEN DATALENGTH(c.text)
ELSE a.statement_end_offset
END - a.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_requests a
JOIN sys.dm_exec_sessions b
on a.session_id = b.session_id
CROSS APPLY fn_get_sql (a.sql_handle) c
Thanks, Dave

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

dcorbitt Ten Centuries Points: 1006 More actions · Answer 1

Perhaps to strip off comments around the actual command text?

Replace the top line of your query with

/* test */SELECT a.session_id,

a.statement_start_offset AS StartO,

a.statement_end_offset AS EndO,

c.TEXT,

and compare. Play with the length of the comment.

DBADave SSChampion Points: 10962 More actions · Answer 2

I found a blog that pointed me in the right direction. Division by 2 is needed because the text data is stored by Microsoft as Unicode.

Thanks, Dave

dcorbitt Ten Centuries Points: 1006 More actions · Answer 3

Hey now, my response demonstrated that several hours before you found that other site.

🙂

For the same reason, the line that reads

WHEN -1 THEN DATALENGTH(c.text)

should read

WHEN -1 THEN DATALENGTH(c.text) * 2

so that the final query is reported in full.

Thanks for sharing a nice query for checking on slowness without perhaps having to launch the profiler.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No, because datalength already takes unicode/non-unicode into account.

SELECT DATALENGTH('abc') returns 3

SELECT DATALENGTH(N'abc') returns 6, because it's a unicode string and hence requires 2 bytes/character

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dcorbitt Ten Centuries Points: 1006 More actions · Answer 5

Run DBADave's original query as is and check the result; it won't return the full query.

Add the "*2" and you'll get the whole thing.

I also ran with a second query running at the same time in another query window before I posted; same results.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

That's not because of the datalength needs to be multiplied by 2. It's because the /2 is outside the CASE statement but only applies to the ELSE portion of the case.

Try this one which does work correctly.

SELECT a.session_id,

SUBSTRING(c.text, (a.statement_start_offset/2)+1,

((CASE a.statement_end_offset

WHEN -1 THEN DATALENGTH(c.text)

ELSE (a.statement_end_offset - a.statement_start_offset)/2

END)) + 1) as statement_text

FROM sys.dm_exec_requests a

JOIN sys.dm_exec_sessions b

on a.session_id = b.session_id

CROSS APPLY fn_get_sql (a.sql_handle) c

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dcorbitt Ten Centuries Points: 1006 More actions · Answer 7

I hadn't noticed that. No point multiplying and dividing by 2.

I should've dug further when I saw it was you, Gail. You always do nice work.

dcorbitt Ten Centuries Points: 1006 More actions · Answer 8

I also noted some occurrences where statement_end_offset came back as 0, so I made the change for my purposes to treat it like -1. Otherwise you get back only one char of the current statement.

(I also add a grab on DB Id, which we needed here, and dropped the join on dm_exec_requests.)

SELECT a.session_id, c.dbid,

SUBSTRING(c.text, (a.statement_start_offset/2)+1,

((CASE WHEN a.statement_end_offset < 1

THEN DATALENGTH(c.text)

ELSE (a.statement_end_offset - a.statement_start_offset)/2

END)) + 1) as statement_text

FROM sys.dm_exec_requests a

--JOIN sys.dm_exec_sessions b

--on a.session_id = b.session_id

CROSS APPLY fn_get_sql (a.sql_handle) c

DBADave SSChampion Points: 10962 More actions · Answer 9

I'll make the change to my code, but I found that if I substitute CROSS APPLY fn_get_sql (a.sql_handle) c with

CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) c the original logic works. Any idea why that would be?

Dave

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Again because of the varchar/nvarchar

In fn_get_sql, the text column is of type text, is a non-unicode value, so datalength returns the length of the string and hence dividing that by 2 gives a wrong answer. In exec_sql_text, the text column is nvarchar(max), so the datalength is twice the length of the string and hence does need to be divided by 2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

DBADave SSChampion Points: 10962 More actions · Answer 11

Thanks. I just reviewed my original notes and the initial code I copied from Microsoft did reference dm_exec_sql_text and not fn_get_sql. Somewhere in my testing I swapped the two, thus leading to the confusion. Thanks for the clarification.

Dave

brk5tr Ten Centuries Points: 1360 More actions · Answer 12

This is a similar version which I use. It removes the join to Requests and Sessions, and uses sys.dm_exec_query_stats to retrieve all plans from the cache.

Please let me know if anything seems incorrect.

SELECT a.execution_count, a.plan_handle,
SUBSTRING(c.text, (a.statement_start_offset/2)+1,
((CASE a.statement_end_offset
WHEN -1 THEN DATALENGTH(c.text)
ELSE a.statement_end_offset
END - a.statement_start_offset)) + 1) as statement_text
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text (a.sql_handle) c

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Please note: 2 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

brk5tr Ten Centuries Points: 1360 More actions · Answer 14

Thanks Gail. Didn't notice that.

Would you agree that the query I posted is suitable for querying plans in the cache and then seperating the statements using the offsets please?

thanks.