Index creation on partitioned table
-
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
-
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/ -
@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/REBUILDinstead ofDROP/CREATE?Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
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.
- drew.allen wrote:
@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/REBUILDinstead ofDROP/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/ -
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
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
-
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply