Sporadic performance problem

  • 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

  • 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://www.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

  • 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.

  • 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