Damian Gardner
asked on
How to SQL Trace a SPECIFIC query
Hello - we have a menacing query that we find frequently locks up our applications and we have to kill it. How do we trace for that specific query? I see how to trace log for "poor performers" and types of queries - but not specific ones. Your help is appreciated.
Thank you!
Thank you!
try ... [query from brent ozar]
You can change the timings like below-
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
https://www.brentozar.com/blitzcache/long-running-queries/
https://www.brentozar.com/responder/get-top-resource-consuming-queries/
Hope it helps !!
You can change the timings like below-
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
SELECT st.text,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
https://www.brentozar.com/blitzcache/long-running-queries/
https://www.brentozar.com/responder/get-top-resource-consuming-queries/
Hope it helps !!
ASKER
Thanks guys for your help. I don't know if we know quite where the specific query is emanating from - like which class module in our system. So let me read up on the suggested articles, and try a few things here. thanks and standby
ASKER
Hey guys - I am trying to use a WHERE clause on the SQL query suggested above, in order to narrow it down to the specific problematic query that is locking up the system - but it seems to ignore my WHERE clause, and shows me the same results each reiteration. Any idea? How do I find my specific query? Attached is a screenshot of what I'm seeing.
thanks,
Damian
SQL-PLAN.docx
thanks,
Damian
SQL-PLAN.docx
Try like this... text LIKE '%(@P1 int,@P2 nvarchar(256)) SELECT TOP 1 T1.ServerId,%' , You can pass any unique characters to search..
Hope it helps !!
SELECT st.text,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE text LIKE '%(@P1 int,@P2 nvarchar(256)) SELECT TOP 1 T1.ServerId,%'
Hope it helps !!
ASKER
thanks Pawan. I actually did try the LIKE before. I tried it again, just to be sure. it makes no difference. its very strange - its as if it ignores that part of the where clause. maybe there's no way to narrow it down? there must be though...
Hi Damian,
Any luck with this :)
Thank you
Pawan
Any luck with this :)
Thank you
Pawan
ASKER
Testing to see if thread is still active...
ASKER
I have not resolved this yet, but was busy with some other IT fires, and am picking back up on this now.
Pawan - I have not had any luck as of yet. I got as far as executing the suggested SQL code above, and it produces 60 results, and I have not been able to identify my target query in the results yet - none seem to match. I'm not great with analyzing SQL, but what it seems like I need to do is identify the SQL that is leading UP to the point that the query I keep having to kill is generated. One thing I noticed in the Activity Monitor is that I can "trace query" when it locks up. So I can START the trace easily at the time the system locks up on this particular query - but I don't know what to do from that point forward. Is there something I can do by initiaiting a trace at that time - or do I need to be tracing it BEFORE that time?
Thanks,
Damian
btw - here is the query that is the culprit:
(@P1 int,@P2 nvarchar(256))SELECT TOP 1 T1.SERVERID,T1.ISDEFAULTRE PORTMODELS ERVER,T1.S ERVERURL,T 1.ISDEFAUL TREPORTLIB RARYSERVER ,T1.AXAPTA REPORTFOLD ER,T1.DESC RIPTION,T1 .DATASOURC ENAME,T1.R EPORTMANAG ERURL,T1.S ERVERINSTA NCE,T1.AOS ID,T1.CONF IGURATIONI D,T1.DEL_M AXROW_CN,T 1.ISSHAREP OINTINTEGR ATED,T1.RE CVERSION,T 1.RECID FROM SRSSERVERS T1 WHERE ((ISDEFAULTREPORTLIBRARYSE RVER=@P1) AND (AOSID=@P2))
Pawan - I have not had any luck as of yet. I got as far as executing the suggested SQL code above, and it produces 60 results, and I have not been able to identify my target query in the results yet - none seem to match. I'm not great with analyzing SQL, but what it seems like I need to do is identify the SQL that is leading UP to the point that the query I keep having to kill is generated. One thing I noticed in the Activity Monitor is that I can "trace query" when it locks up. So I can START the trace easily at the time the system locks up on this particular query - but I don't know what to do from that point forward. Is there something I can do by initiaiting a trace at that time - or do I need to be tracing it BEFORE that time?
Thanks,
Damian
btw - here is the query that is the culprit:
(@P1 int,@P2 nvarchar(256))SELECT TOP 1 T1.SERVERID,T1.ISDEFAULTRE
Are you sure the above query locks your application?
It selects ONE row from ONE table containing some servers list... Does some part of your app write to this table intensively? Why should it be locked?
When the blocking starts issue following query
If it returns empty data set then nothing is waiting so you could execute the above query periodically to identify the blocking state beginning.
It selects ONE row from ONE table containing some servers list... Does some part of your app write to this table intensively? Why should it be locked?
When the blocking starts issue following query
SELECT req.session_id
,blocking_session_id
,ses.host_name
,DB_NAME(req.database_id) AS DB_NAME
,ses.login_name
,req.status
,req.command
,req.start_time
,req.cpu_time
,req.total_elapsed_time / 1000.0 AS total_elapsed_time
,req.command
,req.wait_type
,sqltext.text
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
JOIN sys.dm_exec_sessions ses
ON ses.session_id = req.session_id
WHERE req.wait_type IS NOT NULL
and you should see waiting and blocking commands incl. command text.If it returns empty data set then nothing is waiting so you could execute the above query periodically to identify the blocking state beginning.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your suggestions pcelba and pawankkmr. I will try those, but I wanted to update you on the facgt that I started a SQL trace in SQL Profiler, and had several locks occur this morning - so I should have some data on the locks in trace logs. I am not sure how to quickly sift through them, or what to look for. I am assuming I will see something about a "deadlock", which I could hopefully use to zero in on the issue. Any further suggestions are welcome. but I will try your suggestions above as well.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok - so even if I find a "deadlock" in the logs, that might not help me then?
If you have deadlocks in your app then you should know about them already. You don't find "new" deadlocks if you have some error logging in your application. It is same kind of error as e.g. foreign key rule violation. It always causes error in SQL command and transaction cancellation and it should be recorded/visible even w/o profiler.
And even when you know about deadlocks then you have to investigate why did they happen and it is almost impossible without detail application knowledge.
OTOH, Locks will appear as long lasting commands in the profiler. SQL Server always places some locks during commands processing. If they are short then OK, if they are placed for long time then it blocks other processes and the execution time is longer and longer and you may see waiting commands in the list obtained from one of above queries.
Profiler is useful to tell which commands consume more SQL resources and you may then optimize such commands.
And even when you know about deadlocks then you have to investigate why did they happen and it is almost impossible without detail application knowledge.
OTOH, Locks will appear as long lasting commands in the profiler. SQL Server always places some locks during commands processing. If they are short then OK, if they are placed for long time then it blocks other processes and the execution time is longer and longer and you may see waiting commands in the list obtained from one of above queries.
Profiler is useful to tell which commands consume more SQL resources and you may then optimize such commands.
ASKER
ok, understood. I will see what I find. standby for an update. thanks!
ASKER
I just had the lock happen again, and I executed the code suggested by Pawan, and it looks like the problem is an update statement that is holding up the others. I put some screen shots of the results, plus the text of the update stmt in the attached wordpad file - your thoughts are helpful, since I don't have much experience at this. One thing I wonder is how to find out how long the UPDATE stmt has been locking the table, and how long it would keep going for? thank you gentlemen.
Damian
locks.rtf
Damian
locks.rtf
The attached file does not contain code from Pawan but that's not important.
The blocking command is not the UPDATE running in Session 71 and mentioned in your doc. The UPDATE command is blocked by some command in the Microsoft Dynamics running in the "Blocking Session" 164. Also other waiting commands are blocked by the session 164.
You should look what command is running in Dynamics (session 164) and decide about possible solution together with the program vendor.
You may simply remove the WHERE clause from the query to see the blocking command which does not wait but just consumes too many resources.
Other commands waiting time is visible in total_elapsed_time column. To estimate how long it could wait is impossible.
The blocking command is not the UPDATE running in Session 71 and mentioned in your doc. The UPDATE command is blocked by some command in the Microsoft Dynamics running in the "Blocking Session" 164. Also other waiting commands are blocked by the session 164.
You should look what command is running in Dynamics (session 164) and decide about possible solution together with the program vendor.
You may simply remove the WHERE clause from the query to see the blocking command which does not wait but just consumes too many resources.
Other commands waiting time is visible in total_elapsed_time column. To estimate how long it could wait is impossible.
ASKER
Ah - you're right, 164 was the culprit. And I missed the "total elapsed time" column there - sorry. I'll need to dig into the code driving the Dynamics session then. Thanks pcelba.
ASKER
We traced that 164 thread to some code that was turned on a month ago and did not need to be left running. So we turned it off this morning, and so far have not had any locks. My gut tells me that this code might have simply been exacerbating the real problem. So the trace is still running and we'll see if it locks again.
This sounds like a solution. But not for the Microsoft Dynamics problem which will require more powerful machine most probably.
ASKER
I appreciate your help gentlemen. We had another lock again today, and the queiries in the logs look like all the rest of the queries, so its going to be difficult to find this "needle in the haystack". I'll go ahead and close this thread, as its going to take possibly a SQL consultant to help us on this. Thank you!
ASKER
Thanks to both
You may also list queries executed on SQL Server:
Open in new window
You may also trace extended event SQL:StmtCompleted https://msdn.microsoft.com/en-us/library/ms189886.aspx
If you would like to catch the query on-line then use SQL Profiler.