The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it:
- Creates a special schema to house a temporary object,
- Creates a special stored procedure to run the code,
- Calls said stored procedure,
- Generates a bunch of PRINT statements that serve as the output (along with new line support for readability),
- Cleans up the stored procedure it generated,
- And finally deletes the schema it created.
Click through for the script, as well as a bonus Powershell script. Because hey, it’s only six lines of code.
The original question we wanted to answer was whether we would see a performance difference when a query used the index with all columns in the key, versus the index with most of the columns included in the leaf level. In our first set of tests there was no difference, but in our third and fourth tests there was. It ultimately depends on the query. We only looked at two variations – one had an additional predicate, the other had an ORDER BY – many more exist.
What developers and DBAs need to understand is that there are some great benefits to including columns in an index, but they will not always perform the same as indexes that have all columns in the key. It may be tempting to move columns that are not part of predicates and joins out of the key, and just include them, to reduce the overall size of the index. However, in some cases this requires more resources for query execution and may degrade performance. The degradation may be insignificant; it may not be…you will not know until you test. Therefore, when designing an index, it’s important to think about the columns after the leading one – and understand whether they need to be part of the key (e.g. because keeping the data ordered will provide benefit) or if they can serve their purpose as included columns.
Read the whole thing and be willing to test different approaches.
It is about the time to create our first Clustered Online Resumable Index:CREATE CLUSTERED INDEX CI_SampleDataTable ON dbo.SampleDataTable (c1) WITH ( ONLINE = ON, RESUMABLE = ON ) ;
But all we shall get is an error message:Msg 155, Level 15, State 1, Line 25 'RESUMABLE' is not a recognized CREATE CLUSTERED INDEX option.
I was shocked and I was disappointed, but I have understood that it was my own mind’s fault. Nobody, I repeat – NOBODY has told me that it would work for the CLUSTERED Indexes, but when I see an announcement that the Indexes are supported, I was totally believing that the traditional (not XML, no CLR, no LOB’s) Rowstore Indexes would be totally supported. Oh yes, I know that it is crazy difficult. I know that this is a pretty forward-facing feature, but come on – my mind played trick on me, telling me the story that does not exist, for now, at least.
After realising my mind’s mistake I took a deeper breath and decided to try out the Resumable Nonclustered Index Creation with the following command:CREATE NONCLUSTERED INDEX NCI_SampleDataTable ON dbo.SampleDataTable (c1) WITH ( ONLINE = ON, RESUMABLE = ON );
Hopefully we get a bit more support as SQL Server vNext is developed and eventually released. In the meantime, Niko hits some limitations but his timings for the feature look good.
Bryan Rebok and Brent Ozar recently opened my eyes to something I didn’t know. When SQL Server recommends missing indexes to you it puts the columns in order in which they are found in the table. That’s it. I always thought there was more logic into it. But there isn’t. Upon reading this I had a terrible realization that I’ve made a lot of awful indexes in my time. I owe the world an apology. I hope this post can serve as that apology.
I’ve written a function that accepts the equality column list from dm_db_missing_index_details as a parameter and spits those columns back out in order by their cardinality. This won’t necessarily be the proper order for the columns in every index, but it is far more likely to be correct than the initial result from the DMV.
I’m amazed that the missing index DMV generates column names in such a simplistic manner.
I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code:CREATE NONCLUSTERED INDEX [dbo.NCI_Time] ON [dbo].[Audit] ([UserId]) INCLUDE ([DefID],[ShopID])
Msg 10637, Level 16, State 3, Line 7
Cannot perform this operation on ‘object’ with ID 1093578934 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.
Fortunately, the error message is clear and helpful, two terms which rarely go in conjunction with “error message.”
What you’ll notice is that several of the queries are filtering on the FirstName column. There’s no good index there. If you look at the execution plans for those queries you’ll also note the Missing Index suggestion. That suggestion is a necessary part of the automatic indexing. Yeah, missing indexes. I know. They’re not always accurate. It’s just a suggestion. Blah, blah, blah. I hear you.
The magic is not supplied by missing indexes. The magic is supplied by lots of data. Microsoft can take advantage of three things. Yes, missing index suggestions is first. Then, they can use the query metrics gathered in Query Store to see the behavior of your queries over time. Finally, they can use machine learning algorithms to determine if indexes will be helpful and measure how helpful they’ve been if one gets added. It’s great stuff. Go and read on it.
Click through for more notes, as well as a Powershell script you can use to replicate his findings.
When tuning queries that need to sort large amounts of data, sometimes it makes sense to stick the ordering elements as the leading key column(s) in your index. This allows SQL Server to easily sort your data by that column, and then access other key and included columns to satisfy other parts of the query, whether they’re joins, predicates, or selected columns.
That doesn’t mean that indexes with the sorting element first is always the best idea. Depending on how your query looks, you can sometimes put sorting elements at the end of an index definition and still have your sort be supported.
Read on for an example.
Global secondary index in DynamoDb – An index with a partition key and a sort key that can be different from the base table. A global secondary index is very helpful when you need to query your data without primary key.
The primary key of a global secondary index can be partition key or composite (partition key and sort key).
Global secondary indexes can be created at the same time that you create a table. You can also add a new global secondary index to an existing table, or delete an existing global secondary index
A global secondary index lets you query over the entire table, across all partitions.
The index partition key and sort key (if present) can be any base table attributes of type string, number, or binary.
With global secondary index queries or scans, you can only request the attributes that are projected into the index. DynamoDB will not fetch any attributes from the table.
There are no size restrictions for global secondary indexes.
Click through to learn more about these as well as local secondary indexes.
The CREATE INDEX statement is used to do exactly what its name says, it creates an index. But when you say CREATE UNIQUE INDEX, you are doing more than that; you are enforcing a business rule that involves uniqueness.
I have a simple rule on this. Wherever possible business rules like uniqueness, check values, etc. should be part of the design of the table, and not enforced in an external object like an index.
So, rather than a unique index, I’d rather see a unique constraint on the underlying table.
But that’s where real life steps in. I see two scenarios that lead me to occasionally use CREATE UNIQUE INDEX.
Here’s a third: creating constraints can cause blocking issues. If you already have a large table and Enterprise Edition, creating a unique index can be an online operation (unless you have a clustered columnstore index on the table), but a unique constraint is always a blocking activity.
It looks like internally Query Store is referred to as plan_persist. That makes sense, thinking about how the Query Store persists query plans to your database’s storage. Let’s take a look at those catalog views vs their clustered and nonclustered indexes. I’ve modified the query a bit at this point, to group together the key columns.
This lets you see how the Query Store authors expected us to use these tables. Which isn’t always how people use them…