SQL SERVER – Total Number of Partitions Created by Partition Function

Yesterday, I was helping one of my clients with a comprehensive Comprehensive Database Performance Health Check, we found a very strange partitioning system slowing down their overall performance. It took us a while to figure out that they are facing the performance issues with the partitioning function which they were using. The next task for us was to find out how many partitions so far has been created by the same partition function. Let us learn about that today.

It is very easy to figure out how many partitions are created by any partition function with the help of sys.partition_functions.

SELECT name,fanout,modify_date		
FROM sys.partition_functions

When you run the above function, you will get the result as displayed in the following query.

The first column is self-explanatory, it displays the name of the function. The second column fanout lists the Number of partitions created by the function. The third column modify_date lists when was the last partition created by the same function.

Once we rectified the partition function by my client, we were able to restore the performance back to the original state.

Here are a few blog posts which I had written earlier on the topic of partition functions, which you may find relevant.

It is very common to hear in industry that partitioning helps with SQL Server performance, however, my experience says while partitioning are great for data management, I have not seemed significant performance improvement. I would like to know your opinion, please leave your comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DMV, SQL Function, SQL Scripts, SQL Server, Table Partitioning
Previous Post
SQL SERVER – Statistics Modification Counter – sys.dm_db_stats_properties
Next Post
SQL SERVER – QDS_LOADDB Wait and Asynchronous Load of Query Store

Related Posts

1 Comment. Leave new

  • My SQL versions/editions are all too low to use partitioning, but I imagine that if you have a HUGE table (thinking billions of rows), and poor indexes, you could get a performance benefit by selecting with the WHERE clause causing it to only look at a single partition.
    Failing that, if you had each partition on its own disk (I think you can do that?),and disk I/O was your bottleneck, you would get a decent performance boost from that, no?

    Mind you, the first example is likely easier to fix and maintain by putting a proper index on the table, and the second is not really a good solution in a SAN environment as you may have your partitions on the same physical disk even if it is on different logical disks.
    That being said, if you KNOW that some of your data is frequently accessed and a lot is not (like if most people look at the data from the past year), you could partition it and put the frequently used data on SSD and the less frequently used data on 7200 RPM.


Leave a Reply

Exit mobile version