Recovery Solution SQL Database Re-indexing Best Practices

book

Article ID: 181841

calendar_today

Updated On:

Products

Symantec Products

Issue/Introduction

 

Resolution

Problem
Usually, after half a year of operations, Altiris Recovery Solution database becomes very fragmented (more than 95% for some tables). This Knowledge Base article describes various methods of defragmentation and their positive and negative sides.

Environment
Symantec Altiris Recovery Solution 7.0

Cause
Some tables in the Recovery Solution database are heavily fragmented. This influences productivity of system.

Resolution

1.    Tables Fragmentation

6 tables occupy more than 90% of disk space in Recovery Solution database: FileSystemEntry, FileBackupHistory, FileObject, Revision, Serializedfrom, ClusterBlockInfo. As a rule, these tables are very fragmented (see below results of DBCC SHOWCONTIG from test 32GB database), that leads to performance degradation.

Fragmentation in SQL Server concerns data files. When a record is deleted, space is freed in the page. When a record is inserted in the middle of a clustered index, or at the leaf level of a non-clustered index, it may cause a page split. When a record is updated, its size may increase and the record may move to another page. All of these situations lead to data fragmentation. There are two types of fragmentation in SQL Server: internal and external. Internal fragmentation refers to empty spaces inside pages. External fragmentation refers to page links.

The DBCC SHOWCONTIG statement helps you determine external and internal fragmentation. If you run DBCC SHOWCONTIG, you may obtain the following result:

DBCC SHOWCONTIG (FileSystemEntry)
- Pages Scanned................................: 578842

- Extents Scanned..............................: 72827

- Extent Switches..............................: 506157

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 14.30% [72356:506158]

- Logical Scan Fragmentation ..................: 94.22%

- Extent Scan Fragmentation ...................: 98.40%

- Avg. Bytes Free per Page.....................: 2046.8

- Avg. Page Density (full).....................: 74.71%

 

DBCC SHOWCONTIG (Revision)

- Pages Scanned................................: 416807

- Extents Scanned..............................: 52549

- Extent Switches..............................: 203710

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 25.58% [52101:203711]

- Logical Scan Fragmentation ..................: 71.51%

- Extent Scan Fragmentation ...................: 98.73%

- Avg. Bytes Free per Page.....................: 3110.1

- Avg. Page Density (full).....................: 61.58%

 

DBCC SHOWCONTIG (FileBackupHistory)

- Pages Scanned................................: 685121

- Extents Scanned..............................: 86538

- Extent Switches..............................: 639317

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 13.40% [85641:639318]

- Logical Scan Fragmentation ..................: 96.61%

- Extent Scan Fragmentation ...................: 95.68%

- Avg. Bytes Free per Page.....................: 1487.7

- Avg. Page Density (full).....................: 81.62%

 

DBCC SHOWCONTIG (FileObject)

- Pages Scanned................................: 4993

- Extents Scanned..............................: 634

- Extent Switches..............................: 4990

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 12.52% [625:4991]

- Logical Scan Fragmentation ..................: 99.30%

- Extent Scan Fragmentation ...................: 99.68%

- Avg. Bytes Free per Page.....................: 2999.2

- Avg. Page Density (full).....................: 62.95%

 

DBCC SHOWCONTIG (Serializedfrom)

- Pages Scanned................................: 196352

- Extents Scanned..............................: 24731

- Extent Switches..............................: 25520

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 96.17% [24544:25521]

- Logical Scan Fragmentation ..................: 5.33%

- Extent Scan Fragmentation ...................: 99.97%

- Avg. Bytes Free per Page.....................: 406.4

- Avg. Page Density (full).....................: 94.98%

 

DBCC SHOWCONTIG (ClusterBlockInfo)

- Pages Scanned................................: 48681

- Extents Scanned..............................: 6450

- Extent Switches..............................: 40974

- Avg. Pages per Extent........................: 7.5

- Scan Density [Best Count:Actual Count].......: 14.85% [6086:40975]

- Logical Scan Fragmentation ..................: 95.07%

- Extent Scan Fragmentation ...................: 97.66%

- Avg. Bytes Free per Page.....................: 3684.8

- Avg. Page Density (full).....................: 54.47%

Defragmentation is especially necessary because:

·         Database pages (both tables and indexes) need to be split from time to time, which can result in bad allocations.

·         Defragmenting these pages will lead to less used space, and therefore a better use of I/O and memory.

·         Indexes can become corrupted or simply inefficient.

·         The SQL Server engine can occasionally misunderstand your intentions.

·         In situations where a large number of updates is the norm, things can get hairy (remember that any given update is actually a delete plus an insert).

·          Individual pages, while still work, may lose their optimal storage footprint.

2.    Defragmentation Methods

a.    DBCC INDEXDEFRAG

DBCC INDEXDEFRAG - defragments the leaf level of all types of indexes, and deals with internal and external fragmentation.

It guarantees that pages are linked in the physical order and compacted. Nevertheless, it does not allocate new pages, but simply reshuffles existing pages. So, a heavily fragmented index will not really benefit from this statement.

DBCC INDEXDEFRAG has one major advantage: It is an online statement, holding locks for a very short period of time because it’s using small transactions instead of one big transaction like other statements. You can then defragment an index while users are connected to the database without disturbing normal operations.


Its major drawback is that it has an online operation and is not as efficient as a DBCC DBREINDEX or a CREATE INDEX WITH DROP_EXISTING.


The results of testing on 32GB test database:

Table name

#indexes

Initial size (GB)

Final size (GB)

Execution time (h)

Performance increase

FileSystemEntry

4

12.6

9.7

06:30

~ 1.5-2.0

FileBackupHistory

3

10.3

7.6

04:03

-

Revision

2

4.8

3.0

01:22

-

Serializedfrom

2

3.3

2.8

01:15

-

FileObject

2

0.08

-

00:02

-

ClusterBlockInfo

2

0.6

0.4

00:07

-


The results of DBCC SHOWCONTIG (FileSystemEntry) run after defragmentation:

- Pages Scanned................................: 439433

- Extents Scanned..............................: 55305

- Extent Switches..............................: 55306

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 99.32% [54930:55307]

- Logical Scan Fragmentation ..................: 0.66%

- Extent Scan Fragmentation ...................: 98.78%

- Avg. Bytes Free per Page.....................: 127.8

- Avg. Page Density (full).....................: 98.42%

b.    DBCC DBREINDEX

DBCC DBREINDEX can be used to rebuild and then defragment any or all indexes of a table, and possibly change the index fill factor.


Its major advantage: It is an offline statement (if advantage?), more simple in use and more efficient as DBCC INDEXDEFRAG. If a non-clustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table. If the clustered index is being rebuilt, an exclusive table lock is held. This prevents any table access, therefore effectively making the table offline


Its major drawback is that it has an offline operation and needs stopping our service.


The results of testing on 32GB test database:

Table name

#indexes

Initial size (GB)

Final size (GB)

Execution time (h)

Performance increase

FileSystemEntry

4

12.6

8.6

01:03

~ 1.6-2.1

FileBackupHistory

3

10.3

7.4

00:44

-

Revision

2

4.8

2.9

00:20

-

Serializedfrom

2

3.3

2.7

00:26

-

FileObject

2

0.08

0.04

00:06

-

ClusterBlockInfo

2

0.6

0.3

00:21

-


The results of DBCC SHOWCONTIG (FileSystemEntry) run after defragmentation:

- Pages Scanned................................: 434762

- Extents Scanned..............................: 54346

- Extent Switches..............................: 54345

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [54346:54346]

- Logical Scan Fragmentation ..................: 0.01%

- Extent Scan Fragmentation ...................: 76.98%

- Avg. Bytes Free per Page.....................: 42.2

- Avg. Page Density (full).....................: 99.48%

c.    CREATE INDEX WITH DROP_EXISTING

The WITH DROP_EXISTING clause of the CREATE INDEX statement allows an index to be dropped and recreated in the same statement. This clause is equivalent to DBCC DBREINDEX (slightly less effective). The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.


This has a major advantage if you are running this statement on a clustered index, because the non-clustered index may not be rebuilt. As the clustered key is the row locator, if it does not change, the non-clustered index remains unchanged, too.


The results of testing on 32GB test database:

Table name

#indexes

Initial size (GB)

Final size (GB)

Execution time (h)

Performance increase

FileSystemEntry

4

12.6

8.6

01:23

~ 2.0-2.2

FileBackupHistory

3

10.3

7.4

01:37

-

Revision

2

4.8

3.0

00:32

-

Serializedfrom

2

3.3

2.8

00:18

-

FileObject

2

0.08

0.04

00:10

-

ClusterBlockInfo

2

0.6

0.3

00:28

-


The results of DBCC SHOWCONTIG (FileSystemEntry) run after defragmentation:

- Pages Scanned................................: 434762

- Extents Scanned..............................: 54346

- Extent Switches..............................: 54345

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [54346:54346]

- Logical Scan Fragmentation ..................: 0.01%

- Extent Scan Fragmentation ...................: 77.26%

- Avg. Bytes Free per Page.....................: 42.2

- Avg. Page Density (full).....................: 99.48%

d.    CREATE INDEX ON File Group

This clause is equivalent to DBCC DBREINDEX, but is faster (especially if non-clustered indexes are dropped). Also this method allows moving data of large tables into user filegroups, which leads to minimization of internal and external fragmentation and fragmentation decrease in future.


There are 2 tables (FileSystemEntry and FileBackupHistory) in our database, taking 30% apiece of the whole data amount which could be moved into new location (filegroup).


The results of testing on 32GB test database:

Table name

#indexes

Initial size (GB)

Final size (GB)

Execution time (h)

Performance increase

FileSystemEntry

4

12.6

8.6

00:27

2-5 (clustered index)

10-25 (nonclustered index)


The results of DBCC SHOWCONTIG (FileSystemEntry) run after defragmentation:

- Pages Scanned................................: 434762

- Extents Scanned..............................: 54346

- Extent Switches..............................: 54345

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 100.00% [54346:54346]

- Logical Scan Fragmentation ..................: 0.01%

- Extent Scan Fragmentation ...................: 0.14%

- Avg. Bytes Free per Page.....................: 42.2

- Avg. Page Density (full).....................: 99.48%

e.    RECREATE TABLE

the creation of new table and indexes is meant with subsequent renaming (and deletion of the old one). This method is not the fastest; besides, there are some problems with transactivity.


The results of testing on 32GB test database:

Table name

#indexes

Initial size (GB)

Final size (GB)

Execution time (h)

Performance increase

FileSystemEntry

4

12.6

8.6

01:36

-

 

f.     ALTER INDEX (SQL 2005)

ALTER INDEX REBUILD WITH ONLINE = ON is equivalent to DBCC DBREINDEX, but in online index operation. Test has shown that efficiency of this method is 30% less than DBCC DBREINDEX.


ALTER INDEX REORGANIZE is equivalent to DBCC INDEXDEFRAG. Specifies the index leaf level will be reorganized. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.


But we have to ignore them, because these statements appeared only in SQL Server 2005, and some users still have SQL Server 2000.


The results of testing on 32GB test database:

Table name

#indexes

Initial size (GB)

Final size (GB)

Execution time (h)

Performance increase

FileSystemEntry

4

12.6

8.6

01:24

-

 

3.    Conclusion

a.    Defragmentation routine

DBA should call Defragmentation routine approximately every 6 months. In case he calls it for the first time our service should be stopped. For the posterior times it is not obligatory, but advisable. There will be a special mechanism in IntegrityCheckJob to remind DBA about defragmentation (see item 3.b. below) This routine can be called without reminder also. The need of defragmentation could be clarified by DBCC SHOWCONTIG.

Defragmentation Query i.e. DefragTables.sql is attached to this KB Article.

b.    Reminder mechanism

The reminder mechanism consists in checking of the new LastDefrag field of VaultGroup table (datetime field) during IntegrityCheckJob for time passed since last update. If more than half a year has passed, the warning will be written to Application Event Log, which will remind about calling Defragmentation routine. This warning will appear after every IntegrityCheckJob until Defragmentation routine will update LastDefrag field.

c.    Testing

It is necessary to test:

-      on the SQL Server 2000 and on the SQL Server 2005;

-      with running RS server and with stopped RS server;

-      at least 2 times for each configuration (first step will be different from second step);

-      Check-up of PurgeDatabase stored procedure.

  1. References

    “SQL Server 2000 Books Online”
    “SQL Server 2005 Books Online”
    Marc Israel and J. Steven Jones “Design SQL Server 2000 Exam 70-229 © Sybex 2001”
    Kimberly L. Tripp “Filegroup Usage for VLDBs
    Tony Rogerson “Extent Usage and Behaviours when using DBREINDEX and SHRINKFILE

Attachments

DefragTables.SQL get_app