Partition switching taking lots of time

Question

I have a partitioned table. All the partitions of this table are mapped to a single filegroup.

When I am trying to switch partition, it is taking lots of time more than 5 mins for a single partition.

I can see that there is a file in the filegroup and its size is very high that is 234GB.

Can partition switching take more time due to file size?

or is there any other issue?

I need help in finding out what is the problem.

Thanks,

Answer 1

So you are running ALTER TABLE SWITCH? Check for blocking. The operation requires an Sch-M lock which means that it is blocked any query that is running, including queries using NOLOCK.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Can partition switching take more time due to file size?

No, SWITCH is a meta-data only operation.  The likely cause is the blocking Erland mentioned.  However, SPLIT/MERGE may require data movement depending on the boundaries created/removed.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com