x

Performance tuning, where to begin?

I am not new to SQL Server but have mostly spent my time working on consolidation projects, building new environments, doing upgrades, and working on some ETL.

What I seriously lack is the ability to jump on a troubled server and figure out what is or could be causing issues. Standard things like disk I/O, low PLE I can look at. What I don't know how to do is find out which queries are causing issues, where contention is, what queries are eating up my cache, crushing my disk, spiking my CPU, etc.

I have ran the queries that Glenn Barry put out, but once I get some of the results I am not 100% sure what to do with them.

What is the book, webinar, class that I should attend to really build out this skill set?
more ▼

asked Oct 03 '12 at 02:38 AM in Default

Scotty gravatar image

Scotty
350 2 5 5

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

@mike, great plug on the DMV book. I have it in my collection as well. I found another great supplement to that is a book by Grant Fritchey on execution plans. SQL Server Execution Plans Once you find those pesty queires causing the issues you have to tune them (if you can). Knowing how to dissect the execution plan is key.

@Scotty, you also should be looking at the SQL Server specific perfmon counters. They provide a great deal of information about memory and I/O.
more ▼

answered Oct 03 '12 at 02:45 AM

Tim gravatar image

Tim
35.5k 32 40 138

(comments are locked)
10|1200 characters needed characters left
Hey Scotty, depending on your version of SQL, if you are using 2005 and above your best friend are the DMV's. Check out this book by Tim Ford and Louis Davidson Performance Tuning with DMV's. Glenn Barry offers a lot of explainations about his queries in additional blog post as well as others have referenced his material in their own posts. Performance tuning is as much as art as it is science. Hang in there and good luck. I am looking forward to the response from others on here as well. I am always learning myself.
more ▼

answered Oct 03 '12 at 02:41 AM

BradleySQL gravatar image

BradleySQL
555 3 5 9

(comments are locked)
10|1200 characters needed characters left

This is often a problem with troubleshooting scripts, if they pump out a number - bing! 59! - there's often no context as to what that means or what it should be. Is a lower number better or worse? is the range 0-10 or -1000 to + 3million.

Take for example a script that shows the top 10 wait types - even in the best performing server in the world, there will be 10 top wait types, so that information has to be taken, read and understood in context.

I'd also look at Brent Ozar's server takeover script - sp_blitz. Whilst this is not a troubleshooting script per se, it does look for many common issues and not only reports them out but the website has a lot of supporting information about the what, why and where.

more ▼

answered Oct 03 '12 at 08:02 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(comments are locked)
10|1200 characters needed characters left

Might as well plug my other book, SQL Server Query Performance Tuning. There's a 2008 version and a 2012 version. It covers gathering performance metrics on the server, gathering query metrics, reading execution plans, understanding how the optimizer works, statistics, index fragmentation, common problems and solutions, pretty much the hole shebang.

And, I'm teaching a full day pre-conference seminar at the PASS Summit in Seattle this fall on this exact topic. Another way to learn.

more ▼

answered Oct 03 '12 at 12:23 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.9k 19 21 74

(comments are locked)
10|1200 characters needed characters left

Just to chime in a little...

There is Professional SQL Server 2008 Internals and Troubleshooting written by a group of authors including: Brent Ozar, Jonathon Kehayias, and Cindy Goss.

Likewise there is the bible of SQL Server nuts and bolts, Microsoft SQL Server 2008 Internals, by Kalen Delaney, Paul Randal, Kimberly Tripp and Conor Cunningham.

And you've made a good decision by coming here as well. Make sure you also browse the main site. The question for you now is how deep do you really want to go? The paths are available, enjoy the ride.

more ▼

answered Oct 03 '12 at 01:03 PM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x20

asked: Oct 03 '12 at 02:38 AM

Seen: 747 times

Last Updated: Oct 04 '12 at 05:22 PM