February 5, 2006 at 3:22 pm
Hi,
I have an application that runs on SQL Server 2000. Mostly the application runs fine but under heavy usage some queries can take upto 7 seconds to run when normally they run in miliseconds. how can I go about troubleshooting this, I guess with profiler, but what should I be looking for? Any help would be greatly appeaciated.
Thanks
February 6, 2006 at 2:42 pm
You can use performance monitor to analyize the load on the server. See if the server is running slugish before and during the query is execution. http://qa.sqlservercentral.com/columnists/sjones/performancemonitoringbasiccounters.asp gives a list of basic counters to check.
Check SQL Agent's jobs and see if anything is running at that time. Maybe some job is locking a table or eating up tons of resources.
Another user/application could be sending a large query slowing down the server or locking the table. You can use sql profiler to view tranasctions.
-c
February 7, 2006 at 7:53 am
I would look for table locks and blocked spids. This is typically the reason why a query that typically takes miliseconds takes significantly longer. If another query/user has a lock on an object that your query needs it will simply wait untill the lock is freed. You can look at the sp_lock stored procedure and the syslockinfo system table to see what locks exist. Also, we (Quest Software) have some very good products for diagnosing/pinpointing performance problems including some locking and blocking analysis tools. Feel free to contact me if you'd like any more info about them.
February 7, 2006 at 10:08 am
If the obvious locking/blocking checks do not reveal anything ...
Here's my 'short list' for tuning:
Round I
DBCC UPDATEUSAGE
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round II
DBCC DBREINDEX
UPDATE STATISTICS (with FULL scan) for all tables
exec sp_recompile for all tables
exec sp_refreshview for all views
Round III
Profiler
Query Hints
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply