August 3, 2007 at 7:59 am
Out of nowhere I got several calls of ppl trying to write data thru several of our apps (updating customers etc) and they couldn't. The applications were timing out or just didn't work. This was really odd so I traced the application in profiler and found the actual exec statement and ran it locally in Managment Studio. The same thing happened to me. It was taking forever to to run... so long in fact I cancelled it.
My question is...
What do I want to look for in resolving this type of issue?
I would think that I would look for Locks in the activity monitor but I didn't see any at the time. I'm definitely missing something.
How would some of you go about investigating this type of issue?
After about 3-5 minutes everything was back to normal but I'd still like a good way to investigate this. Thanks for any input.
August 3, 2007 at 12:33 pm
here's one of many ideas you'll get:
since you have the SQL, put it in query analyzer and get the estimated execution plan.(control K in QA)
look at the plan and see if the sql has any table scans, where it is joining on some huge tables (does the query have anything like WHERE NAME NOT IN (SELECT NAME FROM [SOMEBIGTABLE])
table scans will slow any query down, whether it is a select/update/insert or not.
if it is just an insert and nothing else, then you might be experiencing a problem with page splitting based on the clustered index...for example, if the clustered index is on LASTNAME, and you try to insert something between "SAUNDERS" and "SMITH" , and there is no space between them because of the fill factor of the index had no space. it can take a while for SQ<L Server to move data in order to make space to insert a record in that spot.
for
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply