I have an application that is attempting to perform a function (modifying records, so I'm assuming an update query) under a certain user's session and it is timing out. Is there a way to easily monitor what is happening in the database to examine what the issue might be, something to get the exact query so I can run it again in SSMS?
The query store has nothing in the "Top Resource Consumers", however, because the query doesn't complete, I'm thinking it might not show up here(?).
I have used various forms of sp_Blitz to look at locking and don't see anything.
I think the easiest approach is to monitor Activity Monitor when the user is logged in and have them try the process again. I need to get my ducks in a row and know what to look for, any suggestions? Ideally, I would want to get the actual query, then I can run it myself and look at all kinds of stuff, logical reads, query plan, io statistics. etc.
Query timeout is set by the application so maybe you could adjust that to prevent errors until you find the culprit.
Run profiler or use extended events to capture the queries being run, extended events are less resource-intensive.
If you know when the application is performing the function then you should be able to see the query from the DMVs, using a query like the following:
SELECT
der.session_id,
der.STATUS,
db_name(der.database_id) DBName,
der.command,
des.login_name,
des.program_name,
der.wait_type,
StatementExecuting = SUBSTRING(
t.[text],
der.statement_start_offset / 2,
(
CASE WHEN der.statement_end_offset = -1 THEN LEN(
CONVERT(
NVARCHAR(max),
t.[text]
)
) * 2 ELSE der.statement_end_offset END - der.statement_start_offset
) / 2
),
p.query_plan,
der.reads,
der.logical_reads,
der.writes,
des.host_name,
last_request_start_time
FROM
sys.dm_exec_requests der
JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(der.plan_handle) p
where
der.STATUS != 'background'
You should be able to get the plan as well from this which may help show what is causing the query to be slow.
December 1, 2021 at 6:05 pm
Thanks SSCrazy Eights - this is exactly what I needed. I will also give profiler a shot as well.
December 2, 2021 at 8:17 am
This was removed by the editor as SPAM
December 2, 2021 at 12:58 pm
While SQL Server itself doesn't capture metrics about timeouts, the windows OS does. Extended Events can actually capture this with a debug event (these are subject to change without notice, and, depending on the event, potentially dangerous to a system, this one is fine, but use others with caution), connectivity_ring_buffer_recorded. I have an example setup of how to query it for one type of timeout here.
If you do want to use just the query to identify what's happening, you can use rpc_starting & rpc_completed, or sql_batch_starting & sql_batch_completed, depending on if this is a prepared statement/procedure or an ad hoc batch. Combine them with enabling Causality Tracking and then you can group by the guid that causality tracking creates. That will let you see the starts that don't have a stop. Add in capturing wait statistics, as outlined here, and you'll get a lot of info on your queries.
Profiler/trace can't do this stuff, so I wouldn't waste my time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 6, 2021 at 4:29 pm
Thanks Grant, I'll try that as well.
December 6, 2021 at 4:30 pm
SS Crazy: I am testing the script; it actually shows my ID and the script (which would makes sense), but I am connected via SSMS with a TestUser account and running a select * statement and nothing shows up. Do you know why some sessions/queries might not take to these DMVs?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply