Call Us: US - +1 845 478 5244 | UK - +44 20 7193 7850 | AUS - +61 2 8005 4826

Optimize SQL Queries

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.

Brent says, "Watch me tune queries."

Watch Brent tune queries.

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

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

want to make the database structures more efficient so SQL Server works less, here’s our resources:

  • 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.
  • Indexing resources – our favorite blog posts and videos about tuning indexes.
  • SQL Server Book Recommendations – okay, the books aren’t free, but our recommendations are. We pick the best beginner, advanced, and performance tuning books.
  • Table partitioning – this feature seems like a great way to split up big tables, but it comes with some big gotchas.
  • How to tune indexes – and make SQL Server queries go faster.