Partitioned Tables in SQL Server 2008

re: Partitioned Tables in SQL Server 2008

Peso — Sun, 20 Jul 2008 23:06:45 GMT

How accurate is the $PARTIION function in this case, compared to examine the XML output?

re: Partitioned Tables in SQL Server 2008

craigfr — Tue, 22 Jul 2008 07:36:05 GMT

The $PARTITION function operates on a row by row basis. For instance, in the example in this post, if we write:

SELECT *, $PARTITION.PF(A) FROM T

for each row returned by the query we will also get the partition id to which the row belongs. The XML output will only indicate which partitions were scanned but will not tell us how the rows map to partitions.

Note that we can use the $PARTITION function to get the same results as the XML output (much less efficiently) by writing:

SELECT DISTINCT $PARTITION.PF(A) FROM T

Of course, this query does not return any data other than the partition ids.

Partitioned Indexes in SQL Server 2008

Craig Freedman's SQL Server Blog — Tue, 05 Aug 2008 21:36:52 GMT

In my last post , I looked at how SQL Server 2008 handles scans on partitioned tables. I explained that

re: Partitioned Tables in SQL Server 2008

sholliday — Mon, 18 Aug 2008 21:42:00 GMT

//While evaluating the filter repeatedly may not cost too much if we have only four partitions, it is certainly going to waste some cycles if we have many //

For arguments sake, let's say there is a table with 200 partitions. (Not a recommendation, just a number for posing a question).

So you're saying that if the same partitioning scheme(and functions) are used on a Sql Server 2005 db and then (same db setup) on a Sql Server 2008 database, the Sql Server 2008 database will perform better because it will "avoid evaluating the filter" so many times.

My question is primarily about dynamic partition elimination, where I am passing a @MyPartitionKey (int) variable into a stored procedure.

And if I'm discerning all that correctly, any idea how costly that really ends up being?

Maybe in the 5 million total rows, 1 million per partition range? ( I know each case is varying, but any hints would be appreciated).

Thanks.

Dynamic Partition Elimination Performance

Craig Freedman's SQL Server Blog — Sat, 23 Aug 2008 01:50:18 GMT

In this post on partitioned tables, I mentioned that SQL Server 2008 has a much more efficient implementation

re: Partitioned Tables in SQL Server 2008

craigfr — Sat, 23 Aug 2008 01:52:22 GMT

I decided to measure the overhead of dynamic partition elimination with a large number of partitions. Please see this post for the results:

http://blogs.msdn.com/craigfr/archive/2008/08/22/dynamic-partition-elimination-performance.aspx

re: Partitioned Tables in SQL Server 2008

wuloo — Mon, 05 Jan 2009 07:58:42 GMT

one question: I have index partitioning on a table, will the non-clustered index partitioning (one or more using the same partition scheme) bring more tradeoff for index seek or scan or sql optimizer take them the same? I noticed if using non-clustered index partitioning will create more partitions for each NC index.

re: Partitioned Tables in SQL Server 2008

craigfr — Tue, 06 Jan 2009 00:08:47 GMT

With regards to seek or scan performance, the question to ask is whether there is a predicate on the partition column that can be used to enable partition elimination. If the answer is yes, then partitioning should not impact performance. If the answer is no, the query plan may need to seek or scan every partition in which case partitioning may affect performance. The actual impact depends on the number and size of the partitions and total number of rows processed by the query.

A typical scenario for partitioned tables is to use ALTER TABLE to switch an old partition out and a new partition in. This scenario requires that all indexes (clustered and non-clustered) be partitioned using the same scheme. For some workloads, there may be a tradeoff between enabling this scenario and achieving optimal partition elimination performance.