Just to wrap up this week’s discussion on VLDBs we need to look at the new features of SQL Server 2008 as it improves the Data Partitioning features introduced in 2005. Let’s take a look.
First of all, there is now Parallelism in query execution for Partitioned tables and indexes. This translates to performance as multiple threads are used to process separate partitions in parallel. The SQL Server execution strategy attempts to allocate equal number of threads to each partition that needs to be accessed during query processing. This processing is automatic and is apparent when viewing the execution plan in SQL Server Management Studio via the new Parallelism operator.
Another new feature is partition elimination. This enables the query optimizer to only access partitions that are needed when satisfying a query and is done internally by the hidden computed column PartitionID which indicates which partition a particular row is contained in. Again better performance is the result.
In terms of tracing the partition elimination you can now view information when accessing the execution plan, either as text or XML format. SSMS and SQL Profiler will display the XML format graphically and the “Actual Partition Count” will tell you exactly how many partitions were needed to be accessed during the query. Obviously, the smaller the number, the better. Also under the Seek Predicates section of the plan, exact details of which partitions were accessed by the query are displayed (look for “Seek Keys”). In SQL Server 2005 there was little feedback on how the partitions were accessed. Now we can actually analyze queries against partitioned objects in great detail to make sure we are getting maximum performance.
The query optimizer continues to be fine-tuned for VLDBs and Microsoft rightly sees this as the future of the product. Competing against IBM and Oracle, Microsoft’s strategy seems to be to match the big guys then turn around and say “oh by the way, we’re cheaper and easier to use…”. We’ll see if it is successful but either way, we are in for an interesting ride…
Later,
Brian
Recent Posts
VLDB? Use Read-only Filegroups if you can…
