Hot answers tagged

11

You generally followed a delusion - that partitioning will give you a significant performance boost for queries compared to a standard index. This is not the case. There is little difference between filtering with an index and with a partition. Partitions are not there to make your queries faster, but to allow fast DELETE - by swapping out a partition with ...


8

TomTom has a great answer with which I completely agree. sp_BlitzErik rightly cites Kendra Little as a good source for acknowledging that partitioning is not a performance feature: Why Table Partitioning Doesn’t Speed Up Query Performance Partitioning is, in fact, a data management feature. As noted by TomTom, bulk DELETE operations that encompass the data ...


2

If it was me, I would partition by hour, then you can just drop 24 partitions every day. That is only 744 partitions per month. Then if you query within a relatively short window, you will look at much less data. You may also want to look at your index strategy. My guess is that the cost of maintaining the indexes is too high. IMHO, smaller partitions should ...


1

There are many open ends in your question, but partitioning by customer could to be the way to go - especially if: you expect many customers, each of them could have tons of data ("tons" means much more than RAM cache size), most of their datasets will be mutually exclusive (each customer sees different subset of data). RULEs or triggers are a ...



Only top voted, non community-wiki answers of a minimum length are eligible