August 18, 2010 at 4:22 am
i am running a query, which i see in the Activity monitor is changed to SUSPEND MODE.
in the activity monitor the only thing that run's at that time, is some system operation on tempdb.
when i follow up, isee that some times my query goes out for a second or two from suspended mode, and goes back to that mode.
any idea why?
Thanks
Peleg
August 18, 2010 at 5:39 am
Next time you see your query placed in a suspended state, check the "wait type" value for that process. You can see that in Activity Monitor. The "wait type" should give you an idea of what the issue is
August 18, 2010 at 5:57 am
i get PAGEIOLATCH_SH,
what execlly this means?
August 18, 2010 at 6:15 am
A PageIOLatch is placed onto a page while SQL is moving that page from disk to memory or memory to disk. Significant wait times on that may indicate a poorly performing IO subsystem
Suspended means waiting for a resource. It may be a lock, a latch, a memory grant or one of several other things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 18, 2010 at 10:42 am
Just a quick additional note: is the physical_io still changing while it's in this state? Do you have disk queues for the data files it's accessing? If it's a frequently run query that does this often, you could try changing the maxdop value for it (add option (maxdop 4) at the bottom, or whichever numbers you want to try..different queries respond better to different levels of parallelism. I typically test with 1, 2, 4, 8.).
And of course make sure it's not being blocked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply