SQL Server performance triage? [closed]

Question

If you get a call/page about a SQL server performance issue, what do you do first?

Start a trace? Pop up Perfmon? Open activity monitor?

Each of these are useful tools, but what's your "sequence" of troubleshooting?

Answer 1

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/

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

Answer 3

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/ ]

Answer 4

Use SQLIO. You can read a whole bunch of stuff about which switches/incantations to use, but just running it against the drives where your MDF/LDFs are stored (e.g. sqlio.exe /dCDE) will give you a great headline "MBs/sec" reading for comparisons. I tracked down our app's poor performance at a client's site down to disk/LUN contention on a Hitachi AMS (fibre channel disk system). My laptop disk performed 10x better! Soon as our db got its own LUN, our app was happy again.

Answer 5

Check the log files, especially any 'long query' logging you might have on. If it's not on, turn it on.

Check general machine health - it might not be the SQL server, it might be the overall machine - check for runaway processes, overall cpu usage, overall memory usage - you do graph these things with cacti or the like for historical comparisons, right? So check the graphs and see when whatever's slow started being slow.

Answer 6

To add to the list:

Look in the error log for:

• indications of corruption (823s, 824s, 825s)
• indications of IO perf issues (IOs taking longer than X errors)
• latch contention problems im tempdb
• SQL Server working set being paged out on a 64-bit box

Check for index fragmentation. Check you have auto-grow set appropriately and instant initialization enabled. Check you don't have auto-shrink enabled.

Lots of info about all of these on my blog - can post links if you want.

Answer 7

Isolate the problem

1. Check the network is ok
2. Check the server machine isn't being hammered
3. Check the event log
4. Check for locked sql server resources (sp_lock or sys.dm_tran_locks)

Having Isolated to a stored proc or view

1. Check for obvious problems in code e.g. @tables should be #tables
2. Check the execution plan for scans that should be seeks
3. Isolate the SQL that runs slowest
4. Turn on IO and CPU statistics (SET STATISTICS IO ON etc) to get baseline performance numbers
5. Frig around with the SQL, indexes etc
6. Measure IO and CPU performance again
7. If not sufficiently improved goto 5 else goto pub

Answer 8

It depends on the issue, but the first thing I normally do is look at the code to see if it is the cause of inefficiency. I normally then check the affected tables and make sure they have all the appropriate indexes etc.