I have a database that's growing larger by the week (as expected). Key tables are becoming huge and slower to perform queries against. Whilst the indexes are sufficient, I've read the partitioning will improve the speed. What I was thinking on doing was creating partitions on these tables via year. I understand how the partitions would work for previous years. What I've not managed to find is how do you future proof these partitions? Do you have to create partitions for years to come? Or do you create the partition at the end of the current year?

1 Answer 1


Partitioning will not improve speed. Valid partitioning scenarios include storage management (via. hot-cold filegroups), data archival and ETL fast partition switch load. Not query performance. Here is a more detailed answer at How To Decide if You Should Use Table Partitioning which goes into details.

If you want to improve speed then you need to identify the bottlenecks and address them. You can follow How to analyse SQL Server performance for details. The answers will depend on your findings.

If you want a 'magic bullet' for data growing and ad-hoc analytical workloads problems then I would recommend looking into columnstores. I specifically say ad-hoc analytical because transactional OLTP workloads can and should be addressed with traditional indexes.

  • If you downvote, explain why Oct 19, 2015 at 11:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.