You’re a developer, DBA, or sysadmin stuck with long running queries in SQL Server, and you need to do SQL Server performance tuning.
I can help! I’m a consultant, and I do this for a living. I don’t keep anything secret – I’ll tell you how you can get started, or if you prefer, you can hire me for help, whichever’s easier. Here are the basic steps:
- Measure how fast the server is going now.
- Performance tune the queries.
- Performance tune the indexes.
- Performance tune SQL Server’s settings.
Let’s get into the details of each step.
1. Measuring SQL Server Performance
When the users come to you and say their queries are slow, you don’t want to just take their word for it: you want to know whether the server is really slow or not, and if so, what to do about it.
- Free script: sp_BlitzFirst® – this free tool is like SQL Server’s speedometer. It shows you how fast SQL Server is going, and which wait types are preventing you from going faster.
- How to Measure SQL Server’s Wait Time – wait time per core per second is the SQL Server equivalent of your car’s dashboard. Forget Perfmon counters. And if it’s low, here’s why your server is bored.
- One-day class: Fundamentals of Server Tuning – this online class teaches you what the top common wait types mean. You can buy the recorded version and start watching it immediately.
2. How to Optimize SQL Queries
If you’ve got an in-house application, and you’re allowed to do query optimization, here’s a few ways to find the the long running queries in SQL Server, then how to increase their performance. First, we need to find which queries to optimize, and here are the query performance tuning tools I use:
- Free script sp_BlitzCache – uses SQL Server’s plan cache to tell you which queries to focus on tuning first. You don’t have to install anything ahead of time, and it works on all supported versions of SQL Server from 2008 forward. It even works in the cloud! It’ll give you your top 10 most resource-intensive queries, plus warnings about why they’re slow.
- Free script sp_WhoIsActive – shows which queries are running right now from longest-running to shortest, and can help you find the lead blocker when things are going wrong.
- Free video: Watch Brent Tune Queries – ever wonder how somebody else does it? Peer over my shoulder in this free SQL Server tutorial.
- One-day class: Fundamentals of Query Tuning – teaches you why SQL Server struggles to build a good execution plan for complex queries, and what you can do to help. After that, you can graduate to my 3-day Mastering Query Tuning class.
- My SQL Server book recommendations – if you’re the kind of person who can absorb by reading entire books, good for you! You can save a lot of money by using books rather than training classes.
You’ll notice that I didn’t say to catch slow running queries with SQL Profiler. Profiler isn’t a good SQL Server performance monitor: it actually causes all queries to run slower. You’re so much better off using the plan cache as shown above.
3. How to Do Index Performance Tuning
If you’re not allowed to optimize the SQL queries, but you still need to speed them up, then you may need to design nonclustered indexes and covering indexes. This can increase the performance of a SQL query without having to rewrite it.
- Free script: sp_BlitzIndex® – run this in your database for a free sanity check. It shows missing indexes, unused indexes, duplicates, heaps, and more, and explains why they’re killing your performance.
- Free video: How to Think Like the SQL Server Engine – the training class that every developer and DBA should start with, explaining the difference between different index types and how SQL Server uses them. It’s also available in written blog post form.
- One-day class: Fundamentals of Index Tuning – where I teach you how to design the right index for a query just by looking at it, and how to pick the column order in indexes. After this, you’re ready for my Mastering Index Tuning class.
4. Performance Tune SQL Server’s Settings and Hardware
Surprisingly, a lot of SQL Server’s default settings can lead to bad performance. Let’s talk through what you need to do:
- Run sp_Blitz® on the server. It’s a free health check that catches many common performance bottlenecks.
- Check our SQL Server Setup Checklist – some simple configuration tweaks can get you 20-30% performance increases right from the start without spending any extra money.
- Sysadmin’s Guide to SQL Server Memory – why is sqlserver.exe using so much memory? How can you tell if it needs more?
- 3-day class: Mastering Server Tuning – but only tackle this one after you’ve been through the above classes, including Mastering Index Tuning and Mastering Query Tuning.
