Missing-index advice, filtered indexes, columnstore, and over-indexing.
62 associated posts59 primary posts
I know this is gonna sound crazy, but let's say you had a table where people stored all kinds of things in one column: dates, integers, file names, sale prices, file names, you name it. And let's say your application frequently ran a query looking for dates in that column, like this: [crayon-6ae69f2f9a396670029456/] Even if…
When you run a SELECT COUNT(*), the speed of the results depends a lot on the structure & settings of the database. Let's do an exploration of the Votes table in the Stack Overflow database, specifically the 2018-06 ~300GB version where the Votes table has 150,784,380 rows taking up ~5.3GB of space.
I'm going to measure each method 3 ways:
Before I give you the link to the next research paper in this week's series, I wanna give you a few questions to think about: If you had to build something to automatically add indexes to SQL Server databases, where would you start? What data would you use to make your decisions? How would you…
This week, I'm sharing some of my favorite papers that I've read. Sometimes they're about future technologies that haven't shipped yet - and may never ship! Sometimes, like this one, they're not from Microsoft at all, but from someone else in the industry who's explaining a problem that we face in SQL Server. SQL Server…
Graduates of my Mastering Index Tuning class will already be familiar with the handful of ways the missing index DMVs and plan suggestions are just utterly insane. Let's add another oddity to the mix: the usage counts aren't necessarily correct, either. To prove it, Let's take MattDM's Stack Exchange query, "Who brings in the crowds?" He's…
Bit Of A Kick
I've been playing with indexed views a little bit lately for some demos in my Bits Precon.
There are a whole bunch of limitations in creating indexed views. One of them is that you can't base the query on DISTINCT.
Accidental Haha
While working on some demos, I came across sort of funny behavior during indexed view creation and how the indexes you have on the base tables can impact how long it takes to create the index on the view.
Starting off with no indexes, this query runs in about six seconds.
[crayon-6ae69f2fa6384217948701/]
Here's the plan and the query stats:
At First I Was Like... This won't work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they're from stored procedures, dynamic SQL, or in databases with forced parameterization enabled. Unfortunately, it seems limited to filtering out NULLs…
This week I'm running a series of posts introducing the fundamentals of index tuning. Yesterday, I used Brent's 5 and 5 Rule to explain why you want to aim for around 5 indexes per table, and 5 columns (or less) per index. Now, how do we get from our current database situation to a better place?
When someone hands me a database and tells me to make it go faster without changing the code, I start with my D.E.A.T.H. Method:
During our Critical Care® sessions with clients, we often see unused indexes (reads=0) with high writes. Sometimes these unused indexes have very high writes. If an index is never being used to help with performance but SQL Server is having to maintain it for INSERT, UPDATE and DELETE operations, then why keep the index around?
We got a phenomenal series of questions from a client, and I wanted to encapsulate the answers into a blog post to help more folks out: Should all index changes require testing in a staging environment, no matter how big or small? What would be a reasonable timeline duration from index identification to deployment? What…
Warning: I'm about to overly simplify a whole lot of topics to make things easy. Armchair architects, warm up your flamethrowers.
Your table has rows and columns kinda like a spreadsheet:
This week, Erik and Richie discuss monitoring tools, finding all unused tables across databases, query tuning, deleting vs hanging on to indexes, sharding databases, query editors, aggressively-locked indexes, why a plan would not be in the plan cache, and Richie's current housing situation.
Here’s the video on YouTube:
This week, we're sharing Instant Replay videos from our training classes. Next up is Pinal Dave talking about missing index requests from the dynamic management views. It doesn't matter whose script you use - they're all built atop SQL Server's missing-index DMVs, and they all have the same limitations. Treat the missing index requests the same way you would treat appetizers in a restaurant - if you order an appetizer and it's no good, don't order more dishes.
SQL Server Management Studio only shows you the first missing index recommendation in a plan.
Not the best one. Not all of them. Just whichever one happens to show up first.
sp_BlitzIndex gives your indexes a psychiatrist-style evaluation. It's a lot like a real psychiatrist: it's just flagging behaviors, and there's not necessarily anything wrong with being a hoarder, or a workaholic, or a furry. They're just behavioral traits. Let your freak flag fly. One of the warnings is "Aggressive Indexes," which means sys.dm_db_operational_stats reports minutes of…
During Training
We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching.
What's back-searching? It's a word I just made up.
I'm so glad you asked! Let's take a look. Open up the Stack Overflow database, turn on actual execution plans, and run a query that will cause SQL Server to beg and plead for an index:
[crayon-6ae69f2fa80a8188870435/]
And we get a missing index recommendation in the plan:
Now create the index, and try the query again:
[crayon-6ae69f2fa80ad399521163/]
Oh, don't act like you haven't done that. And sure enough, SQL Server uses the index:
I was a bit surprised that key lookups were a possibility with ColumnStore indexes, since "keys" aren't really their strong point, but since we're now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should get a closer look.
That's when it all gets blown away
At one point or another in everyone's SQL-querying career, they end up writing a query that goes something like this:
[crayon-6ae69f2fa9693546875216/]
These are often called optional parameters, and if you spend any time looking at queries, this will make you shudder for many reasons. Poor cardinality estimates, full scans, etc.
