Redheadatabase

About me: My name is Maria Zakourdaev. I have more than 10 years experience with SQL Server (starting from SQL Server 6.5). The last five years have been spent mostly on benchmarking different SQL Server features and flows, like data import, indexes impact on DML flows, star transformations in RDBMS, Hierarchic queries and custom OLAP-like aggregations. I was a speaker in the Microsoft Teched (Israel) on the SQL Server track. I am also an active member of the Israel SQL Server Group.

Sunday, October 18, 2009

Merging partitions in SQL 2005

Today I have played with partition merging. The target of this was to see how long it can take, which locks are being held etc.

I see that even if partions are empty, the minimum time that it takes is 30 sec ( 2x2.66 CPU 7Gb RAM ). Most of the merge time the folowing system tables are locked exclusive which means that no new query compilations, no schema changes are not allowed: sysrowsetrefs,sysrowsets,sysallocunits,syssingleobjrefs, sysobjvalues,syshobtcolumns,sysrowsetcolumns,sysserefs,syshobts,sysschobjs,sysclsobjs. If there are any schema modification processes running in the database or any data modification processes merge of course waits till those are finished and it can aquire the required locks.
If there are other tables that reside on the same partition function, Sch-M locks are placed on them.
Regardless of partition sizes, the merged partition will reside on the filegroup that did not hold the merged boundary. This means that is you need to merge many partitions, start from the most recent partitions:
For instance you have 4 partitions that you need to merge
Feb 500000 rows
March 500000 rows
April 500000 rows
May 500000 rows

Start with merging april data to may, then march data to april+may, then feb data to the march+ may. This way you move each time only 500000 rows.

Labels: , , , , , ,

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home