Sql-server – Clustered Index Maintenance vs. Nonclustered Index Maintenance

clustered-indexidentitysql serversql-server-2017

There is a plethora of information available detailing why an IDENTITY field should be use as both the primary key and clustered index of a table for most situations; yet I'm having difficulty deciding if my particular situation is an exception.

I have a table with around 250 columns—I don't want to start a normalization debate—and around 100M rows. The table is page compressed. On peak days, around 1M rows are entered into the table sequentially with 0 contention from other connections. The combination of a char(2) NOT NULL field and an int NOT NULL field is guaranteed to be unique and never changing. There are only about 10 unique values for the char(2) field.

My argument for an IDENTITY column being used as the clustered index is solely performance based. Since inserts will happen sequentially, I shouldn't haven't to worry about latch contention; thus the inserts should be faster since there will be no need to search the index. On the other hand, a composite key of 6 bytes is rather small; and I'll end up making it the primary key anyway (with the char(2) field first). Furthermore, there will be a few other tables that are bulk loaded based on the new rows in this table (likely via a filtered index). If I use an IDENTITY column, I'll likely use that column as the clustered index and primary key of these other tables (without the IDENTITY property) and not bring over the "natural" composite key.

Does making a nonclustered index on the composite key nullify the expected speed boost of using an IDENTITY column as the clustered index since this index will have to be searched when the inserts are done?

Edit

Based on the comments, I changed the title and am asking an amended question whose answer can be used to answer the original question above. How does maintenance of a unique clustered index compare to maintenance of a unique nonclustered index in regards to inserts? Does a non-sequential clustered index suffer similarly to a non-sequential nonclustered index?

Best Answer

Embarrassingly, I never knew of columnstore indexes. This table is primarily used for analytics; thus I used a clustered columnstore index with a primary key defined on the char(2) and int fields as per the suggestion from David Browne - Microsoft in the comments.

Since the question was about clustered index maintenance compared to nonclustered index maintenance, I'll "answer" that part to the best of my ability. From what I've read, it takes more resources when a clustered index has a page split than a page split on a smaller nonclustered index (with the same key). This is because the page contains far more data in the clustered index. Consequently, had I not used a columnstore clustered index, I would have used (and tested) an IDENTITY column for the clustered index. Having said that, the video sp_BlitzErik provided in the comments is very informative—and easily digestible for plebs like me—and highlights how there is a lot of outdated recommendations "out there" based on older implementations of SQL Server; thus concerns about external fragmentation can be exaggerated.

Related Solutions

Sql-server – Clustered vs Nonclustered Index

Since we are talking about the clustered index, just because you defined the CI key column as ID, you still have the DeletedDate data in the leaf data pages of the index. That's the nature of the clustered index: It is the table data.

Because you are typically having queries that look like:

select *
from YourTable
where DeletedDate is null;

You will likely benefit from a filtered index.

create nonclustered index IX_YourFilteredNci
on YourTable(<Key Columns Here>)
where DeletedDate is not null;
go

I didn't explicitly put the key columns here (and nonkey columns through the use of the INCLUDE clause) because you didn't publish the DDL of your table.

As in my comment above to your question, the choice of key columns (not just columns, but also the order of the columns) will largely depend on your workload and the typical queries that would be using this index.

If you are looking to cover your query(ies), then you would need to ensure that the index satisfies all of the data required of the query(ies). Not to mention, if you have other WHERE clauses (besides your NULL check on DeletedDate) or joins to consider, then the order of your key columns can be the deciding factor between a scan or a seek. And even though it is filtered, and depending on how much data you have in the index, the penalty could be considerable.

Sql-server – Identity column as clustered index bad idea

I usually use an identity column as clustered primary key. However in some (rare?) cases this is not ideal because of the LastPageInsertLatchContention. This happens if a table is heavely filled with data. Because of the identity key all this INSERT's wants to write the last page of the table (index). So this page can be locked and the performance may be better with another solution.

See

for details.

Best Answer

Related Solutions

Sql-server – Clustered vs Nonclustered Index

Sql-server – Identity column as clustered index bad idea

Related Question