Sql-server – Partitioning a database

partitioningsql-server-2008-r2

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?

Best Answer

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.

Related Solutions

Mysql – Creating partitions on a production database

I want to know how a new partitioned table will accommodate future years?

I'm fairly sure, according to the 5.1 documentation, that you'll have to alter table add partition to handle future years.

I also want to know whether I need to make changes to the table name in existing queries and replace the table name with the new partitioned table name

Are you needing to use the 'old' table after you get this set up? If not, I would follow these steps:

Create partitioned table as you want it, under table name 'mytable_new'
INSERT INTO mytable_new SELECT * FROM mytable
RENAME TABLE mytable TO mytable_old
RENAME TABLE mytable_new TO mytable

This should avoid the need to alter your queries. If your table is extremely write-heavy (I assume it must be if you have the need to partition it!), you will probably want to have a maintenance timeframe where it's offline until the operation is complete.

Hope this helps.

Mysql – Adding new key(s) to enable partitioning, INSERT performance

Your one select needs this "compound" index.

INDEX(DeviceID, Date)

Do you have other queries?

But your question was about PARTITIONing. No advantage, based on what you have said so far.

Best Answer

Related Solutions

Mysql – Creating partitions on a production database

Mysql – Adding new key(s) to enable partitioning, INSERT performance

Related Question