How to review SQL fragmentation levels on Enterprise Vault (EV) Databases with Excel

Problem

High SQL fragmentation levels will degrade Enterprise vault performance.

When logical fragmentation levels are above 50% and/or extent fragmentation levels are above 70%, EV will experience degraded performance and possible failures.

Extents should be 1 higher than Extent switches. If excessive switching is occurring, EV will experience degraded performance and possible failures.

Solution

 In SQL Server Management Studio, right click the desired database, then click “New Query”

3. Click Execute, then browse to the desired location and save the result file.
4. Once the query completes, copy the results file to a machine where Excel is installed.
5. Open Excel
6. Click the “Data” tab
7. Click the “From Text” button
8. Browse to the location where the results file was saved
9. Change the file type selection, to all files

10. Select the results file and click “Import”
11. Choose “Fixed Width” and click “Finish”
12. Delete Row 2 as it only contains dashes that will affect the layout of the page.
13. Hold control, and press the  “End” key
14. Press the “Home” key
15. The bottom 4 rows should be a blank row, the number of rows affected, another blank row, and an execution completed row. These rows also need to be deleted as they will affect the layout of the page.
16. Hold control, and press the “Home” key
17. Click the gray square above the number 1 and to the left of the A column (this will highlight the entire sheet)
18. Place the cursor over the vertical line between the A and B columns. When the cursor changes to a vertical line with arrows pointing left and right, double click the vertical line to collapse all columns to the minimum area required to see all content in the cells.
19. Highlight the entire sheet as described in step 18, then click the “Sort” button
20. Check the box labeled “My data has headers” and sort by IndexId, smallest to largest.

21. Click “Ok”
22. Delete all rows where the IndexId is equal to 0, as IndexId 0 represents a SQL heap, and is not used in this analysis.

23. Repeat steps 20 – 22, and sort by “Pages”, smallest to largest
24. Delete any rows where “Pages” is less than 1000, as these are not a concern
25. Delete the following unneeded columns:

• ObjectId
• Level
• MinimumRecordSize
• MaximumRecordSize
• AverageRecordSize
• ForwardedRecords
• AverageFreeBytes
• AveragePageDensity
• ScanDensity
• BestCount
• ActualCount

23. Sort the remaining data for fragmentation levels and extent switches as needed.
24. Based on the results found, if needed, perform SQL maintenance as described in 000040169 to reduce SQL fragmentation