Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: