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…
Looking at that data page, we can see that SQL has added a UNIQUIFIER column. Now this is standard SQL behaviour, SQL does this to all non-unique clustered indexes whether they are on a partitioned table or not.
But also look at the CreatedDate column. It’s after the ID column on the page. If this was a non-partitioned table, we would see that after ColA & ColB (the order the columns are on the table). This has happened because SQL has implicitly added the partitioning key into the index definition, which has changed the physical order of the data on the page.
Read the whole thing.
he benefits of having an index are well known, you can get the same results by reading a smaller amount of data so the improvement in performance can be from several minutes to seconds or even less.
That sounds awesome and it certainly is and there are people out there making a living of it, so it’s a huge deal for sure.
But it’s not always like that, and things can go wrong very easily and make all these shiny indexes just a pile of useless burden.
Let me show you some examples, where we can see our indexes in use, but also how they can be ignored by the query processor and become totally useless. I’m going to use the Microsoft sample database [WideWorldImporters] so you can follow along if you want.
Read on to learn more.