Home Dashboard Directory Help
Search

sys.dm_exec_requests statement offsets wrong during index creation by Adam Machanic


Status: 

Closed
 as Won't Fix Help for as Won't Fix


13
0
Sign in
to vote
Type: Bug
ID: 478601
Opened: 7/30/2009 9:14:49 AM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

Description

When creating an index as part of a batch, the sql_handle changes and the text returned by the sys.dm_exec_sql_text function reflects ONLY the creation of the index, not the full batch. Unfortunately, the statement_start_offset and statement_end_offset do not reflect this, and the offsets are populated as though the returned text will include the entire batch. See the repro below to make this clearer.
Details
Sign in to post a comment.
Posted by Microsoft on 1/18/2010 at 4:07 PM
Hi Adam,
We will fix this in the next major release of SQL Server. Since this doesn't meet the bar for a service pack/cumulative update for SQL Server 2008, this bug has been resolved as "Won't Fix". We have another bug created in the next major version to track this issue and unfortunately you don't see it on connect. I am working with other team members to change this process a little bit so we leave the connect item open but move it to next major release. Anyway, I will keep you updated.

Thanks
Umachandar, SQL Programmability Team
Posted by Adam Machanic on 10/30/2009 at 9:20 AM
Hi UC,

I just found another issue, but I don't think I'm going to be able to easily get you a repro. The issue occurs when a SELECT is calling a scalar UDF. There is a timing issue where if the query against sys.dm_exec_requests is done at exactly the right moment, the statement offsets will be for the UDF's sql_handle rather than the outer SELECT's sql_handle, but the handle in the DMV will still be for the outer SELECT. A moment later this will change and the handle will be correct--that of the UDF. I hope this makes sense?

Thanks,
Adam
Posted by Microsoft on 8/3/2009 at 1:42 PM
Hi Adam,
I saw different results in SQL Server 2005 & 2008. Maybe it is a timing thing. I added a "go 500" to the DMV query window and ran it continuously while the batch was executing. I will try it again and we will look at implementation in both versions anyway.

Thanks
Umachandar
Posted by Adam Machanic on 8/3/2009 at 12:15 PM
Hi UC,

This particular behavior is identical on both 2005 and 2008. Did you try the repro?

Thanks,
Adam
Posted by Microsoft on 8/3/2009 at 12:11 PM
Hi Adam,
Thanks for reporting this issue. It looks like there is a change in behavior from SQL Server 2005 to SQL Server 2008. In both cases, we show the internal statements (stats query, insert) for the CREATE INDEX. But SQL Server 2005, shows the CREATE INDEX statement also. Anyway, we will investigate the problem and let you know what we find.

--
Umachandar, SQL Programmability Team
Sign in to post a workaround.