Reorganizing and Rebuilding Indexes

1
Reorganizing and Rebuilding Indexes
http//Café.naver.com/sqlmvphttp//judydba.tistor
y.com/ chusouk_at_gmail.com 010-7398-1136 ??(?????)
2
??
1. ???
1.1 ??? ????? 1.2 ??? ???? ?? ?? 1.3 ??? ????
?? 1.4 ??? ???? ?? ?? 1.5 ??? ??? ?? ??1.5
??? ??? ?? ??
2. Index Rebuild Tip
2.1 Index Rebuild FillFactor ?? 2.2 Reorganizing
and Rebuilding ?? 2.3 Clustered Index Rebuild?
DROP EXISTSING ?? 2.4 ?? ?? ??
3. Index Rebuild ??
3.1 Rebuilding ?? ?? 3.2 Rebuilding Case3.3
Rebuilding Offline Case
4. QA
3
1. ???
4
1.1 ??? ?????
- OLTP ???? ???? ?? ??
5
1.2 ??? ???? ?? ??
?. Insert and Update operations causing Page
Split ?. Delete operations ?. Initial allocation
of pages from mixed extents ?. Large row size

6
1.3 ??? ??? ??
?. Internal Fragmentation - Random deletes
resulting in empty space on data pages -
Page-splits due to inserts or updates -
Shrinking the row such as when updating a large
value to a smaller value - Using a fill factor
of less than 100 - Using large row sizes

7
1.3 ??? ??? ??
?. Logical Fragmentation - Page-splits due to
inserts or updates - Heavy deletes that can cause
pages be removed from the page chain, resulting
in dis-contiguous page chain

8
1.3 ??? ??? ??
?. Extent Fragmentation

9
1.3 ??? ??? ??
?. ??? ??
?. ???? ?? ??? ???
?. ??? ??/????/?? ? ??? ? ?? ???
10
1.4 ???? ?? ????
Logical fragmentation and Extent fragmentation
will cause the read performance to slow down
11
1.5 ??? ??? ?? ??
DECLARE _at_id int, _at_indid int SET _at_id OBJECT_ID('dbo.TblX') SELECT _at_indid index_id FROM sys.indexes WHERE object_id _at_id AND name 'nc_tblx_randSeq' dbcc showcontig('TblX', _at_indid) go
SELECT table_schema ,OBJECT_NAME(F.OBJECT_ID) obj ,i.name ind ,f.INDEX_TYPE_DESC AS IndexType, f.avg_fragmentation_in_percent, f.Avg_page_space_used_in_percent, f.page_count FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F JOIN SYS.INDEXES I ON(F.OBJECT_IDI.OBJECT_ID)AND i.index_idf.index_id JOIN INFORMATION_SCHEMA.TABLES S ON (s.table_nameOBJECT_NAME(F.OBJECT_ID)) AND f.database_idDB_ID() AND OBJECTPROPERTY(I.OBJECT_ID,'ISSYSTEMTABLE')0 WHERE F.OBJECT_ID OBJECT_ID('TblX') --WHERE F.index_id gt 0 AND F.index_id lt 1000 GO
12
1.6 ??? ??? ?? ??
?. Index Reorganizing(??? ?? ??)
ALTER INDEX index_name ALL     ON
ltobjectgt          REORGANIZE         
PARTITION partition_number          WITH (
LOB_COMPACTION ON OFF )    SET (
ltset_index_optiongt ,...n )     
DBCC INDEXDEFRAG
???? ????? ???? ?? ??? ??? ??? ??? ?? ?? ????
????? ?? ???? ??? ? ?? ????? ? ?????? ???? ?? ???
?? ?? ??? ??

• ???? ??? ???? ??
• ??? ?? ??? ??? ???? ???

13
1.6 ??? ??? ?? ??
?. Index Rebuilding(??? ?? ??)
???? ??? ?? ?? ??
ALTER INDEX index_name ALL     ON
ltobjectgt         REBUILD          PARTITION
ALL            WITH ( ltrebuild_index_optiongt
,...n )            PARTITION
partition_number                  WITH (
ltsingle_partition_rebuild_index_optiongt           
               ,...n )                 
                         SET (
ltset_index_optiongt ,...n )     
DROP INDEX ???? ON ???? CREATE INDEX ???? ON ????
DROP_EXISTING
CREATE UNIQUE CLUSTERED NONCLUSTERED
INDEX index_name      ON ltobjectgt ( column ASC
DESC ,...n )      INCLUDE ( column_name
,...n ) WHERE ltfilter_predicategt     
WITH ( ltrelational_index_optiongt ,...n )
     ON partition_scheme_name ( column_name )
          filegroup_name           default
               FILESTREAM_ON
filestream_filegroup_name partition_scheme_name
"NULL" DROP_EXISTING ON OFF

DBCC DBREINDEX (     table_name      ,
index_name , fillfactor )      WITH
NO_INFOMSGS
14
1.6 ??? ??? ?? ??
?. Reogranizing and rebuilding? ??
Characteristic Alter Index REORGANIZE Alter Index REBUILD
1 Online or Offline Online Offline (unless using the Online keyword)
2 Address Internal Fragmentation Yes (can only raise page density) Yes
3 Address Logical Fragmentation Yes Yes
4 Transaction Atomicity Small Discrete Transactions Single Atomic Transaction
5 Rebuild Statistics Automatically No Yes
6 Parallel Execution in multi-processor machines No Yes
7 Untangle Indexes that have become interleaved within a data file No Yes
8 Transaction log space used Less More
9 Additional free space required in the data file No Yes
15
2. Index Rebuild Tip
16
2.1 Index Rebuild FillFactor ??
?. Low Update Tables (100-1 read to write ratio)
100 fill factor ?. High Update Tables (where
writes exceed reads) 50-70 fill factor ?.
Everything In-Between 80-90 fill factor.

17
2.2 Reorganizing and Rebuilding ??
?. Fragmentation gt30 AND PAGESgt1000 ??
rebuild?. Fragmentation between 15 to 29 AND
PAGESgt1000 ?? reorganizeupdatestatistics?. ??
?? ??? ???? ?? ??, update the statistics  

18
2.3 Clustered Index Rebuild? DROP EXISTSING ??
DROP_EXISTING ?? SQL ??? ????? ???? ???? ?? ?? ?
??? ???? ?? ????? ???? ???? ?? ????? ???? ? ???
???? ?? ?? ???? ????? ???? ?? ?????? ?? ?? -
????? ???? ???? ?? ??? ?? ??? ???? ?? ? ??? ??
  

19
2.4 ?? ?? ??
??? ?? ?? ?? ??????? ?? ??? ?? ?? ???? ?? ?? ??
??? ???? ??? ??? ??? ??? ??? ? ??   

??? ?? ?? ?? ?? ??
ALTER INDEX REORGANIZE ?? ?? ?? ?? ?? ??
ALTER INDEX REBUILD ?? ?? ?? ?? ?? ??
CREATE INDEX ?? ?? ?? ?? ?? ??
DBCC INDEXDEFRAG ?? ?? ?? ?? ?? ??
DBCC DBREINDEX ?? ?? ?? ?? ?? ??
DROP INDEX ??? ??? ?? ??? ?? ?????. ?? ??? ?? ?? ?? ??? ? ?? ?? ?????. ??? ??? ?? ??? ?? ?????. ?? ??? ?? ?? ?? ??? ? ?? ?? ?????. ??? ??? ?? ??? ?? ?????. ?? ??? ?? ?? ?? ??? ? ?? ?? ?????.
20
3. Index Rebuild ??
21
3.1 Rebuilding ?? ??
?. ?? ??? ?? Source Sort Table B-Tree?
?? ?? 2.2Index Size ?? ?. ??? ?? ?? ??? ?? ??
?? a) Users Database(default) b)
tempdb(SORT_IN_TEMPDB ?? ??) ??? ?? ?.
Query Executor Process ??? ?? ??? ???
40Pages(3200KB)? ??? ??  
22
3.2 Rebuilding Case
Online Index Build Offline Index Build
Create clustered index idx_t on t(c1, c2) WITH (ONLINE  ON) Create clustered index idx_t on t(c1, c2)
Serial Index Build Parallel Index Build
Create index idx_t on t(c1, c2) Create index idx_t on t(c1, c2) WITH (MAXDOP  2)
Storing in Users database Storing in tempdb
Create clustered Index idx_t on t(c1) Create clustered Index idx_t on t(c1) WITH (SORT_IN_TEMPDB  ON)
Partitioned index build Non Partitioned build
23
3.3 Rebuilding Offline Case
Case Add Case Desc
1 Serial DISK 2.2Index Size Memory At least 40 Pages(3200KB)
2 Parallel Use Stats Plan(Historygram) Serial Build?? ? ?? ??? ?? DOP?? sort table ??
2 Parallel Non Stats Plan(No historygram) Indexed view(No Stats Plan) Parallel data source read
4 Parallel Partitionning (use sort_in_tmpdb) Aligned partitioned
4 Parallel Partitionning (use sort_in_tmpdb) Non-Aligned partitioned
Aligned (when base object and in-build index
use the same partition schema) Not- Aligned
(when heap and index use different partition
schemas (including the case when base object is
not partitioned at all and in-build index use
partitions))
24
??

1. ???? ??? ? ?? ????? ???? (?????
   ???)http//judydba.tistory.com/135
2. http//www.alicerock.com/1051
3. http//blogs.msdn.com/b/pamitt/archive/2010/12/23/
   notes-sql-server-index-fragmentation-types-and-sol
   utions.aspx
4. http//blogs.msdn.com/b/sqlqueryprocessing/archive
   /tags/indexing/

25
Thank you