Table/Index Partitioning in SQL 2008 – General Question | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Table/Index Partitioning in SQL 2008 – General QuestionThis is a general question without code samples or specifics on the structures being implemented. I’d appreciate any applicable feedback on the overall approach.
I’m working on implementing a partitioned table/index structure for a large denormalized table. I used the Kimberly Tripp article and 2008 Books Online as a guide. I defined the partitons based on the most commonly used date range selections (Today, Last 7, Last 30, Last 60), end expected to see better results with the partition approach particularly on the Last 30 and Last 60 periods, since the query had to search back through a significant date range but much less data logically (because of the partitions). There is a year and half worth of data in the table. The table, clustered indices, and nonclustered indices all utilize the partition structure.
My overall finding is that there doesn’t seem to be much improvement between our normal clustered index use on the date column and the partitioned table using the same date column. I’m wondering if the fact that the underlying disk structure is the same (single disk being used in both the primary filegroup appraoch and the partition scheme approach), I’m not going to get much improvement over the simpler clustered index approach.
Welcome to the forums.
What is the physical design of the database?
Also what kind of hardware you are using?
What kind of partitioning you have obtained, horizontal?
FYI, Horizontal partitioning can be a great benefit to data warehouses, as it greatly facilitates the management of very large databases. Of course, querying on partitions can also have performance benefits, especially when only a small percentage of the database is queried and partition elimination can occur.
As you may be aware that SQL Server 2008 behavior changes the way in which threads are allocated. It allocates all available threads to each partition relevant to the query in a round robin fashion. This results in fast and consistent query execution, no matter how many partitions are queried. Also the access to these files/pages within SQL engine makes it all the more important to allocate partitions to filegroups that are spread across many disk spindles, allowing the query on a given partition to be as efficient as possible.