Microsoft – SQLServer – Partition Management (and a few delays)
Partitioning a database or more specifically individual objects (ie table) is one of those things that you can do to gain better performance.
Benefits of Partitioning
As the databases in an organization scale up and contain large volumes of data, it is critical that high availability be maintained while accommodating the need for a small database maintenance window. This requirement makes partitioning a natural fit for very large databases. Partitioning addresses key issues in supporting very large tables by letting you decompose them into smaller partitions thereby facilitating better management of influencing factors such as data loading, aging, and archival. Microsoft SQL Server supports data partitioning through partitioned views in SQL Server 7.0/2000 and added support for partitioned tables in SQL Server 2005.
One of the more common partitioning strategy is “Sliding Window”. The crust of this strategy is to create silos for new data and gracefully “age” out old data.
One of the little problems we found is that the “split range” command sometimes runs longer than we anticipated:
- It blocks other sessions
- It uses a lot of IO
Actual SQL Server Errors may include:
- Could not allocate space for object ‘<temporary system object: ####>’ in database tempdb because the primary file group is empty. Create disk space ….
Msg 1105, Level 17, State 2
- The transaction log for database ‘tempdb’ is full
Msg 9002, Level 17, State 4
With a bit of thought, all the shortcomings are to be a bit expected.
The “Split Range” statement basically shreds the existing data into a new partition.
Depending on how far behind one is, there are could possible be a few iterations to go through.
What to do:
- Monitor like hell
- Keep good housekeeping as to current operations
- Set Lock Timeout (Set Lock_Timeout <Number of milliseconds>)
- Plan & implement your Partition Management process as soon as you can
And, have “Kenny Chesney” playing in the background:
To be honest any “Kenny Chesney” will do, but for now, I am listening to:
Kenny Chesney – The Woman with you
- Split Partition and expect locks
- Altering a Partitioning Function
- Strategies for Partitioning Data Warehouse
- SQL Server 2005 partition tables getting timeouts when splitting range
- Set Lock Timeout