March 26, 2009 at 2:51 am
Hi All,
While executing few set of statements
I am able to see 270000 locks(page,key,row and table) on a table. But when I ran same again i.e nd ,3rd ,.. time there is no locks on the table.
The locking statement is "insert"
Let me know why i am getting issue at first time.
First time means after restarting the machine.
Thanks,
KK.
March 26, 2009 at 4:11 am
Hi
Please try this statement (thanks to Mohit 😉 ) for more specific information about the "INSERT":
SELECT TOP(50)
qs.total_worker_time / execution_count as avg_worker_time,
(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
)
FROM sys.dm_exec_sql_text(qs.sql_handle) AS ST
) AS statement_text,
*
FROM sys.dm_exec_query_stats AS qs
ORDER BY avg_worker_time DESC
Greets
Flo
March 28, 2009 at 3:53 pm
Heh, that was my attempt to do it without using CROSS APPLY; for databases running in 80 compatibility mode. Which failed horribly, only solution I found was to do it with Cursors ... and I couldn't get my self to post that on form LOL so it's hidden in my script library LOL.
Here is the Cross apply for same script above:
SELECT TOP 50 qs.total_worker_time / execution_count AS avg_worker_time,
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) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
ORDER BY avg_worker_time DESC
PS I am trying to get DMV/DMF queries for me blog if anyone wants to donate, I'll credit it appropriately ;-). (http://sqllearningsdmvdmf.blogspot.com/)
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply