December 9, 2008 at 4:50 pm
Hi everybody,
Twice in the last week I've had an unexplained down time of about 2 or 3 minutes straight where basically every query I'm issuing to my database, from many different servers, times out. I have scoured every log file I have available and haven't found any obvious cause.
This is a really busy database, thousands of tx/sec, a hundred gigs or so of data.
The only other time I've seen anything like this was when I did something stupid like copy a huge file off of the same disk that holds data files, or executed my index maintenance script while things were busy. But for these outages nothing else was being done on the server. And the server is 100% dedicated to just the SQL Server 2005 instance, nothing else runs there.
Any suggestions for troubleshooting steps?
Thanks,
EZB
December 9, 2008 at 5:20 pm
Have you tried using Profiler to get a picture of what's going on in the database?
I had similar timeouts previously and in my case, I was able to identify a query that was being executed hundreds of times every minute. I took that query and rewrote it to take advantage of the indexes on that particular table. I then took it one step further and parametrized the query so that a new execution plan wasn't being created each and every time the query ran. This greatly improved performance and my time-outs are gone now.
From my experience, I would start with Profiler and see what info that provides.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply