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.
Performance Tuning Steps for Existing Servers
When the server’s already in production, start by gathering metrics:
- Perfmon Tutorial for SQL Server DBAs - how to set up Perfmon, what SQL Server Perfmon counters to track what the indicators mean.
- Poor Performance Checklist – Jeremiah’s 5 things that fix bad SQL Server performance.
- Sysadmin’s Guide to SQL Server Memory – why isn’t sqlserver.exe using much memory? How can you tell if it needs more?
- 7 Things Developers Should Know About SQL – including why functions rarely perform well, why WITH NOLOCK doesn’t mean there’s no locking, and more.
Free SQL Server Performance Tuning Tools
The best things in life are free. Here’s our favorites:
- sp_Blitz® – free health check that catches common performance bottlenecks.
- sp_BlitzIndex® - finds missing indexes, unused indexes, heaps, and more.
- sp_BlitzCache™ – finds the worst queries in the plan cache.
- 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.
- SQL Server Book Recommendations – okay, the books aren’t free, but our recommendations are. We pick the best beginner, advanced, and performance tuning books.
Building and Load Testing New SQL Servers
When you’re planning how big of a server to build, start with Brent’s video from TechEd 2012, Building the Fastest SQL Servers. He explains the two common access patterns for databases (OLTP and data warehousing) and gives you resources on what hardware to pick for each.
For data warehouses, 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.
Once the server is built, here’s our favorite resources for load testing and setup:
- How Fast is Your Storage? – I show you how to use the free CrystalDiskMark tool to do a fast load test on solid state drives and SANs.
- 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.
- SQL Server Setup Checklist - some simple configuration tweaks can get you 20-30% performance increases right from the start without spending any extra money.
- 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?
Our Performance Tuning Training
Video: How to Think Like the SQL Server Engine - You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos with Brent Ozar, you’ll learn how the SQL Server engine looks at your indexes and builds your query results. Learn more and watch a preview, or buy it now.
Video: How to Tune Indexes and Speed Up SQL Server - You want to become an expert at index tuning, but you can’t find a good start-to-finish training course. In this six hour course packed with demos and quizzes, Microsoft Certified Master Kendra Little teaches you how to design clustered, non-clustered, and filtered indexes to speed up your SQL Server queries– and how to gather the right data to prove your changes made a difference. Learn more and watch a preview, or buy it now.
In-Person Training Class: SQL Server Performance Troubleshooting – You’re a developer. You need to speed up a database server that you don’t fully understand – but that’s about to change in three days of learning and fun. Learn more.
Free 30-Minute Performance Tuning Videos – we run free weekly webcasts on performance tuning and high availability. Check out past videos here.
SQL Server Performance Tuning Consultants
We specialize in making Microsoft SQL Server fast and reliable.
Our clients are diverse: dot-com sites, hedge funds, hospitals, software vendors, and all the way down to small 1-2 person shops. They all have one thing in common: they’re frustrated with SQL Server, and they need fast, cost-effective answers.