Link to home
Start Free TrialLog in
Avatar of Damian Gardner
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!
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

How would you like to trace the query? You should find it in your app and optimize it.

You may also list queries executed on SQL Server:
SELECT
    deqs.last_execution_time AS [Time], 
    dest.TEXT AS [Query]
 FROM 
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY 
    deqs.last_execution_time DESC

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.
try ... [query from brent ozar]

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

Open in new window


https://www.brentozar.com/blitzcache/long-running-queries/
https://www.brentozar.com/responder/get-top-resource-consuming-queries/

Hope it helps !!
Avatar of Damian Gardner
Damian Gardner

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
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
Try like this...  text LIKE '%(@P1 int,@P2 nvarchar(256)) SELECT TOP 1 T1.ServerId,%' , You can pass any unique characters to search..

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,%'

Open in new window


Hope it helps !!
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
Testing to see if thread is still active...
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.ISDEFAULTREPORTMODELSERVER,T1.SERVERURL,T1.ISDEFAULTREPORTLIBRARYSERVER,T1.AXAPTAREPORTFOLDER,T1.DESCRIPTION,T1.DATASOURCENAME,T1.REPORTMANAGERURL,T1.SERVERINSTANCE,T1.AOSID,T1.CONFIGURATIONID,T1.DEL_MAXROW_CN,T1.ISSHAREPOINTINTEGRATED,T1.RECVERSION,T1.RECID FROM SRSSERVERS T1 WHERE ((ISDEFAULTREPORTLIBRARYSERVER=@P1) AND (AOSID=@P2))
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
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

Open in new window

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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
ok, understood.  I will see what I find.  standby for an update.  thanks!
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
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.
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.
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.
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!
Thanks to both