Index creation on partitioned table

Question

Index creation on partitioned table

Alexp012

Old Hand

Points: 369
More actions
June 29, 2023 at 10:43 am

#4222348

Hi
I have a large partitioned table and in order to carry out some maintenance on the leading partition I needed to switch it into another table. As part of te maintenance I needed to drop and recreate the indexes
In test (a fairly recent restore of production) things went smoothly, to create the indexes it started with a single table scan (around 100 million rows). In production though, it is doing a full table scan for each of the partitions resulting in 80 billion reads, even though only one of them has any data in it. This is taking a lot of time.
Any idea why one only does a single scan and the other multiple ones. I have done a stats update on the Prod table but not with Fullscan
Prod
Dev

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

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

Michael L John One Orange Chip Points: 27060 More actions · Answer 1

What is the point of dropping an re-creating the indexes?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

drew.allen SSC Guru Points: 76997 More actions · Answer 2

@michael-2, My understanding is that you cannot switch partitions if there are indexes that are not aligned with the partition.

@Alexp, Have you tried DISABLE/REBUILD instead of DROP/CREATE?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 3

commenting on this to follow it, dealing with a similar problem I want to understand better.

I didn't think you could even have an index on a partitioned table unless it was aligned.

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

drew.allen wrote:

@Michael, My understanding is that you cannot switch partitions if there are indexes that are not aligned with the partition.
@Alexp, Have you tried DISABLE/REBUILD instead of DROP/CREATE?
Drew

Thanks! Forgot that.

Are we switching partitions to perform index maintenance, or something else?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Alexp012 Old Hand Points: 369 More actions · Answer 5

You indexing needs to be the same if you switch to new tables. I had to switch partitions because there was a columnstore index and it had been neglected so there wasn't a data free leading partition. You can add a partition and a table with a clustered columnstore index if the leading parition has data in it.

I found this where they had the same issue

https://dba.stackexchange.com/questions/221491/rebuilding-indexes-for-a-partitioned-table-having-300-partitions

Changing the estimation mode did remove the filter, which was a quicker plan, but it still iterated through it as many times as it had parititons

Alexp012 Old Hand Points: 369 More actions · Answer 6

My method came from this Dan Guzman answer. I found the Drop Exisiting hint didn't work so I manually dropped and re-created.

sql server - Splitting Existing Partitions in SQL 2014 - Database Administrators Stack Exchange