Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Posts Tagged ‘index rebuild’

Index rebuild vs. Statistics update

Posted by Simon Cho on 04/11/2012

http://sqlserverpedia.com/blog/sql-server-bloggers/update-statistics-before-or-after-an-index-rebuild/

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx

Another good article.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Posted in Common | Tagged: , | Leave a Comment »

Index operation

Posted by Simon Cho on 04/11/2012

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 operation

Full

Bulk-logged

Simple

ALTER INDEX REORGANIZE Fully logged Fully logged Fully logged
ALTER INDEX REBUILD Fully logged Minimally logged Minimally logged
CREATE INDEX Fully logged Minimally logged Minimally logged
DBCC INDEXDEFRAG Fully logged Fully logged Fully logged
DBCC DBREINDEX Fully logged Minimally logged Minimally logged
DROP INDEX Index 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

Posted in Common | Tagged: , , , | Leave a Comment »