index-tuning

This question came up in a webcast recently: if a filtered index is causing data modifications to fail, does disabling the filtered index fix the problem?

I recently got a great question: if I order by a column where all rows in that column have the same value, will SQL Server then order the results by the clustered index key?

I’m very that my session on table partitioning from the 2017 SQL PASS Summit is being featured as one of the “Best of Summit” videos.

I had a terrific time presenting this session, thanks to everyone in the audience: you were awesome!

You can watch the video here, and follow the link above to see even more great videos from the conference for free.

Last week’s Quizletter featured a quiz on keys vs included columns in SQL Server. I got a great question from a reader:

What do you consider a good reference and/or description of indexes for SQL Server? For example where would you have documentation that explains how the answer to #1 is right?

I received a question recently asking about disk access patterns for index seeks in SQL Server. The question suggested that index seeks would have a random read pattern.

Is this necessarily the case?

I had a great time giving a session recently for the 24 Hours of PASS - Summit Preview Edition.

When you modify the indexes on a table, SQL Server needs to reconsider how it executes queries that reference that table. But the way this appears when you’re looking at your execution plan cache is far from obvious: Impacted query plans are NOT removed from cache at the time you change an index. The next time the query is executed, it will be recompiled, and the new plan and related execution information will be placed in cache.

Nonclustered indexes are awesome in SQL Server: they can get you huge performance gains.

But we can’t always create the perfect index for every query. And sometimes when SQL Server finds an index that isn’t quite perfect and decides to use it, it might make your query slower instead of faster.

I recently got an interesting question from a reader about running a CREATE INDEX statement with DROP_EXISTING

Here’s a great recent question that I got about query tuning and index use:

Assuming that the documented levels of data type precedence in SQL Server are true as of SQL 2016, why does a bigint value not force an index scan when compared against an int column?

Indexes help queries run faster in SQL Server for several reasons. One of those reasons is that indexes can help your update and delete statements lock fewer rows. And I’m not only talking about shared locks, either.

SQL Server has tools that suggest indexes– and they’ll even auto-create the indexes for you.

I’m not a huge fan of these tools because they’ve got some notable flaws: they lead to creating more indexes than you need, and they aren’t super smart about the indexes they recommend.

Ever see those little green messages at the top of an execution plan?

Those are missing index suggestions.

SQL Server loves to suggest that you consider changing up your index game.

The word ‘statistics’ is awfully confusing in SQL Server

It can mean “statistics” themselves – little objects that describe the distribution of data in columns or indexes to help the optimizer.

Or it can mean “usage statistics” -  dynamic management views that let you see how many times an index has been used or requested, how many times a query has been run, that kind of thing.

The hardest thing about looking at index requests in SQL Server is understanding what the column names mean.

Optimizing queries is the most fun when you don’t need to add indexes. There’s nothing quite so nice as finding a way to make reading data faster, without slowing down writes or creating new data structures that need to be maintained.