Sql-server – nvarchar(max) in Included Columns of NC index

index-maintenancesql serversql-server-2012

Would adding nvarchar(max) to the included columns of an NC index that has one nchar(16) column key column cause rebuild issues with 20+ million rows?

By "rebuild issues," I mean taking a very long time to rebuild the index. I thought that the "included" columns were treated differently from key columns and would not have as big of an impact on the cluster.

Best Answer

Yes, rebuilding that index will probably be a lot slower.

Baseline it will be an entire extra copy of this data on your disks. If the column is updated frequently, then it could cause the index to fragment more quickly. And the more data is in the index, the longer it will take to drop and recreate ("rebuild").

It's generally not recommended to add large data types as included columns in a nonclustered index. It causes fewer rows to fit on a page (or pointers to off row storage), which causes more pages to have to be read whenever you query this index. And as you can imagine it will require more RAM to keep these index pages in memory.

You would want to do performance testing to make sure that the benefits of this included column (presumably to avoid a key lookup?) outweigh the costs mentioned above.

Of course, the level of impact will depend greatly on how much data is actually stored in this column in practice.

You're right that included columns are treated differently, in that they don't affect the order that index data is stored. But they are still included at the leaf level of the index, so they will definitely cause your rebuilds to take longer. Included columns will generally cause less overhead than key columns when it comes to updates, since they won't cause the rows to be shuffled around if the order changes. But rebuilds are really just dropping and recreating the whole index.

Related Solutions

Sql-server – the best index implementation for a really large databse

Can i replace the non-clustered indices with one covering index ?

No. Suppose you sometimes find people by LastName and sometimes by FirstName. An index on (LastName, FirstName) won't help you find people by FirstName.

If not, is there any way to get rid of RID Lookup rather than a covering index ?

Not in a way that's particularly useful.

Does each query require a different covering index depending on the columns in the select list and the search conditions ?

Pretty much. Your job is to come up with a compromise.

I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?

A PK doesn't necessarily mean a clustered index. And having a CIX just means your RID Lookups will become Key Lookups, which are potentially worse. But without CIXs you have heaps which can fragment when you change or delete data. So CIXs are fine, but won't improve performance of your Lookups.

Sql-server – Help optimising delete statement

I've run into this kind of performance issue before, and breaking the delete up into discrete steps of a fixed size is what ended up doing the trick. It allows SQL Server to commit changes more frequently, which is generally easier on the transaction log.

SELECT 'Begin Delete'; --gives @@ROWCOUNT a value
WHILE @@ROWCOUNT <> 0
    delete top (100000) from [dbo].[tbl1]
    where exists (
        select *
        from [dbo].[tbl2] t 
        where [dbo].[tbl1].[col1] = t.[col1]
        and [dbo].[tbl1].[col2] = t.[col2]
        and [dbo].[tbl1].[col3] = t.[col3]  
    );

The optimal batch size just depends on your server and what it can handle. I've found 100,000 to be a safe size for me, and we've not got anything particularly powerful, so you may be able to get away with more.

Best Answer

Related Solutions

Sql-server – the best index implementation for a really large databse

Sql-server – Help optimising delete statement

Related Question