April 28, 2015 at 3:21 am
Looking around for an ideal maintenance plan. Specifically for two databases.
Staging database
we truncate the table and then repopulate with changed data (new or updated) from the source system.
Warehouse database
All tables carry a history of changes and the data from the staging is added in, we changed the current flag to zero and stamp the end date for the historical record.
The current implemented plan is for both databases, runs daily at midnight. Consists of
Check Database Integrity
Shrink Database (when grows beyond 2000MB, Amount of free space to remain after shrink set to 10% and return free space to OS)
Reorganize index (Staging and Warehouse)
Rebuild Index (Staging and Warehouse)
Update Statistics (Staging and Warehouse)
History Clean up
Previously I have dropped and rebuilt indexes on staging tables before and after we have loaded the data. I do not see the point of sorting the indexes and statistics at midnight and then truncating the table?
I don't like the Shrink database task and it is not restricting the size of the database files.
Is there a standard approach for a warehousing solution. Main concerns are speed of the plan and stopping database files and log files autogrowing to max out the disk.
Cheers
E
April 28, 2015 at 4:28 am
I'd get rid of the shrink database task immediately and then look at removing the index and statistics maintenance task by using Ola Hallengren's scripts separately.
I'd recommend running the DBCC CHECKDB separate from your index maintenance.
April 28, 2015 at 5:28 am
Doing a statistics update after an index rebuild will, in some situations, result in worse statistics. This is more of an issue in transactional systems, but you could hit similar issues. I'd separate those or at least update statistics prior to index rebuild.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2015 at 6:00 am
Many thanks for the help. I had forgotten about Olla's scripts.
Cheers
E
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply