SQL Server performance tuning boils down to these steps:
Step 1: Capturing 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 with Profiler
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.
- SQLServerPedia’s T-SQL Code Library – includes a great index defrag script, missing index DMV script and indexes-not-in-use script.
- 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:
- 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.
Our Performance Tuning Presentations
Here’s Brent’s Performance Tuning for Race Car Drivers presentation filmed at SQLBits in Great Britain:
- 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 2-3 day SQL Server health and performance check-up that quickly gets to the root cause of issues and shows you how to fix it. Contact us to get relief fast.
