Designing, evaluating, maintaining, and troubleshooting indexes and statistics.
568 associated posts270 primary posts
“Throughout history, slow queries are the normal condition of man. Indexes which permit this norm to be exceeded — here and there, now and then — are the work of an extremely small minority, frequently despised, often condemned, and almost always opposed by all right-thinking people who don't think bit columns are selective enough to…
5. Turning on auto-shrink It might make disk space alerts go away for a minute, but it can cause lots of problems. 4. Enabling xp_cmdshell and giving the SQL Server account administrative rights in Windows Young DBAs often do this to try to get a quick fix in place to manage backup files. Later on,…
SQL server loves unique indexes
Why? Because it's lazy. Just like you. If you had to spend all day flipping pages around, you'd probably be even lazier. Thank Codd someone figured out how to make a computer do it. There's some code below, along with some screen shots, but...
TL;DR
Clustered indexes are fundamental
And I'm not just saying that because Kendra is my spiritual adviser!
They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that's for another time. A long time from now. When I've raised an army, in accordance with ancient prophecy.
i've seen this happen
Especially if you've just inherited a database, or started using a vendor application. This can also be the result of inexperienced developers having free reign over index design.
Unless you're running regular health checks on your indexes with something like our sp_BlitzIndex® tool, you might not catch immediately that you have a heap of HEAPs in your database.
During some testing with SQL Server 2014's new cardinality estimator, I noticed something fun: the new CE can give you different index recommendations than the old one. I'm using the public Stack Overflow database export, and I'm running this Jon Skeet comparison query from Data.StackExchange.com. (Note that it has something a little tricky at the…
I've been doing SQL Critical Care® work with clients for over a year now. It's absolutely true that every client's situation is different and needs special attention. However, I've found that there's a short list of issues that nearly all clients have in common. Drawing from that list, here are the top three high-impact, low-effort areas you can work…
It's not glamorous And on your list of things that aren't going fast enough, it's probably pretty low. But you can get some pretty dramatic gains from indexes that cover columns you're performing aggregations on. We'll take a quick walk down demo lane in a moment, using the Stack Overflow database. Query outta nowhere! [crayon-6a6f35a05273c285728992/]…
Hooray Windowing Functions
They do stuff that used to be hard to do, or took weird self-joins or correlated sub-queries with triangular joins to accomplish. That's when there's a standalone inequality predicate, usually for getting a running total.
It's common to try to build the perfect index for a query. This index only uses the exact right columns for the key columns and other, presentational, data is pushed to the INCLUDE section of the index. While you can create a narrow key this way, the entire index row can become wide.
A Sample Report
We had some great questions about trivial execution plans in SQL Server in our Advanced Querying and Indexing class a few weeks ago. Here's a little glimpse into what we talked about.
For really simple queries, SQL Server can use "trivial optimization". If there's a very limited number of ways to run the query, why do a bunch of fancy, CPU burning cost-based optimization? Just chuck the plan at the query and let it go!
When you create an index in SQL Server with multiple columns, behind the scenes it creates a related multi-column statistic for the index. This statistic gives SQL Server some information about the relationship between the columns that it can use for row estimates when running queries.
But what if you use 'included' columns in the index? Do they get information recorded in the statistics?
If you've got indexed views, or are thinking of using them, this is important.
First, go read the blog post, "An Indexed View Bug with Scalar Aggregates" by Paul White. It will take a minute, but it's important to understand.
Have you ever been watching queries crawl, feeling sort of helpless because you can't change them? And even if you could, it's hitting a new custom column in an ISV table that no one bothered to tell you about. I've been there. I've been pomade deep there.
I've also had my Robot tell me: "there's an index for that, Erik."
Learn the basics of Foreign Keys in SQL Server: trust, join elimination, cascading deletes, and indexing child keys. Limited on time? Check out the Table of Contents below.
https://www.youtube.com/watch?v=BkNOQgV5Zv8
You can't do everything with filtered indexes in SQL Server. For instance, you can't create the following index:
[crayon-6a6f35a053a08936506056/]
If you try, you'll get the error message:
[crayon-6a6f35a053a0d859617790/]
Instead, you can use 'IN' and create the index this way:
[crayon-6a6f35a053a0f723982394/]
That works-- and good news, even queries written with 'OR' can use that filtered index, because SQL Server is clever like that. Here's an execution plan that shows it in action.
One of the blocking scenarios I find most interesting is related to online index rebuilds. Index rebuilds are only mostly online. In order to complete they need a very high level of lock: a schema modification lock (SCH-M).
Here's one way this can become a big problem:
tl;dr -- Not necessarily.
One of our students asked me a great question: if you update statistics on every table in the database, is that effectively the same as dumping the procedure cache on the instance? Will every execution plan have to be recompiled the next time it is run? I thought it was a great question and it spurred an interesting discussion about maintenance and recompilation.
If you're a production database administrator responsible for backups, corruption checking, and index maintenance on SQL Server, try Ola Hallengren's free database maintenance scripts. They're better than yours (trust me), and they give you more flexibility than built-in maintenance plans.
However, the index maintenance defaults aren't good for everyone. Here's how they ship:
[crayon-6a6f35a053efd162543224/]
The defaults on some of these parameters are a little tricky:
Words are hard. Acronyms are even harder. Here's the words that give me the most trouble when talking to people about SQL Server. And thanks to all my Twitter friends quoted here for their suggestions!
Replication
"I have a question about replication..."
"Which kind? Transactional? Merge? Peer to Peer?"
