Non-clustered index taking time on cetain days

ACDBA 416

I have a non-clustered index created every day on a partitioned table of 90million records..Usually, it takes 5 min to complete. but on some days it takes 3 to 4 hours. No other activity is running on the server at that time and nothing is blocking this creation. I can see a spike in CPU\Memory usage during this window.

Can you advise what to check? data volume is almost the same.

Ronen Ariely 15,096 Reputation points

2021-12-03T12:04:12.323+00:00

Hi,

Did you have any monitoring information till now? If so, then please provide it all so we will have something to based on.

Since this is "once in awhile" issue, I would start with monitoring impact of other processes (check waits and block, check IO and CPU and who use these resources - Perfmon can be useful tool here,

Start collecting information and provide as much as you have since at this time we are totally in the dark :-)
Tom Phillips 17,716 Reputation points

2021-12-03T13:27:00.28+00:00

You are dropping and recreating a partitioned non-clustered index on a 90 million row table every day? May I ask why?

I would not recommend that unless you are making changes to >25% of the table all at once. You would likely be better off leaving it in place. Even though the changes will be slower.
ACDBA 416 Reputation points

2021-12-03T14:47:26.517+00:00

its required for dev team while partitioning data between archive and live tables.
Tom Phillips 17,716 Reputation points

2021-12-03T15:40:20.863+00:00

Without knowing more about your specific situation it is hard to guess. But it should not be required to drop and recreate indexes during swap out/in of partitions.

It would be best to stop that practice rather than try to "fix" it.
Erland Sommarskog 101.9K Reputation points MVP

2021-12-03T23:00:14.973+00:00

Without knowing more about your specific situation it is hard to guess. But it should not be required to drop and recreate indexes during swap out/in of partitions.

If the index is not aligned with the partition key, you need to drop it.

Then again, it does not really sound like a plain partition switch, but like data is being physically moved. In such case, I can understand that it make sense to disable the index during such an operation to rebuild it later. Then again, if this is a daily operation, it cannot be that much data that is being moved.

Hm, there is something fishy here...
CathyJi-MSFT 21,096 Reputation points Microsoft Vendor

2021-12-07T07:07:31.293+00:00

Hi @ACDBA ,

Please use the WAIT_INFO Extended Event to capture the create index progress. Then we can find which resource that creating index session wait for.
ACDBA 416 Reputation points

2021-12-07T09:14:21.613+00:00

I put a perfmon counter and can confirm that there is no memory or CPU resource shortage shown
ACDBA 416 Reputation points

2021-12-07T09:14:40.43+00:00

Let me run it today.
ACDBA 416 Reputation points

2021-12-07T09:16:03.597+00:00

Can updating stats prior index creation can help?
Erland Sommarskog 101.9K Reputation points MVP

2021-12-07T22:19:20.27+00:00

Can updating stats prior index creation can help?

I would not think so. The table has to be scanned from start to end. Possibly it could matter for a filtered index, if there are other indexes on the table that could be scanned to create this index.

But I think the real answer to the problem is to review the entire process. But make take the daily index creation out of the picture entirely.

1 answer

Tom Phillips 17,716 Reputation points

2021-12-03T13:31:37.7+00:00

Are you running on a virtual machine? My first guess would be yes. I would suggest you look at the host usage, not the VM instance usage. It is likely something else on the host is interfering with the SQL VM.
Please sign in to rate this answer.
ACDBA 416 Reputation points

2021-12-03T14:07:21.82+00:00

I did check VM cluster usages and its all good.
Sign in to comment