You store data in SQL Server, but you don’t have enough work to keep a full-time DBA busy.

In just half an hour, you’ll learn the basics of performance troubleshooting and index tuning from me – a recovering developer myself. I’ll show you the basic care and feeding of a Microsoft SQL Server instance and give you scripts to keep you out of trouble:

In this session, I cover these steps:

1. Start every new table with a clustered index.

A clustered index tells SQL Server, “Here’s how I want you to sort the data.” Without that definition, SQL Server just sloppily throws your data anywhere that it can find space. While that might be awesome for insert speeds, it’s not so good as a universal data structure when we need to make performance tuning as easy as possible.

Generally, your clustered index needs to be on fields that are:

• Narrow
• Unique
• Static
• Ever-increasing

As you learn more about indexes, it’s tempting to micro-optimize them by saying, “Well, I think my end users are going to query this data on a combination of date and warehouse number.” Don’t over-think that before the users start using the app. Index designs are easy to change later.

There are situations where heaps – tables without clustered indexes – can perform faster. Thing is, they have some serious gotchas as Kendra Little explains in this 30-minute video. Heaps also miss out on the magic of SQL Server’s automatically suggested indexes, and that’s especially important for us in the next step.

2. Check your indexes weekly with sp_BlitzIndex®.

As it runs your beautifully crafted queries, SQL Server is constantly gathering index diagnostics. It keeps track of what indexes would have been the most helpful, how many times they would have been used, and how to create those indexes. You can query those diagnostic tables yourself, but frankly, it’s painful.

That’s where the free sp_BlitzIndex comes in. Download it, install it on your production server, and then run it in your database. It gives a funny sanity check on which indexes you could probably drop, and which ones you should add.

Run it, and then cover your eyes. It’s going to produce all kinds of heinous warnings about the awful mess that the last guy stuck you with. I only want you to focus on two warnings:

Warning: Index Hoarding – these are nonclustered indexes that are in place now, but nobody’s using them. SQL Server has decided these indexes just aren’t the most efficient way to get your data. Disable these rather than dropping them, because that way if they’re needed again, you can just enable them again as explained in that link.

(Now would be a good time to mention that if you’ve been bossing SQL Server around by specifying index hints in your queries, you’re gonna have a bad time. Your queries will fail.)

Warning: Missing Indexes – SQL Server wants a copy of your table stored in a different order to make queries go faster. Careful adding these willy-nilly – aim for 5 or less nonclustered indexes per table, and each index should have 5 or less fields on it. Avoid indexing XML, VARCHAR(MAX), NVARCHAR(MAX), or other big data types because the index will be huuuuge.

If you’re on SQL Server Standard Edition, creating indexes can lock your table while you work, so do the actual adding in development first. Time how long it takes, and depending on your dev hardware’s speed and database size, you’ll have a rough idea of what it will look like in production. Then do the doin’ during a maintenance window or after hours. If you’re lucky enough to use SQL Server Enterprise Edition, you can use the WITH (ONLINE = ON) parameter while creating indexes to have a lower impact on your end users.

And remember that first step where we said to start with a clustered index? If you’re thinking SQL Server will recommend the right clustered index for you, think again.

Repeat this process every Friday. See, SQL Server empties out the contents of this diagnostic data whenever SQL Server restarts, and then it constantly keeps piling the data back up. On Friday, even if your server was restarted over the weekend, you’ve got some good diagnostic data to check because users have been hitting the app all week. (This is why you can’t run it in your dev/staging environment and get the same good recommendations.) Within a month or so, you won’t believe the difference in performance.

3. Build the Suckerboard weekly with sp_BlitzCache®.

As it’s executing queries, SQL Server tracks which queries get run most often, and which ones use the most server resources. It’s like capturing a trace of your server, but even better because SQL Server is already doing this for you. You can query the dynamic management views to pull the data out, but ain’t nobody got time for that.

That’s where the free sp_BlitzCache comes in. Download it, install it on your production SQL Server, and then run it with these parameters:

EXEC sp_BlitzCache @top = 10, @sort_order = ‘duration’

This will give you the top 10 queries that have run the longest in total. For example, if a query runs for 10 seconds, and it’s been called 1,000 times, then it’ll have 10,000 total seconds of duration, so it will rank higher than a query that was only ran once and ran for 500 seconds.

This is the opposite of the leaderboard – this is the Suckerboard. It’s the Hall of Shame, the queries that we’re not too proud of. The good news is that it’s usually really easy to fix these once you know what they are. On the far right of sp_BlitzCache’s output, you can click on the query plan to see the query’s execution plan, and you’ll often find things like missing index warnings or zombies.

Repeat the process with a few other parameters to see the highest CPU users, the ones that read the most data, and the ones that ran most frequently:

EXEC sp_BlitzCache @top = 10, @sort_order = ‘CPU’
EXEC sp_BlitzCache @top = 10, @sort_order = ‘reads’
EXEC sp_BlitzCache @top = 10, @sort_order = ‘execution’

Repeat this every Friday and email the list of queries to your team. This has an amazing effect – people see their queries on the Suckerboard, and they immediately wanna work on those queries to get them off the Hall of Shame. It becomes a competition to keep off the Suckerboard.

After a month or so, you’ll be very comfortable with the list of queries. You’ll know them right away at a glance, and you’ll know why they show up – like what kind of work they’re doing. This makes you extremely well-equipped to deal with the next step.

4. When slowness strikes, use sp_WhoIsActive and sp_BlitzFirst®.

When someone comes running in screaming that the SQL Server is slow, run these two stored procedures:

sp_WhoIsActive lists all of the queries that are running right now, ordered by longest-running to newest.

sp_BlitzFirst checks a bunch of common potential issues on your server and warns you if a data file is growing, a transaction is stuck rolling back, a backup is running, and so on.

5. When you need to learn more, here’s what to do.

Indexing resources:

• Explaining Clustered vs Nonclustered Indexes – Jes Schultz Borland simplifies these in a blog post with examples.
• Filtered Index Limitations – these are indexes with a WHERE clause, thereby taking up less space, but…yeah.
• How to Decide if You Should Use Table Partitioning – this advanced indexing technique has a tempting call as you start to scale. Kendra Little explains it.
• More index resources

Plan cache and execution plan resources:

• How to Use sp_BlitzCache® – Jeremiah explains how to query your plan cache and find the most resource-intensive queries.
• How to Get Your Query’s Execution Plan – a 15-minute video that explains a few different methods.
• Parameter Sniffing – if your query suddenly runs slow out of nowhere without any changes, this might be your problem.
• More execution plan resources

And if you’re coming to PASS, we’re holding a performance tuning class on Monday & Tuesday called Make SQL Server Apps Go Faster. If you learned something in this article & video, then you’ll learn a LOT in the class. Read more about it now.

DBAs reading this: stop freaking out.

I know, I’m simplifying a lot of stuff in here. When you read the sentence, “Index designs are easy to change later,” you instantly pop a vein in your forehead because you remember that time you had to change a clustered index on a 1TB data warehouse.

Take a step back and put yourself in the frame of mind of a developer who’s alone in a dark room with an application, a SQL Server, no DBA, no budget, and no time to get everything perfect. I’m trying to give them the tools to get good-enough performance while spending less than 2 hours a week worrying about their database.

Sure, there’s all kinds of interesting scenarios and tricks you’d like me to explain on this page – but remember, the time they spend reading this page and learning techniques counts against their 2 hours of spare time per week. Focus on the things that will truly make a difference in performance, and ease off the academic “well actually” stuff.