Studying custom index fragmentation reports

A custom script from a Production Asset Management (PAM) system reports on indexes that are in ‘red’ state and suggests that a rebuild is necessary. But is it really?

ObjectName

Rebuild

IndexName

IndexId

Rows

Pages

Extents

Extent Switches

Scan Density

Best Count

Actual Count

Logical Fragmentation

Extent Fragmentation

BinElem

rebuild

PK_ElemBin

1

6939

102

28

62

20.63

13

63

75.49

78.57

BasedPlaylistItem

rebuild

PK_BasedPlaylistItem

1

778

155

44

74

26.67

20

75

60.65

90.91

Directory

rebuild

PK_Directory

1

12323

783

118

316

30.91

98

317

26.44

77.12

AutoCompleteSearch

rebuild

PK_AutoCompleteSearch

1

4111

65

18

29

30

9

30

38.46

77.78

AutoCompleteSearchInstance

rebuild

CLUST_NDX_AutoCompleteSearchInstance

1

6499

72

19

44

20

9

45

87.5

78.95

TC2Field

rebuild

CLUST_NDX_TC2Field

1

6002

43

12

38

15.38

6

39

88.37

58.33

IdentifiantPlaylistElem

rebuild

PK_IdentifiantPlaylistElem

1

3489

245

76

119

25.83

31

120

58.78

94.74

Bin

rebuild

PK_Bin

1

2717

72

15

58

15.25

9

59

59.72

73.33

AssetKeyword

rebuild

PK_AssetKeyword

1

5562

49

23

34

20

7

35

81.63

82.61

AssetItemAccess

rebuild

PK_AssetItemAccess

1

56747

605

111

587

12.93

76

588

98.68

82.88

Asset

rebuild

PK_Asset

1

1E+05

7498

980

3098

30.27

938

3099

4.61

89.9

MediaItemHistory

rebuild

PK_MediaItemHistory

1

5120

417

127

216

24.42

53

217

72.18

94.49

BasedPlaylist

rebuild

PK_BasedPlaylist

1

2834

150

33

65

28.79

19

66

72.67

84.85

 

Looks like quite a few indices need to be rebuilt. But when we run the re-indexing script provided by the same vendor, no new indexes are actually being rebuilt (the MS SQL Agent Job does nothing).

 

Is this normal?

Simply speaking, index data can be:

  • placed within each (or the only) page in an ordered way, without much blank space.
  • spread across hundreds of pages with each page only containing, say, 30% of data and 70% of free space. In this scenario SQL server will still have to read through all the unused space within the page, causing unnecessary use of CPU cycles and memory.

 

So what kinds of indexes can lead to slow performance of queries? Indexes with many pages, which are sparsely populated.

If we run “DBCC SHOWCONTIG WITH TABLERESULTS”, the columns containing this information would be ‘Pages’ and ‘AveragePageDensity’. Let’s take one example:

We would expect the application’s maintenance script (the one that was added to SQL Agent by installer) to rebuild these indexes overnight, as they are highlighted in red? But for some reason it doesn’t.

 

Why?

First, let’s look at the monitoring script’s criteria for marking indexes as requiring a rebuild. Rebuild link will appear when:

  • Best count > 5
  • Scan Density < 90

 

And what about the SQL Agent job? The reindexing job criteria is different:

  • avg_fragmentation_in_percent > 5
  • page_count > 1000

 

Since the page count is smaller than 1000, the two examples above will never be re-built, unless the number of pages exceeds 1000.

 

Performance gains?

Let’s do some performance tests against these two indexes.

First, let’s formulate a query and ensure that it will make use of the index:

And measure the execution time:

Based on 0 execution time it appears that these indexes are so insignificantly small that there probably is no need to mark them as ‘red’ within the index monitoring script. The fragmentation that is likely to have any sort of performance impact is only going to occur in much larger indexes.

So how can we get rid of the annoying ‘red’ entries in the application’s index fragmentation report?

If we click on what actually (!) is a link, the index will be re-built manually, turning the respective row color from Las-Vegas gold back to sludge-metal-fan pasty:

Before:

After:

 

Outro

What did we discover today?

  • There is a discrepancy between what index fragmentation report will show you, and what the maintenance tool will actually attempt to rebuild/reorganise on a nightly basis.
  • If your maintenance jobs are configured correctly, you can safely ignore all PK_* index warnings in the fragmentation report tool
  • There is a hidden link that doesn’t look like a link that will rebuild the index for you.