Indexing

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. Fair enough, but you can do GROUP BY. And what’s pretty…

Indexed View Creation And Underlying Indexes

December 4, 2018

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. Transact-SQL DECLARE…

Tales From Overindexing: Too Many One Column Indexes

November 29, 2018

Execution Plans, Indexing, SQL Server

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. Usually. Sometimes, there’s just really weird indexing. Here’s A Thing, Look At It Promises This is a query against one table. No views, no functions, no…

Adventures In Foreign Keys 5: How Join Elimination Makes Queries Faster

November 16, 2018

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. Depending…

Adventures In Foreign Keys 4: How to Index Foreign Keys

November 15, 2018

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…

How Check Constraints MIGHT Improve Your Queries and Missing Index Requests

October 31, 2018

The more SQL Server knows about your data, the better your query plans can get. Say you’ve got an app that’s designed to store multiple companies in a single database – but you don’t actually use it that way. All of the data in a given database is actually for the same company. We’ll use…

Filtered Indexes vs Parameterization (Again)

October 26, 2018

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…

Batch Mode For Row Store: Does It Fix Parameter Sniffing?

October 25, 2018

Batch Mode, Execution Plans, Indexing

Snorting The Future SQL Server 2019 introduced batch mode over row store, which allows for batch mode processing to kick in on queries when the optimizer deems it cost effective to do so, and also to open up row store queries to the possibility of Adaptive Joins, and Memory Grant Feedback. These optimizer tricks have…

Index Tuning Week: Missed Missing Index Opportunities

October 12, 2018

A Reminder With a new baby in the house, I often find myself comparing her to various things. Barnacles. Ham. Pugs. No, I swear, I love her. SQL Server Has A Baby, Too That baby is the optimizer, and when it starts crying, it uses missing index requests to tell you something is wrong. Usually.…

Index Tuning Week: Getting Blocking? Play “Hot or Not.”

October 11, 2018

Deadlocks, Indexing

This week, we’re all about tuning indexes. So far, we’ve covered Brent’s 5 and 5 Rule and The D.E.A.T.H. Method. Today, let’s talk about reducing blocking and deadlocking. Normally, when we think about the causes of blocking or deadlocks, we use badly written queries that modify different tables in the wrong order, resulting in a Mexican…

Index Tuning Week: My D.E.A.T.H. Method for Tuning Indexes

October 9, 2018

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…

Index Tuning Week: How Many Indexes Are Too Many?

October 8, 2018

This week I’m running a series of posts introducing the fundamentals of index tuning. First up, let’s talk about understanding how many indexes make sense on a table. The more nonclustered indexes you have on a table, the slower your inserts and deletes will go. It’s that simple. If you have a table with 10…

Unused Indexes – Are they really unused or have they just not been used YET?

October 2, 2018

Tara Kizer

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?…

Should Index Changes Require Change Control?

September 14, 2018

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…

Forwarded Fetches and Bookmark Lookups

September 7, 2018

Base Table When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches — SQL Server’s little change of address form for rows that don’t fit on the page anymore. This typically isn’t a good thing, though. All that jumping around means extra reads and CPU…

It’s Okay If You Don’t Create Statistics.

September 6, 2018

Along with the ability to create indexes (which you most definitely should be doing), SQL Server gives you the ability to create statistics. This helps SQL Server guess how many rows will come back for your searches, which can help it make better decisions on seeks vs scans, which tables to process first, and how…

Single-Column-Key Missing Index Recommendations are Usually Wrong.

September 4, 2018

When you’re looking at an index recommendation – whether it’s in an execution plan or the missing index DMVs – it helps to understand Clippy’s blind spots. Let’s start with the small StackOverflow2010 database so you can follow along. (It’s just a 1GB direct download, and it expands to a 10GB database that reflects StackOverflow.com…

Tall Tales From Table Variables

August 23, 2018

Secret Squirrel When you modify a table with multiple indexes, SQL Server may choose either a narrow plan, if it doesn’t think all that many rows are going to change, or a wide plan if it thinks many will. In narrow plans, the work SQL Server has to do to modify many indexes is hidden…

A Visual Guide to Choosing an Index Type

August 7, 2018