October 29, 2013 at 11:02 am
Hi SSC,
I’m doing some testing on index fragmentation and I wanted to re-build (or reorganize) some of the indices, something like this:
alter index myIndex on dbo.MyTable rebuild;
or
alter index myIndex on dbo.MyTable reorganize;
Some of these indices are compressed. My question is this: when I re-build the index, do I have to explicitly specify data_compression again or will a rebuild/reorganize use the current compression settings of the index?
imagine this was the initial index creation statement:
create nonclustered index myIndex on dbo.MyTable with (data_compression = page)
October 29, 2013 at 12:00 pm
Nevermind, I found the answer to my own question. From BOL...
"When an option is not explicitly specified, the current setting is applied. For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process. To view the current index option settings, use sys.indexes."
October 30, 2013 at 4:23 am
Yep, you got it.
One point. You might want to consider skipping reorganize. For most indexes it doesn't do much, but is still a fairly costly operation. For detailed testing on this look up an article by Brad McGehee where he lays it all out.
"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
November 1, 2013 at 11:51 am
Grant Fritchey (10/30/2013)
Yep, you got it.One point. You might want to consider skipping reorganize. For most indexes it doesn't do much, but is still a fairly costly operation. For detailed testing on this look up an article by Brad McGehee where he lays it all out.
+1
Back with SQL Server 2000, I did extensive testing, and practice agreed with theory - the end result of REBUILD is universally superior to or equal with the end result of REORGANIZE.
REORGANIZE is useful in a handful of edge cases where you have specific requirements, but I personally never use it, as I work with systems that either are Enterprise edition and can do REBUILDs online, or with fixed maintenance windows where I can lock any table/index for the duration of the maintenance window.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply