Do fragmanted indexes affect the system performance?

[yakcinar]

Performance issue becomes a real problem for some SAP systems. Especially if your DB size is big, there are many customer codes.

In one of our customer's system we had a performance issue. We had a meeting with all parts involved in SAP Systems. HW vendor, Network group, Business, System Admins and BASIS. The result of the meeting was an action list.

Beside all architectural actions, one of the action was finding fragmanted indexes and applying defragmantation and it was my duty to do the needful.

This blog is output of this action and about index defragmantation on SQL Server.

I will try to explain the topic in the following order.

1. List the indexes that will be possibly defragmanted and effecting the system performance
2. Analyze the indexes
3. Defragmant the indexes
4. Run new Statistics for the indexes
5. Results and Chart
6. Pros and Cons of defragmanting

And these are the details of the steps:

1. List the Indexes

When we were listing the fragmanted indexes our scope was on the indexes that are used in expensive SQL Statements. We also checked the indexes of the tables that are accessed sequentially on process overview.

Go to DBACOCKPIT --> Performance --> SQL Statements

Double Click on one of the SQL Statements.

Go to Explain Tree Tab to see the index names used in the SQL Statements.

For other indexes we use SM66 Global Work Process Overview.

Check the sequential reads and related tables.

After taking the list of related tables you go to SE11 and take the index names.

2. Analyze the Index

Go to DBACOCKPIT --> Space --> Single Table Analysis

Enter the related table name and select the index name.

Select DBCC showcontig  button.

    

     Run one of the options Fast, All Levels and Normal.

    

Yo can also use SQL SQL Server Management Studio to find DBCC Showcontig results using similar Queries like below.

    

DBCC SHOWCONTIG allows you to measure fragmentation and page density levels on indexes.

The result is analyzed according to the document "Microsoft SQL Server 2000 Index Defragmentation Best Practices" mentioned in SAP Note 159316 - Reorganizing tables on SQL Server. You can check the "Analyzing Output from DBCC SHOWCONTIG" section in the document for details.

The document says that  "High values for logical scan fragmentation can lead to degraded performance of index scans. Consider defragmenting indexes with 20 percent or more logical fragmentation."

It states also that "indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages)."

So we selected all indexes that has logical scan fragmantation more than 20% and more pages.

3. Defragmant the indexes

We used SQL Server Management Studio Query editor to run scripts for defragmanting.

Examples:   DBCC INDEXDEFRAG (<SID>, "<sid>.EKPO", [EKPO~0])

                    DBCC INDEXDEFRAG (<SID>, "<sid>.RFBLG", [RFBLG~0])

Duration of defragmantation changes according to the index sizes and Logical Scan Fragmentation percentage. As you see below screen it took 3 hours. Some lasts more than a day.

This process also generates logs. So you must monitor your log space during the process. Otherwise your system can stop due to lack of log space.

DBCC INDEXDEFRAG reports the estimated percentage completed every five minutes.

DBCC INDEXDEFRAG can be stopped at any point in the process, and any completed work is retained.

4- Update Statistics

     We updated statistics of the related tables after defragmantation to obtain the changed statistics.

    

5- Results and Graph

    When we rerun DBCC Showcontig command after defragmantation we can easily see the decrease in Logical Scan Fragmentation.

     The value became an acceptable value as it is seen in below picture.

    

     As a result, we took the I/O Stall read and write times from DBACOCKPIT --> Performace --> History --> Database Collector Data Time Series table.

    

     We exported data to an excel sheet and took the averages of all read and write times for the whole period. (Red and orange lines in below chart).

     From the chart below it can easily be seen that I/O values decreased significantly. So it worth defragmenting.

    

6- Pros and Cons of defragmanting

• First of all, SAP explicitly recommends not to reorganize or rebuild any database objects on a regular basis. You should not even reorganize or rebuild objects as an attempt to solve a performance problem as long as it is not evident that fragmentation is the root cause of the problem (which it hardly ever is).

• You can do this process for particular indexes that are fragmented with high percentages, has alot off pages and used in expensive sql statements.

• It is time consuming job

• Generates lots of logs, monitoring is must

• Defragmentation can be done online. Instead DBREINDEX can be used for offline solution.

Resources:

159316 - Reorganizing tables on SQL Server

1660220 - Microsoft SQL Server: Common misconceptions

Microsoft SQL Server 2000 Index Defragmentation Best Practices