September 22, 2009 at 10:50 am
We are running SQL Server 2005 SP3 on a guest OS on VM Ware. Many Times we have self-blocking spids that eventually work themselves out. I have tried to determine the problem and I want to believe it is the virtual server we are on.I have monitored the disk queue length but that is normally 0. What else can I be looking for to determine the cause?
September 22, 2009 at 11:31 am
How are your indexes and statistics? Next time you see this come up take a moment and update the statistics on the most heavily used (inserts / updates / deletes) table. See if that cures the problem.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 22, 2009 at 11:35 am
I have indexes on most lookups and they were reindexed earlier today. The problems still exists.
September 22, 2009 at 11:38 am
Did you use rebuild or reorg? Rebuild will take care of stats reorg will not.
Certainly not making the case that this is emphatically the issue but in most cases that I have seen this it has been.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 22, 2009 at 11:44 am
I used DBCC DBREINDEX which from what I have read, rebuilds them. I have also been looking into latch waits but I am not sure what I should expect from these metrics.
September 22, 2009 at 12:06 pm
What is the lastwaittype that is in sysprocesses for the spid in question when it is blocking itself?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 22, 2009 at 12:18 pm
MISCELLANEOUS
September 22, 2009 at 12:18 pm
Another has SOS_SCHEDULER_YIELD
October 2, 2009 at 7:49 am
First - I apologize for the late reply on this. I am in the middle of a significant upgrade project and probably shouldn't have even started on this thread. 🙁
The SOS_SCHEDULER_YIELD implies CPU pressure and you can review some information on that here - http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx
I also wonder if you were to apply the OPTION (MAXDOP 1) to the query if you would see that go away. Check the execution plan to see if parallelism is being used in the execution of the query.
Let me know what you find and I will try to get back to you quicker than this last time. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply