NC index creation duration question

Question

Post reply

NC index creation duration question

Coffee_&_SQL

SSC Veteran

Points: 230
More actions
December 6, 2024 at 3:12 pm

#4494208

Hey All,
My setup is as follows:
Table with Clustered Index on ID col. Size 13 GB. 200+ fields, many LOB cols.
Single non-clustered index with key CaseNumber and 2 include columns. Size 200 MB.
Server performance was good, normal, i.e. hardware doesn't explain the issue.
Scenario:
I attempted to add a new Non-clustered index to the table on key col CaseNumber with 9 include columns.
After 70 minutes I cancelled this query.
I dropped the existing Non-clustered index. Then the attempt to add new NC index completed in 1 minute.
Is there a reason that my first attempt was taking forever? I assume it has something to do with the fact that there was already an existing NC index on CaseNumber.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

You do realize that the addition of the new index requires space that might not have been available and that dropping the first NCI made a bunch of space available, correct? I don't know if the original attempt to add the new index allocated any space before you killed it and it rolled back.

Also, do you have "Instant File Initialization" enabled so that such growths are faster?

Last but not least, how big did the new index actually end up being? Hopefully, you didn't make the mistake of INCLUDEing any LOB columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

daytonbrown2 Say Hey Kid Points: 684 More actions · Answer 3

When you killed the first index did it take a long time to roll back or was it instant? Maybe you were actually just blocked on the schema change. Although 70 minutes is a long time to be blocked.

Coffee_&_SQL SSC Veteran Points: 230 More actions · Answer 4

Jeff Moden wrote:

You do realize that the addition of the new index requires space that might not have been available and that dropping the first NCI made a bunch of space available, correct? I don't know if the original attempt to add the new index allocated any space before you killed it and it rolled back.
Also, do you have "Instant File Initialization" enabled so that such growths are faster?
Last but not least, how big did the new index actually end up being? Hopefully, you didn't make the mistake of INCLUDEing any LOB columns.

Hey Jeff and daytonbrown2,

Thanks for replying. I do have instant file initialization on. New index ended up being about 245 MB in size.

There are clearly some things that I did not think to check when I ran into this issue. At this point, there's no way to know what exactly occurred. I'll see if I can repeat this scenario on my dev server at some point this week.

Thanks All!

ScottPletcher SSC Guru Points: 101115 More actions · Answer 5

To reduce space usage in that db, you can specify SORT_IN_TEMPDB = ON in the index create.

Also, to save time, pre-allocate additional log space, if needed. If you must get the log space back, you can shrink the log back to its original size later.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

EdVassie SSC Guru Points: 60476 More actions · Answer 6

Without knowing what happened, it does smell a bit like locking.

Before retirement I made fairly extensive use of columnstore indexes, but I also had snapshot isolation active. SI did a great deal in reducing lock contention, with no noticeable detriment to performance. The reduced r/w locking was a real benefit.

If you are using CSIs, clustered or non-clustered, looking at table volatility becomes critical, especially in an OLTP environment. I set up a process that automatically reviewed all CSIs once every 2 hours, and ran maintenance on any that exceeded a threshold. (This was on EE, not Std).

We had a CSI on almost every table and the index use stats showed such a reduction in row-format index use that it allowed us to safely drop shedloads of row-store indexes. The reduced row-store index updating was also good for performance as well as saving storage space.

The problems we found (on SQL2019) were:

* Row-format indexes used for foreign keys needed to be kept, although we could normally eliminate any included columns. The lookup for foreign keys seemed to be hardcoded to avoid a CSI, hopefully this changed in SQL2022 or 2025.

* Sometimes a given query would scan the CSI instead of doing seeks, and we needed to reinstate row-format indexes for these queries to perform well

* Some tables were just too volatile to safely use a CSI. We determined this by saying any table where the CSI needed maintenance 5 times or more in 24 hours was too volatile

In summary, for about 98% of all tables having a CSI was either neutral or beneficial to query performance, but the critters do need automated maintenance to safely use them.

This reply was modified 16 hours, 19 minutes ago by EdVassie.
This reply was modified 16 hours, 18 minutes ago by EdVassie.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara