Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?
Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>