SQL server - Archive/Unarchive or Partitioned View strategy

Question

question

mangeshtambare asked Aug 20, '18

SQL server - Archive/Unarchive or Partitioned View strategy

Environments

2012 (11.0.6607.3) Standard Edition

This database hosted centrally and accessed by a windows application.

Scenario

Currently, I am working on database optimization task. This database was developed long back by different developers.
After analyzing the database following are the highlighted problems
The high volume of data: There are 15 large tables in which the data is more than 10 million in each table.
Unused and Missing Indexes: After running the DMV – I found the unused and missing indexes on tables.
Following is the strategy – I can think to optimize the same.

optimize top 5 worst performing queries.
implement Archive and Unarchive Strategy or Implement Partitioned View.

Strategy

Archiving/ Unarchiving
Need to reduce the high volume of data. For the same – I am thinking to create an archive schema in the database. Create the exact tables under the archive schema. Create a SQL job which will move the records from dbo to archive schema and vice versa. For the same – I can have the Queue table from which SQL Job will pull the records and migrate to the archive tables.
Reason – The client may require accessing the archived data at any point in time.
Partitioned View
Table partition is supported in the SQL server enterprise edition. Client is having standard edition. For the same – I am thinking to spam the data under different tables and create a partitioned view.
With this approach the problem is – the along with partition key, there are columns which are participating the Searching the records. Therefore, table has on average 6 to 7 indexes. And there is huge DML operations are performed by an application on the database.
Please let me know your thought the on same.

sql server indexing sqlserver2012 optimization table partition

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-08-20T11:20:12Z

Magnus Ahlkvist answered Aug 20, '18

With a partitioned view, you can achieve more or less the same thing as you do with a partitioned table. You even get proper partition elimination when using partitioned views, just as with partitioned tables. What you need to achieve that is to have check constraints on each of the tables underneath the partitioned view, much the same as you would have boundaries in the partition function for a partitioned table.

If this is a good idea or not is a whole other thing. It depends on how the table is searched. If the partition key is always a part of the queries, ideally selecting single partitions/tables you will most probably benefit for all your queries. If you however have queries like SELECT MAX(somecolumn) WHERE <predicate which will have to look into several or even all partitions>, you'll get terrible performance instead. You can overcome the later scenario by rewriting the queries, but now comes the downside of partitioned views vs partitioned tables. With partitioned tables, you can easily cross apply against sys-views and that way get the max from within each partition and then get the max from that result set. With a partitioned view, it's not at all as easy to dynamically find all the partitions, simply because there aren't sys-views to support it.

So I wouldn't be awfully concerned about the number of indexes - you can easily automate the creating of new tables and indexes when you need to add a new partition to the partitioned view. What I would be concerned about is the ability to foresee all queries that you new partitioned view need to support.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question