February 13, 2017 at 8:05 am
We had this happen few times now, on certain tables where saving record takes lot of time. On the Normal day if it takes 1 sec , it takes anywhere from 1 to 2 minutes to save the record.
- Checked the indexes and they looked fine.
- We tried looking for locking/blocking process and could not find anything (using sp_who2) .
- Then we restarted the database server and database started behaving normally.
Trying to get some pointers so that we can pin point what is causing this problem.
February 13, 2017 at 10:46 am
skb 44459 - Monday, February 13, 2017 8:05 AMWe had this happen few times now, on certain tables where saving record takes lot of time. On the Normal day if it takes 1 sec , it takes anywhere from 1 to 2 minutes to save the record.
- Checked the indexes and they looked fine.
- We tried looking for locking/blocking process and could not find anything (using sp_who2) .
- Then we restarted the database server and database started behaving normally.Trying to get some pointers so that we can pin point what is causing this problem.
Try looking at the waiting tasks when this happens. At a high level, start with something like:
SELECT wt.session_id,
wt.wait_duration_ms,
wt.wait_type,
wt.blocking_session_id,
wt.resource_description,
er.wait_resource,
er.command,
er.status,
est.text
FROM sys.dm_os_waiting_tasks wt
INNER JOIN sys.dm_exec_requests er
ON wt.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est
You may also want to download sp_WhoIsActive as it is commonly used and is like an enhanced version of sp_who2. You can download it from here:
sp_WhoIsActive
Sue
February 13, 2017 at 11:07 am
Thanks for the info.
But What exactly I am looking for and how I can identify the session which is causing the problem ?
Thanks,
Shashi
February 13, 2017 at 11:14 am
skb 44459 - Monday, February 13, 2017 11:07 AMThanks for the info.
But What exactly I am looking for and how I can identify the session which is causing the problem ?
Thanks,Shashi
No one can tell you exactly what you are looking for - that's the point of executing that query. It could be dozens of things.
You would look at the text to see what is being executed by the sessions and look at the wait time since someone waiting minutes on an update or insert would show increasing, high wait times.
What are you using to access those tables? Hopefully you aren't doing this against a large table using SSMS.
Sue
February 13, 2017 at 11:21 am
ok. I will run this when it happens again.
Everything is happening from application not ssms
February 16, 2017 at 5:59 am
I checked dbo.sp_WhoIsActive and one of the process is stuck at Insert Command forever.
Not able to figure out why its waiting indefinitely.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply