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…
Update May 20 - make sure to read to the end for an update.
Okay, look, it's a mouthful of a blog post title, and there are only gonna be maybe six of us in the world who get excited enough to check this kind of thing, but if you're in that intimate group, then the title's already got you interested in the demo. (Shout out to Riddhi P. for asking this cool question in class.)
Somewhere along the way in your career, you were told that:
Index seeks are quick, lightweight operations
Table scans are ugly, slow operations
SQL Server 2019 adds resumable online index creation, and it's pretty spiffy:
[crayon-6a6366d6af491004215423/]
Those parameters mean:
ONLINE = ON means you've got the money for Enterprise Edition
RESUMABLE = ON means you can pause the index creation and start it again later
MAX_DURATION = 1 means work for 1 minute, and then gracefully pause yourself to pick up again later
When you index a nullable field, are the rows with nulls stored in the index? It's easy enough to find out by creating a table with a nullable field, and then creating an index on it: [crayon-6a6366d6af986085609423/] Here's the plan: Yep, SQL Server stores the nulls in the index. That makes sense, really, because sooner…
Michael J. Swart posted an interesting question: he had a large table with 7.5 billion rows and 5 indexes. When he deleted 10 million rows, he noticed that the indexes were getting larger, not smaller.
Here's one way that deletes can cause a table to grow:
Azure SQL DB's Automatic Tuning will create and drop indexes based on your workloads. It's easy to enable - just go into your database in the Azure portal, Automatic Tuning, and then turn "on" for create and drop index:
Let's track what it does, and when. I set up Kendra Little's DDL trigger to log index changes, which produces a nice table showing who changed what indexes, when, and how:
You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In this series of videos, you’ll learn how the SQL Server engine looks at your indexes and builds your query results.
You're a developer who needs to make your application go faster, and you don't want to (or can't!) change the queries. You've never been to an official training class, and your manager isn't about to spring for travel.
In my new Fundamentals of Index Tuning course, you'll learn:
If you're a graduate of my free How to Think Like the SQL Server Engine course - and you'd better be, dear reader - then you're vaguely familiar with DBCC SHOW_STATISTICS. It's a command that shows you the contents of a statistics histogram. When I'm doing the first two parts of the D.E.A.T.H. Method - (D)eduplicating identical…
Grouple On
At the December 2018 GroupBy I had the honor of co-presenting with one of the smartest guys around, Joe Obbish.
Here's the video of our session, "The Problems With Optional Operators", and a link to the resources. Enjoy!
Resources:
To warn you ahead of time, this post is an exploration without an answer (yet). There's some interesting stuff in here, but no conclusions. If that's not your kind of post, feel free to skip it. If it is, well, here goes nothin'... Wild, Wild Life Creating indexes is kind of a funny thing. By…
!erehT iH
I know what you're thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster.
Just watch the video.
Say I've got a function in my WHERE clause:
[crayon-6a6366d6b1cce499040952/]
If I have an index on DisplayName, will SQL Server use it? Sure:
Even though SQL Server can't seek to "Brent Ozar," it will still scan that entire index, along the way trimming the leading & trailing spaces from every user's DisplayName.
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-6a6366d6b3115746229937/]
Here's the plan and the query stats:
Master Plan
Sometimes you see a query, and it's hitting one table, and then the query plan looks like a vase full of spaghetti.
Usually, there's a view involved.
FINALLY...
This is the last post I'll write about foreign keys for a while. Maybe ever.
Let's face it, most developers probably find them more annoying than useful, and if you didn't implement them when you first started designing your database, you're not likely to go back and start trying to add them in.
This week, we're all about foreign keys. So far, we set up the Stack Overflow database to get ready, then tried to set up relationships, and encountered cascading locking issues.
Dawn Of The Data
I don't know how long the recommendation to index your foreign keys has been a thing, but I generally find it useful to abide by, depending a bit on how they're used.
Legendreary
In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.
I didn't even get to the point where I wanted to test all those relationships.
