Partitioning Tips

Sales data in my production database has increased significantly. I am planning to archive this data apart from the production database, so this can still be used for reporting purposes. I am conscious about performance issues while inserting new data to these archive tables and also for report response time. During the planning phase I found out about the new partitioning feature in SQL Server 2005 and later editions of SQL Server. It is a vast topic with several associated operations and configurations involved. In this tip I will share what I learned about partitioning, show an example to implement horizontal table partitioning along with a comprehensive list of related benefits and considerations.

With the increasing use of SQL Server to handle all aspects of the organization as well as the increased use of storing more and more data in your databases there comes a time when tables get so large it is very difficult to perform maintenance tasks or the time to perform these maintenance tasks is just not available. In the past, one way of getting around this issue was to partition very large tables into smaller tables and then use views to handle the data manipulation. With SQL Server 2005 a new feature has been added that handles this data partitioning for you automatically, so the ability to create and manipulate data in partitioned tables is much simpler.

I am using partitioning in my database. Is there an easy way for me to identify partitions that may have too much data so they can be split? Check out the examples and scripts in this tip to find out.

SQL Server Books Online (BOL) suggests that in order to clean up old partitions from a partition table, you should either issue a DELETE or SWITCH command. BOL also suggests that while SWITCHING partitions, you should set up a NON PARTITIONED TABLE in the same filegroup where the original partition is located. The BOL solution works if you need to manage a couple of partitions since creating a couple of tables in each filegroup is not that time consuming. However, this problem becomes very challenging when you need to manage many historical partitions located on many different filegroups.

Partitioning, introduced with SQL 2005 Enterprise Edition, is a godsend for working with very large tables. It provides the means to effectively manage and scale your data at a time when tables are growing exponentially, but maintenance windows are either shrinking or non-existent as with many 24/7 online environments. In this tip I will cover some data partitioning myths and truths you should know about.

One of the challenges of working with large datasets or datasets that become stale is the need to move large chunks of data in and out of your tables. This can be done with large INSERT and DELETE statements or by using views, but with SQL Server 2005 data partitioning makes this task much easier to manage than in previous versions of SQL Server. In this tip we will take a look at how to use the SWITCH operator to move data in and out of partitions.

Sales data in my production database has increased significantly. I am planning to archive this data apart from the production database, so this can still be used for reporting purposes. I am conscious about performance issues while inserting new data to these archive tables and also for report response time. During the planning phase I found out about the new partitioning feature in SQL Server 2005 and later editions of SQL Server. It is a vast topic with several associated operations and configurations involved. In this tip I will share what I learned about partitioning, show an example to implement horizontal table partitioning along with a comprehensive list of related benefits and considerations.

Partitioning, introduced with SQL 2005 Enterprise Edition, is a godsend for working with very large tables. It provides the means to effectively manage and scale your data at a time when tables are growing exponentially, but maintenance windows are either shrinking or non-existent as with many 24/7 online environments. In this tip I will cover some data partitioning myths and truths you should know about.

With the increasing use of SQL Server to handle all aspects of the organization as well as the increased use of storing more and more data in your databases there comes a time when tables get so large it is very difficult to perform maintenance tasks or the time to perform these maintenance tasks is just not available. In the past, one way of getting around this issue was to partition very large tables into smaller tables and then use views to handle the data manipulation. With SQL Server 2005 a new feature has been added that handles this data partitioning for you automatically, so the ability to create and manipulate data in partitioned tables is much simpler.

SQL Server Books Online (BOL) suggests that in order to clean up old partitions from a partition table, you should either issue a DELETE or SWITCH command. BOL also suggests that while SWITCHING partitions, you should set up a NON PARTITIONED TABLE in the same filegroup where the original partition is located. The BOL solution works if you need to manage a couple of partitions since creating a couple of tables in each filegroup is not that time consuming. However, this problem becomes very challenging when you need to manage many historical partitions located on many different filegroups.

One of the challenges of working with large datasets or datasets that become stale is the need to move large chunks of data in and out of your tables. This can be done with large INSERT and DELETE statements or by using views, but with SQL Server 2005 data partitioning makes this task much easier to manage than in previous versions of SQL Server. In this tip we will take a look at how to use the SWITCH operator to move data in and out of partitions.

I am using partitioning in my database. Is there an easy way for me to identify partitions that may have too much data so they can be split? Check out the examples and scripts in this tip to find out.

I am using partitioning in my database. Is there an easy way for me to identify partitions that may have too much data so they can be split? Check out the examples and scripts in this tip to find out.

Partitioning, introduced with SQL 2005 Enterprise Edition, is a godsend for working with very large tables. It provides the means to effectively manage and scale your data at a time when tables are growing exponentially, but maintenance windows are either shrinking or non-existent as with many 24/7 online environments. In this tip I will cover some data partitioning myths and truths you should know about.

Sales data in my production database has increased significantly. I am planning to archive this data apart from the production database, so this can still be used for reporting purposes. I am conscious about performance issues while inserting new data to these archive tables and also for report response time. During the planning phase I found out about the new partitioning feature in SQL Server 2005 and later editions of SQL Server. It is a vast topic with several associated operations and configurations involved. In this tip I will share what I learned about partitioning, show an example to implement horizontal table partitioning along with a comprehensive list of related benefits and considerations.

SQL Server Books Online (BOL) suggests that in order to clean up old partitions from a partition table, you should either issue a DELETE or SWITCH command. BOL also suggests that while SWITCHING partitions, you should set up a NON PARTITIONED TABLE in the same filegroup where the original partition is located. The BOL solution works if you need to manage a couple of partitions since creating a couple of tables in each filegroup is not that time consuming. However, this problem becomes very challenging when you need to manage many historical partitions located on many different filegroups.

One of the challenges of working with large datasets or datasets that become stale is the need to move large chunks of data in and out of your tables. This can be done with large INSERT and DELETE statements or by using views, but with SQL Server 2005 data partitioning makes this task much easier to manage than in previous versions of SQL Server. In this tip we will take a look at how to use the SWITCH operator to move data in and out of partitions.

With the increasing use of SQL Server to handle all aspects of the organization as well as the increased use of storing more and more data in your databases there comes a time when tables get so large it is very difficult to perform maintenance tasks or the time to perform these maintenance tasks is just not available. In the past, one way of getting around this issue was to partition very large tables into smaller tables and then use views to handle the data manipulation. With SQL Server 2005 a new feature has been added that handles this data partitioning for you automatically, so the ability to create and manipulate data in partitioned tables is much simpler.