The Froyo DBA team deleted old data on weekends, and that process also had problems. Users were never really sure when data was finished loading and when it was safe to run reports. This meant that sometimes reports contained partial data for the most recent day, which caused problems. To help alleviate blocking, some reports were modified to have NOLOCK hints. Each night as data was loaded, reports repeatedly blocked inserts. Life used to be tough for the Froyo DBA team. As a safety precaution, they prefer to keep three additional months of data online, but do not want reports to access the older data.
95% of reports run are against the most recent two months of data, and the DBA team controls and can tune the queries run by each report. The Froyo DBA team needs to maintain only 13 months of data in the FroyoSales table. Reports are run against FroyoReports 24 x 7, although there is a two hour window each day where there is significantly lighter load. Contoso Corp has employees worldwide who query the data using SQL Server Reporting Services. Each day, 10 million rows of sales data are loaded into a table named FroyoSales. (Is this true in reality? More on this later.) An Textbook Example of Table PartitioningĬontoso Corporation’s Froyo Division has a 2TB database named FroyoReports. In other words, in theory you don’t need to change any code in the calling applications. This means a partitioned heap, clustered index, or non-clustered index can be referenced as a single structure although it’s stored in independent physical partitions. Each chunk, or partition, has the same columns– just a different range of rows. Table partitioning allows tables or indexes to be stored in multiple physical sections- a partitioned index is like one large index made up of multiple little indexes. (Cha-ching! $$$.) You can test it in developer edition, but if you want to use it in production, you gotta make sure it’s worth the licensing costs as well as your time. First of all, this is an Enterprise Edition feature. Let’s nerd out for a bit on what table partitioning does in SQL Server.
SQL Server Table Partitioning: The Basics How do you know if you should invest your time in table partitioning? It’s a complex feature and you can read for days to just understand how you might apply it. But will it improve performance for you? Table partitioning produces great benefits for some applications, but causes giant headaches for others. Great volumes have been written about table partitioning.
0 kommentar(er)
