I think the title is misleading. 'Why Table Partitioning Doesn't Speed Up Query Performance' should be 'Why Table Partitioning Doesn't Speed Up Query Performance On Its Own'. Partitioning can definitely help query performance, but it's just that the actual partitioning is only 1 part of the work. To do it properly, you also have to make sure that the relevant queries are taking advantage of the partitioning scheme - be it by including filters on the partitioning column or by doing something more explicit with the $partition function, such that partition elimination happens as you want it to happen. When that is done then not only do you get the performance back, but it's actually a lot faster than the original situation. And it makes sense, especially for large tables, when partition elimination is working well suddenly you're searching in 1 or few small(in comparison to the complete table) table(s); couple that with aligned partitioned indices and the speed goes up a ton. Also of course the partitioning column itself has to make sense. If you partition on date for example and you do it by week, but all queries are using half a year chunks, then of course there's a mismatch between how it is partitioned and how it is actually being used, which would cause some of the issues described in the video.
Zepheldir is right.. Think that: There is a table which stores last 2 years of data (730 days). And ten millions of rows per day.. And I partitioned this huge table daily on its datetime column. When I query for just 1 day data will it be faster? And I've clustered index with date column for both (partitioned and non-partitioned) cases.. I can say that it will be more than 10x faster.. And how can I speed up the query without partition?
this is a huge help and the timing is perfect as I am in the process of consolidating our historical objects, which are decades of transactions, and was going to move forward with partitioning assuming the partitioning would provide performance improvements. You know what they say about assuming :)
one of the benefits of partitioning you have mentioned, namely "moving chunks of data in and out" of different partitioned sections of the table, which sounds like what you can also do with multiple tables , without the locking or blocking??
I have a weird error I am trying to chase down on an SSAS cube rebuild from a deep and wide fact table. There are 16 million rows and 188 columns on this table. It has a guid clustered primary key (ugh) and a HeaderID and LineID unique non clustered index as a business key however the partitioning is done on LineID into 150 partitions. So I have a non aligned non clustered index and partition. From time to time the SSAS cube rebuild fails. I do not yet have sysadmin access on production so I don't know details about the failures but... could this non aligned non clustered index and partition be the problem?
Hi, I'm new to partitioning in SQL Server, coming from an Oracle where it is very much toted as a query performance feature. I'm very confused why partition elimination wouldn't improve queries as long as they are referencing partition key in the query predicate? In Oracle, each partition has it's own segment on disk which can be scanned independently. Not sure if that's the case in SQL Server. In any case, that's a huge drawback of this feature IMO. Thanks, enjoyed the video.
I think the audience for this are junior devs/data engineers who merely see partitioning as a way to naively speed everything up, and hence misuse it. If used properly in many RDBMS, the performance gain can be worth the increase in complexity/overhead. I wouldn't go around partitioning everything, but it has it's uses. Some RDBMSes even parallelize operations across partitions, great for high concurrency situations of larger tables.
I've changed a lot in my recording process since this was made. Unfortunately, UA-cam doesn't allow replacing videos, so my choices are to either retire this old video (and leave a dead link to anyone who refers to it), or to leave it. I am considering taking down the older content, however.
I think the title is misleading. 'Why Table Partitioning Doesn't Speed Up Query Performance' should be 'Why Table Partitioning Doesn't Speed Up Query Performance On Its Own'.
Partitioning can definitely help query performance, but it's just that the actual partitioning is only 1 part of the work. To do it properly, you also have to make sure that the relevant queries are taking advantage of the partitioning scheme - be it by including filters on the partitioning column or by doing something more explicit with the $partition function, such that partition elimination happens as you want it to happen. When that is done then not only do you get the performance back, but it's actually a lot faster than the original situation. And it makes sense, especially for large tables, when partition elimination is working well suddenly you're searching in 1 or few small(in comparison to the complete table) table(s); couple that with aligned partitioned indices and the speed goes up a ton.
Also of course the partitioning column itself has to make sense. If you partition on date for example and you do it by week, but all queries are using half a year chunks, then of course there's a mismatch between how it is partitioned and how it is actually being used, which would cause some of the issues described in the video.
Zepheldir is right..
Think that: There is a table which stores last 2 years of data (730 days). And ten millions of rows per day..
And I partitioned this huge table daily on its datetime column.
When I query for just 1 day data will it be faster?
And I've clustered index with date column for both (partitioned and non-partitioned) cases..
I can say that it will be more than 10x faster..
And how can I speed up the query without partition?
this is a huge help and the timing is perfect as I am in the process of consolidating our historical objects, which are decades of transactions, and was going to move forward with partitioning assuming the partitioning would provide performance improvements. You know what they say about assuming :)
one of the benefits of partitioning you have mentioned, namely "moving chunks of data in and out" of different partitioned sections of the table, which sounds like what you can also do with multiple tables , without the locking or blocking??
I have a weird error I am trying to chase down on an SSAS cube rebuild from a deep and wide fact table. There are 16 million rows and 188 columns on this table. It has a guid clustered primary key (ugh) and a HeaderID and LineID unique non clustered index as a business key however the partitioning is done on LineID into 150 partitions. So I have a non aligned non clustered index and partition. From time to time the SSAS cube rebuild fails. I do not yet have sysadmin access on production so I don't know details about the failures but... could this non aligned non clustered index and partition be the problem?
Richard Inman g
Hi, I'm new to partitioning in SQL Server, coming from an Oracle where it is very much toted as a query performance feature. I'm very confused why partition elimination wouldn't improve queries as long as they are referencing partition key in the query predicate? In Oracle, each partition has it's own segment on disk which can be scanned independently. Not sure if that's the case in SQL Server. In any case, that's a huge drawback of this feature IMO. Thanks, enjoyed the video.
I think the audience for this are junior devs/data engineers who merely see partitioning as a way to naively speed everything up, and hence misuse it. If used properly in many RDBMS, the performance gain can be worth the increase in complexity/overhead. I wouldn't go around partitioning everything, but it has it's uses. Some RDBMSes even parallelize operations across partitions, great for high concurrency situations of larger tables.
Did Microsoft ever get this query bug fixed?
Very helpful, thanks
your info is great but the sound is horrible. get a decent mike and put a carpet down to kill the tinny echo
I've changed a lot in my recording process since this was made. Unfortunately, UA-cam doesn't allow replacing videos, so my choices are to either retire this old video (and leave a dead link to anyone who refers to it), or to leave it. I am considering taking down the older content, however.
super madam
you are misleading us.. please don't do that...