I enjoy working with my clients on Comprehensive Database Performance Health Check. The best part of the consultation is that I get to work with new challenges every day. Most of the time, it is a new technology, new enhancements in a database or something just conversation which I keep on doing again for ages. One of the conversations which never gets old is Table Partitioning for Slow Performance.
I want you to read the statement again, I did not say table partitioning for faster performance, I have said for slow performance. While I discuss this in detail but I want to express my feeling in this blog post in few statements.
A Common Myth behind Slow Performance
Lots of people believe that – When you have a large table in your system, you can get better performance by doing table partitioning. The logic behind this thinking is that if it is a large table, SQL Server has to read the entire table to get the data and if the table is smaller, the process of reading the table is faster.
This is actually true in some cases but in most cases, if there is a huge table and it is scanned to read the few rows, no matter how much partitioning solution you apply, you will not get better performance at all.
Indexing vs Partitioning
If you have to read only a few rows from the table and your table is large or huge, the better solution is actually indexing and not partitioning. If you have indexed your table properly, when you want to read the data, you can easily read it from the index rather than going to the large table. If due to any reason, you have to go to the large table (key lookup or row id lookup), with the help of proper indexing, you will be reading only a few rows instead of the whole table.
If you think logically, indexing is nothing but partitioning where we know actually where our data is located. Even though if you partition your table, you still have to create indexes on it to retrieve your data faster.
When to Partition?
This brings us to the classic question. If the partition is not helpful in improving the performance of your query when doing the partitioning. Well, the answer is pretty straightforward. One should partition when you want to do a mass delete of your specific set of data frequently. In that case, you can create a partition and switch it with an empty partition to get faster delete of a large set of data.
Additionally, table partition can be helpful when you are running out of space on your disk.
In summary, partition itself may not get you better performance. It is quite possible when you partition your queries even start getting slower because now there is one more function to be processed between your query and data.
Reach out to me on Twitter and we can talk more about it.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
