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?
Server Fault is a question and answer site for system and network administrators. It only takes a minute to sign up.Sign up to join this community
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.
First thing? Talk to the user.
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/ ]
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.
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.
To add to the list:
Look in the error log for:
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.
Isolate the problem
Having Isolated to a stored proc or view
scansthat should be
SET STATISTICS IO ONetc) to get baseline performance numbers