A Fine Slice Of SQL Server
So, when SHOULD you explictly define the clustering key columns in a nonclustered index? When they ARE needed by the query.
This sounds rather simple but if the column is USED by the query then the index MUST have the column explicitly defined. Yes, I realize that SQL Server will add it… so it’s not necessary NOW but what if things change? (this is the main point!)
One of the more common cases I could think of is multi-part clustered indexes, like on a junction table.
Comments closedNiko Neugebauer notes a big improvement in vNext:
For the Columnstore Indexes, the only online operation for the indexes that was available so far, was the Row Group Merging and Removal with ALTER INDEX REORGANIZE (as well as the Tuple Mover operations). With appearance of HTAP scenarios (Hybrid Transactional Analytical Processing aka Operational Analytics) in SQL Server 2016, there was a huge need for the online index maintenance, making sure that the operational part of the HTAP runs smoothly. For any online business, taking their application down for an hour means loosing real money and even worse – loosing credibility from their customers. To my knowledge, Microsoft was very much aware and was working on improving this missing part.
For the SQL Server vNext version (after SQL Server 2016) in CTP 1.2, yesterday, we have finally received the first Online Rebuild operation for the Columnstore Indexes – in this case for the Nonclustered Columnstore Indexes, and this is a huge news for anyone using the HTAP scenarios.
Naturally this feature is Enterprise Edition Only, and like ever before – if you are running a critical workload, you need to step up and use the Enterprise Edition.
Online clustered columnstore reorganization in 2016 was a life-saver, and I’m looking forward to online clustered columnstore rebuilding at some point in the future.
Comments closedIt Is Known
That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn’t have data for WHERE Bit = 0. That index would only be suitable for one variation of the query, so caching a plan that uses an index which can’t be reused for every variation isn’t feasible.
Read on for a couple examples, and check the comments on this as well.
Comments closedRegardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.
The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.
Click through for the code. I enjoy asking this as an interview question. It’s a non-trivial problem with a non-trivial solution and isn’t a trick question.
Comments closedKendra Little looks at index creation and modification dates:
SQL Server doesn’t really track index create or modification date by default
I say “really”, because SQL Server’s default trace captures things like index create and alter commands. However, the default trace rolls over pretty quickly on most active servers, and it’s rare that you’re looking up the creation date for an index you created five minutes ago.
I think it’s fine that SQL Server doesn’t permanently store the creation date and modification date for most indexes, because not everyone wants this information — so why not make the default as lightweight as possible?
That said, Kendra has several methods for answering the question of when a particular index was created.
Comments closedKendra Little offers advice on index tuning within stored procedures:
The best way is to run the stored procedure yourself to generate and save an “actual” execution plan, which contains the estimates SQL Server used when it generated the plan as well as actual rowcounts, actual memory granted, etc. It will also contain a green tooltip with a “missing index request” if SQL Server thinks an index would help.
This is an introductory-level post which contains good advice.
Comments closedRaul Gonzalez takes a look at non-clustered indexes on memory-optimized tables:
Wow, what happened there? This is something new I wasn’t expecting.
The first query matches one of our expected query plans, “Index Seek + Lookup” but just an “Index seek” doesn’t make sense, or it does?
Read on for the answer.
Comments closedDavid Klee shows the risk of creating custom indexes on third-party software:
The error file tells you specifically which indexes it does not like. The error file is found at:
C:\Users\(youraccount)\AppData\Local\Temp\vcsUpgrade\vcdb_req.err
You’ll find the error message towards the bottom of the document. My specific item was:
1 [42000](50000) [Microsoft][SQL Server Native Client 11.0][SQL Server]ERROR ! Extra indexes: VPX_EVENT.HFX_VPX_EVENT_Cover01; VPX_STAT_COUNTER.IX_VPX_STAT_COUNTER_STAT; VPX_TASK.HFX_VPX_TASK_Cover01;
For well-maintained third-party vendor software which doesn’t require you to add indexes to support the product at any scale beyond what a developer needs for basic testing, this isn’t an issue. And if you ever find that piece of software, write the company a note of congratulations for being the first…
Comments closedKenneth Fisher looks to see when his indexes were created (or at least updated):
SQL Server stores a create date and a change date for each object in the sys.objects system view.
Unfortunately while tables, views and even constraints are objects, indexes are not. Or at least they aren’t stored in the sys.objects system view. And the sys.indexes system view doesn’t have any dates associated with it. So how do we get the create/update date on an index? Well, short answer is you don’t. Long answer is that in some cases you can get some information.
These aren’t ideal answers, but they can be better than nothing.
Comments closedKendra Little explains the two types of filtered indexes:
These two filtered indexes are very different – and the SQL Server optimizer can use them very differently!
While classic filtered nonclustered rowstore indexes must reliably “cover” parts of the query to be used to the optimizer, filtered nonclustered columnstore indexes may be combined with other indexes to produce a plan returning a larger range of data.
This sounds a little weird. I’ll show you what I mean using the WideWorldImporters database.
Kendra exposes an interesting difference in the two types of index and a case where filtered indexes simply fail (though that’s not a situation you want to be in anyhow!).
Comments closed