Jump to: navigation, search


The following SQL statement is an easy way to monitor how many records are stored in each partition for a given partitioned table (the interactions table in this example):

p.partition_number, fg.name, p.rows
sys.partitions p
INNER JOIN sys.allocation_units au
ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg
ON fg.data_space_id = au.data_space_id
p.object_id = OBJECT_ID('interactions')

This produces results similar to those shown in the following table.

Partition_number Name Rows
2 P1 1
3 P2 1
4 P3 1

The table shows that each partition contains a single record. If you insert a new record and execute the above statement again, it will show which partition the new record has been placed in, verifying your partition function and scheme.

To compare performance of the partitioned database with an unpartitioned database, you will need to artificially create a certain distribution of interactions between partitions (different queues) and see how fast the same SELECTs are being executed. When interactions change queues, the records are physically relocated into different partitions (according to the partition scheme).
This page was last modified on October 15, 2018, at 10:26.


Comment on this article:

blog comments powered by Disqus