Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

closed as not a real question by Chris S Feb 16 '12 at 3:23

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

8 Answers

up vote 4 down vote accepted

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/

share|improve this answer
add comment

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.

share|improve this answer
    
Also worth checking if your RAID 5 is showing a fault. You can get "drops like a stone" performance hit if your RAID array loses a disk and a rebuild is in place, or the battery backed write cache goes on the blink. –  Guy Nov 30 '10 at 22:34
add comment

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

share|improve this answer
    
Looks very interesting, although your blog post doesn't say what these numbers represent (Are they per/sec numbers? Or cumulative values? Values accumulated since when? How do I know what's good or bad?) I just need to dig into books online to see what those numbers represent. –  BradC May 12 '09 at 14:00
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
re: long query - what logging are you referring to? A trace? Or is there a trace flag? –  Sam May 14 '09 at 23:52
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.