How to Do Performance Tuning in SQL Server

You’re a developer, DBA, or sysadmin stuck with long running queries in SQL Server, and you need to do SQL Server performance tuning. We’ll teach you SQL tuning in a series of easy, free tutorials:

1. Measure how fast the server is going now.
2. Performance tune the queries.
3. Performance tune the indexes.
4. Performance tune SQL Server’s settings.
5. Performance tune the hardware.
6. Finally, if you have to buy new servers, get a best practices setup checklist, and load test before going live.

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 exactly which queries are slow, and why. Let’s start out by

• 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.
• SQL Server Perfmon Tutorial – how to set up Perfmon, what SQL Server Perfmon counters to track, and what the indicators mean.
• Watch Brent Tune Servers – at Microsoft Ignite 2015, he took several SQL Server workloads, found the bottleneck, and then tweaked various settings for quick fixes.

Then, before you start changing things, ask what parts of the server you’re allowed to change. The Manager’s Guide to Tuning SQL Server gives you a simple checklist of what parts you can tweak, change, or replace completely.

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:

• To find your long running queries overall, use the free script sp_BlitzCache. 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.)
• To find out long running queries right now, run sp_BlitzWho. It shows queries from longest-running to shortest, and gives you their execution plans.

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.

Watch Brent tune queries.

Once you’ve found the expensive SQL queries you need to tune, here’s my favorite performance tuning tips:

• Watch Brent Tune Queries – ever wonder how somebody else does it? Peer over Brent’s shoulder in this free SQL Server tutorial.
• 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.
• How to measure performance improvements – Kendra explains how to use statistics from SSMS.
• How to read query execution plans and tune them to make them go faster.