Performance & Tuning

Index operation

Index rebuild and Statistics update

http://weblogs.sqlteam.com/billg/archive/2011/02/08/sql-server-scripts-i-use.aspx

Logging issue

Version : SQL2008 R2

http://msdn.microsoft.com/en-us/library/ms191484%28v=sql.105%29.aspx

Index operationFullBulk-loggedSimple
ALTER INDEX REORGANIZEFully loggedFully loggedFully logged
ALTER INDEX REBUILDFully loggedMinimally loggedMinimally logged
CREATE INDEXFully loggedMinimally loggedMinimally logged
DBCC INDEXDEFRAGFully loggedFully loggedFully logged
DBCC DBREINDEXFully loggedMinimally loggedMinimally logged
DROP INDEXIndex page deallocation is fully logged; new heap rebuild, if applicable, is fully logged.Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged.Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged.

You need to change Bulk-logged operation when starting the index rebuild.

Even if Bulk-logged, “Alter index reoranize” is fully logged. So, please change the schedule of Trasanction log backup frequently.

Index rebuild

http://sqlfool.com/2010/04/index-defrag-script-v4-0/

Statistics update

http://weblogs.sqlteam.com/billg/archive/2010/12/02/script-to-update-statistics-with-time-window.aspx