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)
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.