I've been asked to test out performance of a database (a test system) after implementing horizontal partitioning. The database is on SQL Server 2008 r2. I'm inheriting this from someone else and found something I didn't expect in some of their code. In some cases, I've seen that adding
WHERE $partition.archiving_Partition_function(MyTable.IsArchived) = 1 to the Where clause improves query performance.
(The client wants to "archive" old data by partitioning it into an "archive" partition. They can't use dates, since some projects are considered old after different time intervals. So they want to manually flip a bit to archive it.)
So, I have a handful of tables on which I have modified the clustered index in order to partition the table. (This is my first go-around with partitioning, so bear with me if my terminology is lacking.) And I've been performance tuning several queries, often adding something like
Where MyTable.IsArchived = CONVERT(BIT,0) in order to restrict to a single partition. However, some queries perform better when I replace
MyTable.IsArchived = CONVERT(BIT,0) with
$partition.archiving_Partition_function(MyTable.IsArchived) = 1. This surprises me. My expectation is that the latter simply passes
MyTable.IsArchived to the partition function and compares the result to an integer constant. It isn't sargable and doesn't look like it would help determine what index is best. It looks like it simply adds some overhead by calling a function, whereas the former code does the same thing... Compare the bit value to some constant. Except that the former code follows the guidelines to help use the clustered index.
So, I guess my question is, "What's going on under the covers with
$partition.MyPartitionFunction? It looks to me like unnecessary overhead, but sometimes performs better than checking the partition column directly. And I can't seem to find any documentation that sheds light on this.