July 17, 2013 at 6:17 am
Hi,
I was wondering if you guys would be able to shed some light on a subject that seems very involved!
We have a SQL Backend Database, which has just over a 250,000 rows (growing every day) in the main table. I have been tasked with trying to improve our Backups and Maintenance. It's an area that I have never really looked into as it was all set-up by the previous DBA. I was just looking for some thoughts on our current plan and maybe where you guys think it could be improved.
- Full DB Backup (Once Before Working Hours, Once During Working Hours, Once After Working Hours)
- DB Differential Backup (Every Hour)
- DB Integrity Check (Once a Day)
- Index Rebuild (Once a Day)
- Update Statistics (Once a Day after Index Rebuild)
- Checkpoint on TempDB (Once a Day)
- Transaction Log (Every 30 minutes)
- SP_Delete_BackupHistory (Once a week)
- SP_Purge_JobHistory (Once a day)
Thanks in advance!
July 17, 2013 at 6:46 am
Hi,
The best way of thinking about your backups is as a restore strategy. If you "lost" the database, how long would you have to bring it back online and what would be the acceptable level of data loss (if any)?
With a maintenance strategy it really depends. How long does it take for your indexes to become fragmented? Are you seeing poor performance due to invalid statistics?
Sorry to be so vague, but it really does depend on the system
Andrew
July 17, 2013 at 6:55 am
JAMMOD (7/17/2013)
- Full DB Backup (Once Before Working Hours, Once During Working Hours, Once After Working Hours)- DB Differential Backup (Every Hour)
- DB Integrity Check (Once a Day)
- Index Rebuild (Once a Day)
- Update Statistics (Once a Day after Index Rebuild)
- Checkpoint on TempDB (Once a Day)
- Transaction Log (Every 30 minutes)
- SP_Delete_BackupHistory (Once a week)
- SP_Purge_JobHistory (Once a day)
Thanks in advance!
Specific the backup plan is depending in your business requirement. How much data-loss is acceptable and how much time is allowed to get back online after a disaster. In my opinion the timing and frequency of the DIFF and LOG backups are not in line with each other. I should increase the frequency of the LOG backup to every 10 minutes or so. A full backup during working hours could also be a bit of an overkill.
Also the update of the statistics is probably unnecassary. This will allready be done with the rebuild of the indexes. Are all indexes rebuild?
What is the purpose of the Checkpoint on TempDB?
It looks like your previous DBA has implemented the SQL Maintenence Plan. Allthough this has a very simple implementation, it also has some drawbacks. Take a look at some custom maintenance solutions (like the one from Ola Hallengren http://ola.hallengren.com/)
July 17, 2013 at 7:31 am
Sorry but an update of the statistics is necessary as a rebuild index will not update the statistics on non indexed columns.
Andrew
July 18, 2013 at 2:46 am
Specific the backup plan is depending in your business requirement. How much data-loss is acceptable and how much time is allowed to get back online after a disaster. In my opinion the timing and frequency of the DIFF and LOG backups are not in line with each other. I should increase the frequency of the LOG backup to every 10 minutes or so. A full backup during working hours could also be a bit of an overkill.
Also the update of the statistics is probably unnecassary. This will allready be done with the rebuild of the indexes. Are all indexes rebuild?
What is the purpose of the Checkpoint on TempDB?
It looks like your previous DBA has implemented the SQL Maintenence Plan. Allthough this has a very simple implementation, it also has some drawbacks. Take a look at some custom maintenance solutions (like the one from Ola Hallengren http://ola.hallengren.com/)
Thanks for the replies guys! just a couple of questions on the above, would you be able to explain a bit more about why the DIFF and LOG backups should be more inline and to increase the amount of LOG backups, or if you have any links on this it would be great.
As far as I know it uses a Maintenance Plan to Rebuild Tables and Views and the option "Reorganize pages with the default amount of space is selected"
And with the Checkpoint on the TempDB, it has been one of those things that has been there since I took over and as it is working I haven't touched it.
Thanks,
July 18, 2013 at 2:53 am
JAMMOD (7/18/2013)[hrThanks for the replies guys! just a couple of questions on the above, would you be able to explain a bit more about why the DIFF and LOG backups should be more inline and to increase the amount of LOG backups, or if you have any links on this it would be great.
A DIFF backup is usually taken to shorten the time and minimize the actions needed to restore the database. The frequency of the LOG backup is a mayor part in determining how much data (measured in time) is allowed to get lost. See: http://msdn.microsoft.com/en-us/library/ms191239(v=sql.105).aspx as a starter. But remember: all depends on your business requirements.
July 18, 2013 at 4:45 am
Sorry - Update statistics is not required for Index Rebuild, automatically it will be updated. Please refer the below link. I have pasted the specific content below:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
After Maintenance Operations
Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates.
Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The query optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however; this statistics update is a byproduct of re-creating the index. The query optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.
July 18, 2013 at 6:13 am
What that means is that rebuilding indexes doesn't change the distribution of data and therefore the ALTER INDEX REBUILD operation itself doesn't make the statistics go out of date. However, bear in mind the following:
(1) As Andrew mentioned, unindexed columns won't have their statistics updated
(2) Any index that is reorganized won't trigger an update of statistics
(3) The statistics update automatically triggered by ALTER INDEX REBUILD is based on a limited scan of the data, whereas UPDATE STATISTICS is based on a full scan.
John
July 18, 2013 at 6:14 am
Sorry - Update statistics is not required for Index Rebuild, automatically it will be updated. Please refer the below link. I have pasted the specific content below:
http://msdn.microsoft.com/en-us/library/ms190397.aspx
After Maintenance Operations
Consider updating statistics after performing maintenance procedures that change the distribution of data, such as truncating a table or performing a bulk insert of a large percentage of the rows. This can avoid future delays in query processing while queries wait for automatic statistics updates.
Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The query optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however; this statistics update is a byproduct of re-creating the index. The query optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.
Hi,
I think you need to read this: http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/09/is-statistics-over-non-indexed-columns-updated-by-index-rebuild.aspx
Thanks,
Simon
July 18, 2013 at 8:00 am
Wah! Thank you Simon, John and Andrew.
July 18, 2013 at 8:12 am
John Mitchell-245523 (7/18/2013)
(3) The statistics update automatically triggered by ALTER INDEX REBUILD is based on a limited scan of the data, whereas UPDATE STATISTICS is based on a full scan.
Other way around.
When an index rebuild is run, the statistics on that index are updated with fullscan as part of the index rebuild process. It's not that the rebuild is triggering an automatic update, it's not. The update is part and parcel of the index rebuild operation.
On larger tables, UPDATE STATISTICS is sampled unless WITH FULLSCAN is specified. The details of what constitutes large and the sample rates are detailed in Books Online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2013 at 8:26 am
GilaMonster (7/18/2013)
Other way around.When an index rebuild is run, the statistics on that index are updated with fullscan as part of the index rebuild process. It's not that the rebuild is triggering an automatic update, it's not. The update is part and parcel of the index rebuild operation.
On larger tables, UPDATE STATISTICS is sampled unless WITH FULLSCAN is specified. The details of what constitutes large and the sample rates are detailed in Books Online.
Gail is right. I must have been thinking about partitioned indexes, and that only applies in SQL Server 2012 anyway. And I was mostly wrong about UPDATE STATISTICS, so please ignore my third point!
John
July 19, 2013 at 12:05 pm
Andrew Pruski (7/17/2013)
Sorry but an update of the statistics is necessary as a rebuild index will not update the statistics on non indexed columns.Andrew
Correct - however, it is rather wasteful to update statistics for index statistics that have just been updated due to an index rebuild. And, if you are not careful - you end up with a sampling rate instead of the recently built full scan.
Depending on the system, the maintenance window, etc... I will usually setup a process to rebuild all indexes weekly, and follow that with a task to update column statistics only with a full scan. That is usually good enough for the smaller systems I manage.
For larger systems, it gets much more complicated...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply