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.
- SQL SERVER – Disabling 15000 Partitions (15k)
- SQL SERVER – Script to Get Partition Info Using DMV
- SQL SERVER – Introduction to Partitioning
- SQL SERVER – Database Table Partitioning Tutorial – How to Horizontal Partition Database Table
- SQL SERVER – Adding Values WITH OVER and PARTITION BY
Once in a while, I get questions where users have indeed explained the question well enough that I can easily understand their question. If I understand the question quickly, I am always able to answer it efficiently as well.
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)
