Philippe Cand (5/1/2010)
We have a debate about partitioned tables vs the old partitioned view in SQL2008. I have a troublesome table with over 30M rows. I thought that horizontal partitioning with aligned indexes and aligned cubes partitions would significantly increase performance since most queries touch 1 partition and SQL2008 allocate many threads to these queries as opposed to SQL2005 which allocate only one thread. A Colleague of mine bought the SQL Server 2008 Bible and there is a call-out that says that table partitioning will HURT performance unless your table is a real monster table with billions of rows.
Hello. In my experience from using SQL Server 2005 Partitioning on decently-sized tables I can offer two insights:
1) 30M rows is generally NOT a large table. If you are having performance problems you first need to look into the data model (maybe it can be improved by breaking the table into two one-to-one tables, etc.) and index structure (and don't forget about index rebuilds / reorgs).
2) The book is correct as far as I have seen. We have implemented Partitioning and I have tested a 125 Million row table that we partitioned and made a copy that was not partitioned and the performance was about the same between the two. Although to be fair, I believe I only tested queries that hit the PK.
I believe Microsoft has also stated that Partitioning is really only meant to increase performance of large data loads, such as in a Data Warehouse and was not meant to increase query performance (even though intuition says that it would). I am going to forward this thread to someone who might be able to give more details.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR