March 23, 2006 at 8:35 am
We had a long running query on yesterday. In the Current Activity Window (in EM) the following information was displayed:
Status: Sleeping
Wait Time: 11063
Wait Type: CXPacket (The other Update Stmt Wait Type was PAGEIOLATCH_SH)
Wait Resource: 8:1:149936
Login Time: 3/20/06 0815 A.M.
Last Batch: 3/20/06 22:38 P.M.
Blocked By: 0
Blocking: 0
The query completed on yesterday evening after 49 hours and 54 minutes. My question is what can I conclude from the information provided by the Current Activity Window? Does a Status of Sleeping means the query was not executing? BOL states a Sleeping Process is waiting for a lock or user input. But, the above information was showing there were no locks. What does a Wait Type of CXPacket and PAGEIOLATCH_SH indicates? Does the Last Batch TimeStamp means that was the last time the query executed?
Also, if the CPU and Physical I/O numbers do not change does this means the query is hung or not doing anything or sleeping? Can these numbers be used to indicate activity or a query which is doing work?
Could SQL Profiler been used to monitor this query? Could I have started Profiler while this query was running or would I have had to open Profiler and start this script within Profiler along with a trace file?
We have SQL 2000 (SP3) running on Windows 2000.
Thanks in advance for you help, Kevin
March 23, 2006 at 12:02 pm
1) an oversight of waittypes in sqlserver
http://support.microsoft.com/?kbid=822101
2)The activity window in enterprise manager isn't autorefresh.
3) You could use the profiler if your query is composed of multiple steps, by watching statement started, statement completed
4)If there were no locks, it could be your I/O. Have you monitored disk queue length with the performance monitor? Normally it shouldn't go above 2 per spindle for an extended period.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply