- 2012 (11.0.6607.3) Standard Edition
- This database hosted centrally and accessed by a windows application.
- 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.
- 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.