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.

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.

4. Performance Tune SQL Server’s Settings

Surprisingly, a lot of SQL Server’s default settings can lead to bad performance. Let’s talk through what you need to do:

1. Run sp_Blitz® on the server. It’s a free health check that catches many common performance bottlenecks.
2. 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.
3. Review the Poor Performance Checklist – Jeremiah’s 5 things that fix bad SQL Server performance.
4. Sysadmin’s Guide to SQL Server Memory – why isn’t sqlserver.exe using much memory? How can you tell if it needs more?

5. Performance Tune the Hardware

Hey, sometimes, throwing hardware at it is the fastest, easiest, and cheapest fix.

• What’s the Smallest SQL Server You Should Build? – Brent explains why 96GB RAM really isn’t all that much – especially when you compare it to SQL Server licensing costs.
• SSD RAID Load Testing Results – Exactly how fast are off-the-shelf consumer SSDs, especially when you put them behind a RAID controller?
• Shared Storage for SQL Server – we do a lot of SAN tuning, and we’ve gathered our favorite resources.
• Virtualization Best Practices – licensing, recoverability, and performance configurations.
• Virtualization, SANs, and Hardware for SQL Server – a 7-hour online class teaching how to set things up and tune them.

6. Building and Load Testing New SQL Servers

When you’re planning how big of a server to build, start with Brent’s video from Microsoft 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.

Need SQL Server consulting?
We’re 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 their SQL Servers, and they need fast, cost-effective answers.

You’ve probably seen us present at conferences around the world, and you might have even learned something. You’ll love our in-person training classes and training videos.

Want faster, personalized help? We offer a 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 now to learn more.