We tried to use table partitioning to get a large database (at least for us) spilt across high/low priority storage, but ran into this bug:http://connect.microsoft.com/SQLSer...erformance
The bug results in certain queries performing a complete index sort, rather than just using the known order of an index to determine the result.
This is very unfortunate if your table contains billions of rows.
Examples of simple queries affected by the bug:
A. "SELECT MAX(ColumnN) FROM PartitionedTable" (with a non-clustered aligned index on ColumnN)
B. "SELECT TOP 10 ColumnX, ColumnY, ColumnZ FROM PartitionedTable ORDER BY ColumnX, ColumnY, ColumnZ" (with a clustered aligned index on ColumnX, ColumnY, ColumnZ)
In our case the above queries resulted in a difference in execution duration of milliseconds vs. hours, for the exact same queries, on non-partitioned and partitioned tables respectively.
As far as I know Microsoft has no plans to fix this bug in current releases of SQL Server, and has no plans to fix it in the next major either.
So for an enterprise solution that allows for us to:
- prioritize data in the same table on physical storage (because all data has to be available online, but only some is frequently queried)
- keep the physical prioritization transparent to the layers above the database (because you already have existing applications using the database, and/or is using ORMs in the data access layer)
- keep your indexes aligned to utilize PARTITION SWITCH (because your maintenance window for moving less relevant data to low priority storage is limited)
- use TOP N/ORDER BY style queries on a large amount of rows (because who doesn't
... we were not able to use table partitioning.
The bug made me wonder if anyone was using table partitioning at all, and how do you get around this bug?
So please share if you are