SQL Server performance triage? [closed]

Depends.

Is this a sudden, out-of-the-blue performance is dead slow issue, or it is a long term case of general poor performance?

If it's the former, I'll start with the execution-related DMVs (sys.dm_exec_requests). Is there any blocking? Is there a query that's running excessively long or using massive resources. Basically I'll be looking for something abnormal (note, to be able to see that, I must know what's normal)

If it's the latter, I'll use the method that I wrote about in these articles.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

First thing? Talk to the user.

What's changed?

Is this poor performance a new thing, has it come on suddlenly or just building up?

What do your historical reports show (SQLH2) over the last month?

Check the server for disk space, ram usage, cpu usage (that order). Was the server setup correctly in the first place (ram settings) - taskmgr.

Check performance yourself. Does it seem slow to you?

Has the user got used to 'instant' performance from a new system (no data) and now it's starting to drag?

...Then you can start investigating possible problems. You have to establish a benchmark first otherwise you'll never know when your finished.

Before I even do all the above suggestions, I would do this:

SELECT 

        @@total_read AS 'Total Read', 
        @@total_write AS 'Total Write', 
        @@total_errors AS 'Total Error',
        @@io_busy AS 'IO Processing Time (ms)',
        @@cpu_busy AS 'CPU Processing Time (ms)',
        @@idle AS 'Idle Time (ms)'

[I am referencing my blog post: http://dbalink.wordpress.com/2009/04/28/monitoring-sql-server-performance-quickie-edition/ ]

SQL Server performance triage? [closed]

Related

Recent Posts