SQL Server Performance Tuning

If you’re building a new SQL Server from scratch, start by checking out Microsoft’s Fast Track Reference Architectures.  These detailed instructions tell you exactly how to configure hardware, storage, and even SQL Server settings for maximum velocity.

Go to the Microsoft Fast Track Landing Page, but BEFORE YOU CLICK, this page has a lot of marketing stuff, and you need to know what you’re looking for. Focus on the Reference Configurations and Configuration Guides.  There are some vendor-neutral ones from Microsoft, and then there’s vendor-specific guides from Dell, HP, IBM, etc. You may have to scroll all the way to the bottom to see the vendor-specific stuff.

On the other hand, if you want to make an existing SQL Server go faster, here’s the steps:

Step 1: Capture Performance Metrics

To gather the metrics, you can start by using free tools from Microsoft and others.  In these tutorials, I explain how they works, and I’d suggest going through ‘em in this order:

• Perfmon Tutorial for SQL Server DBAs – how to set up Perfmon, what SQL Server Perfmon counters to track what the indicators mean.
• How to Test SAN Performance with SQLIO – SQLIO is probably the worst-named tool in history: it has nothing to do with databases. It’s still really useful to test bottlenecks though.
• Bottlenecks and Bank Balances – is page life expectancy of 214 a good thing or a bad thing?  What do metrics really mean?  There’s only one answer: it depends.
• How to do SQL Server Load Testing – should you use canned load scripts or watch your users in production?

Step 2: Find the Problem Queries

Track down the root cause of the bad Perfmon metrics.  After you focus on a particular counter as the weak point, here’s a tips to fix bottlenecks:

• SQL Server Profiler 101 Video Tutorial – at SQLServerPedia.
• ClearTrace – my favorite tool to make it easier to analyze Profiler trace results.
• Find Slow Queries with sp_WhoIsActive – this free replacement for sp_who shows you running queries along with their plans and metrics.
• Find the Top Resource-Intensive Queries in the Plan Cache – make sure to only select the top 20 queries or so, otherwise it’ll take tens of minutes on busy servers.
• Gather Traces Before You Upgrade or Change Your SQL Server – you’d be amazed at what pops up that might stop your upgrade.

Step 3: Define Mitigations for the Problem Queries

Give your management some options on how to fix the performance problem:

• SQLServerPedia’s T-SQL Code Library – includes a missing index DMV script and unused indexes script.
• Sample Performance Mitigation Scenarios Video – I walk through several common performance scenarios and show how to fix ‘em.
• SQL Server Setup Checklist – some simple configuration tweaks can get you 20-30% performance increases right from the start without spending any extra money.
• Primary Keys and Indexes – I explain the concepts behind keys and indexes using phone books as an example.  Indexes have huge impacts on performance, and if you master these you can make your server go a whole lot faster without spending more money.

Live Performance Troubleshooting

Need to troubleshoot when it’s already gone into production?  Check out our favorite tools:

• ClearTrace - a BI-style slice and dice utility for your trace files.
• Plans in the Cache – make sure to use SELECT TOP 20, and play with the ORDER BY.
• sp_WhoIsActive – catches queries running slow right now.
• Wait Stats Sample – 30-second sample of your current wait stats.

Our Performance Tuning Presentations

If you’ve attended one of our recent conference sessions, here’s the slide deck:

• 2012 Fall DevConnections Vegas – Brent Ozar’s Building Faster SQL Servers (PDF)
• 2012 Fall DevConnections Vegas – Brent Ozar’s Developer’s Guide to SQL Server (PDF)

Here’s Brent’s Performance Tuning for Race Car Drivers presentation filmed at SQLBits in Great Britain:

http://www.vimeo.com/10728198

• SQL Server Index Tuning Tutorial Video – four-part video series covering advanced index tuning.  After the video finishes, look at the list of videos immediately below and you can pick the next session.
• Why You Need to Baseline – video presentation at SQLServerPedia about why you need to regularly capture performance statistics on your servers.

Want more help?  We’re here for you.

• We offer a fast SQL Server health and performance check-up that quickly gets to the root cause of issues and shows you how to fix it.
• We deliver fun, action-packed in person training. Join our two day SQL Server training for Developers.
• Need something else? Contact us to get SQL pain relief.