Data Partitioning in SQL Server was introduced in the 2005 release. This was a feature that had been in Oracle and IBM’s DB2 for a while, but Microsoft is positioning to compete in the VLDB market place. What constitutes a VLDB? Well, that’s a “moving window” – most people now talk about terabyte size as being large. Once Microsoft match the big guys for functionality, it can then turn around and say SQL Server has a cheaper TCO and is easier to use. That’s the theory anyway. Partitioning has been improved in SQL Server 2008 in terms of performance. There are a couple visible improvements too….
The great thing about data partitioning is that a large table can be divided up onto physical devices based on a single column value, for instance, a date. The system will then only use the partitions necessary to satisfy a query. Also, if you are looking for data across partitions the system will use multiple threads across partitions providing superior parallel processing to execute the query.
From a manageability point of view, partitioning provides improved archiving capabilities with instant switching of partitions between a table and its archive counterpart. I talked about this in a previous blog entry – “Sliding Doors or Sliding Windows?“.
The great new feature in SQL Server 2008 that relates to performance and tuning is in the Execution Plan display in SSMS. You are now shown exactly which partitions were used in a query by including the actual execution plan. There is a new section: “Seek Predicates” which will outline which partitions were accessed. This is invaluable information as you attempt to tune a partitioned query.
So while performance improvements have been made for data partitioning, the extra good news is that we can actually see “under the hood” and verify those improvements.
Later,
Brian
Recent Posts:
Change Data Capture in SQL Server 2008
