January 31, 2005 at 11:04 am
Evening All;
I'm having this strange problem on a production server (SQL Server 2000, SP3A, Build# 8.00.760)
whereby, at non-regular intervals during the day we are swamped with blocks.
So far I have taken the usual approach, ran traces to identify slow running queries, cpu intensive queries, even to locate what resources are locked; unfortunately nothing to that effect sheds any light.
Its never the same resource, there are no slow running queries, atleast none that are slow enough to warrant a 5 minute block, none of the procs are particualy cpu-hungry.
It does use a full-text index, quite heavily and that is rebuilt and repopulated during planned downtime each night, there are no users at this point.
Traffic doesnt appear to make a difference to the occurrence of the blocks, whether we have 200 users or 100 users, infact during a period of 3 hours where we have a constant supply of users, and the 200 threshold was maintained, we didnt experience any blocks. Yet, just before lunch with 90-something users, we encountered about 5 unique blocks that persisted for 9 minutes before being kill manually.
I have tested this on two seperate servers, in the hopes that a shared server would be hte problem, its now on a dedicate box, and still the same problems.
There is a virus scanner active on there, but I have disabled the monitor from touching the \FTData directory and below!
Does anyone have any idea, what to look for next, this is an amazingly small site, that just doesnt want to co-operate, I have been thru every stored procedure (there is nothing embedded either) and there is nothing obvious that needs changing. We made the developers adhere to very strict guidelines.
Should the full text catalog be rebuilt more often than once per day?
Any suggestions or ideas would be greatly appreciated;
thanks for your time,
Alex Wilson
February 1, 2005 at 6:30 am
You should be able to identify what is causing a block, you could run a query against sysprocesses or there are lots of procs available from microsoft technet if you want to feel safe about running code.
Once you can identify what is causing the block then you can proceed.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply