Conor vs. Statement Offsets

As most of you have figured out by now, I work mostly on queries :).  One of the things that I often need to do is to get a statement out of a batch so that I can go look more closely at a query plan (and, in my case, the code that generates it). 

I use the a query like this to read through a few DMVs and emit the text for the plan I want:

 select statement_start_offset, statement_end_offset, statement_text from (
SELECT 
    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)statement_text,
         *
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st) a
ORDER BY statement_text, statement_start_offset

A few notes:

1. SQL 2005 and SQL 2008 sometimes show –1 for the end offset instead of computing the real offset. 

2. the offsets are actually byte offsets within a UTF-16 string (so, since SQL Server doesn’t do anything special for the fancy > 2 byte characters, this just means that you divide the offsets by 2 to find the character offsets)

Now, the DMVs were initially intended as debugging tools. While they are very useful, they have become quite a bit more popular than I had originally intended (I built much of the internal framework for what became DMVs, but I didn’t build these specific DMVs).  I was talking with a few of the other developers, and we were curious how people thought about this –1 end offset business.  The original intent was that these DMVs should be able to change each release, but I’d expect that people could have dependencies on nuances like this in the DMVs.  If you have thoughts on this, please feel free to mail them to me at (conorc (at) microsoft (dot) com).

Happy Querying!

Conor