SQL Index Fragmentation and Statistics

book

Article ID: 100006254

calendar_today

Updated On:

Cause

SQL performance is key to releasing Enterprise Vault (EV) threads and to conserve resources. Many EV threads request an action by SQL in order to complete their task. Once the task is complete, the thread becomes idle and is marked to be deleted during a garbage run. The garbage run deletes these threads and places the memory used by the idle threads back into the application memory pool to be used by other worker threads.

Stale SQL Statistics and excessive Logical and Extent fragmentation of SQL indexes, play a huge role in SQL stored procedure performance. Threads will ‘stack up’ to wait for access to the SQL tables/views. The longer the wait, the more threads will stack up with more memory being consumed by the worker threads. So it is critical for programs that rely tremendously on SQL, like EV and the Accelerator products, to have the Statistics and Index fragmentation monitored and maintained. Maintenance must be performed in order for SQL to run efficiently.

Resolution

INDICES:

To monitor Index Fragmentation:

The sys.dm_db_index_physical_stats function is the preferred tool to use to check index fragmentation on any CA or DA database table. Veritas now uses the heavily debated command DBCC SHOWCONTIG WITH ALL_INDEXES,or DBCC SHOWCONTIG WITH ALL_INDEXES,tableresults (Table results will give you a table format to read) to monitor the health of the table indexes when the use of the sys.dm_db_index_physical_stats function, in addition to other recommended adjustments, does not improve the performance of CA or DA. Since the release of SQL 2005, Database Administrators have used the sys.dm_db_index_physical_stats function to gather index fragmentation information. The one element lacking from the Index physical statistics is the Extent fragmentation (page order) calculation. Since EV indices do not span multiple data files, the Extent fragmentation value provides a valuable overview of the index fragmentation health.

How to analyze a SHOWCONTIG result:

(Example of SHOWCONTIG)

DBCC SHOWCONTIG scanning 'tblIntDiscoveredItems' table...
Table: 'tblIntDiscoveredItems' (1364915934); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned.................................................: 20424
- Extents Scanned...............................................: 2564
- Extent Switches...............................................: 4912
- Avg. Pages per Extent.........................................: 8.0
- Scan Density [Best Count:Actual Count]........................: 52.67% [2553:4513]
- Logical Scan Fragmentation ...................................: 59.94%
- Extent Scan Fragmentation ....................................: 98.10%
- Avg. Bytes Free per Page......................................: 550.7
- Avg. Page Density (full)......................................: 43.20%

1. Starting with the line: Table: 'tblIntDiscoveredItems' (1364915934); index ID: 1, database ID: 5.

The index ID can be any value but ‘0’, in this example the index ID is ‘1’. A ‘0’ index ID or ‘heap’ index, indicates the table does not include a clustered (main) index. SQL will add a heap index to tables without a clustered index. Heap indexes can be ignored on fragmentation levels since the stored procedures designed to access these tables will never use the heap index.

2. Pages Scanned – rule of thumb is to ignore tables with less than 1,000 pages. SQL processes will run through tables with less than a thousand pages quickly enough to not cause any noticeable latency.

3. Logical Scan Fragmentation – item order within an extent (explained next). When Logical Scan Fragmentation reaches above 10%, latency will begin to be apparent and once Logical Scan Fragmentation reaches above 50% , the index usage is significantly slower when executing stored procedures that make use of the index.

4. Extent Scan Fragmentation – page order fragmentation. An extent is 8 pages of index. An extent is loaded into memory when an index is called. If the pages are out of order to an extent of over 70% fragmentation, noticeable latency will occur.

5. Avg. Page Density (full) – is the percentage of data that fills each index page. If the percentage is below 50%, each Extent read into memory will be equivalent to less than 4 pages of index data instead of the optimal 8 pages. Once maintenance is performed to alleviate index fragmentation, the average page density will increase to above 90%.

For optimal performance, logical fragmentation needs to be below 10% and Extent below 70%.

Analysis of the above SHOWCONTIG example shows:

1. Index ID = 1, so it qualifies to be examined.
2. Pages Scanned is above 1,000 pages making the index fragmentation important.
3. Logical Scan Fragmentation is not only above 10% which causes noticeable latency, but is above 50% which makes the index usage significantly slower.
4. Extent Scan fragmentation is above 70% also causing extensive latency.
5. Average Page Density is way below 90% causing more index reads to navigate through an index.

The above SHOWCONTIG shows this index is a prime example of an index in need of maintenance.

Other Notables:

1. Table access (reads) will force SQL maintenance to only deal with the fragmentation of the items in memory, or an extent. So, when services are running that access the tables in a database, SQL will not be able to swap pages between extents and thus only be able to defragment the logical order of index items and not the extent or page order. This will become evident as time progresses and logical Scan Fragmentation is low, but Extent Scan Fragmentation continues to climb; this is why Veritas recommends stopping all services prior to performing SQL maintenance.

In production environments, stopping all EV services for maintenance is hard to accommodate. In order to not force the services to be shut down every time maintenance is run, it is recommended to only shut down services when extent scan fragmentation begins to exceed the 70% value.

Steps to determine an ‘EV services shut down for maintenance’ schedule:
A. Stop all EV services and run SQL maintenance to determine Baseline figures. Save the fragmentation statistics from a SHOWCONTIG query for all indexes with tables that contain more than 1,000 pages.

B. Bring service back online and continue to conduct your standard SQL maintenance schedule with EV services running. Veritas recommends to perform SQL maintenance at least once per week.

C. Examine the Extent Scan Fragmentation of those table with over 1,000 pages to determine the rate of fragmentation growth after each scheduled maintenance run.

D. Multiply the rate of growth to determine how long it will take the Extent Scan Fragmentation to exceed the 70% threshold. Do not allow Extent Scan Fragmentation to exceed 90%.

E. From the above data, a scheduled stopping of EV services for maintenance can be determined.

2. Once Logical Scan Fragmentation reaches 100% , standard SQL maintenance will not be able to properly defragment the indexed items. A manual script can be run in order to defragment those indices.

A. Stop the EV and Accelerator services for those databases.

B. Run the following script against the appropriate database.

-- Ensure a USE  statement has been executed first.

USE   DB_NAME

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR

SELECT * FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

   BEGIN;

        FETCH NEXT

           FROM partitions

           INTO @objectid, @indexid, @partitionnum, @frag;

        IF @@FETCH_STATUS < 0 BREAK;

        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

        FROM sys.objects AS o

        JOIN sys.schemas as s ON s.schema_id = o.schema_id

        WHERE o.object_id = @objectid;

        SELECT @indexname = QUOTENAME(name)

        FROM sys.indexes

        WHERE object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*)

        FROM sys.partitions

        WHERE object_id = @objectid AND index_id = @indexid;

-- 10 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

        IF @frag < 10.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

        IF @frag >= 10.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

        IF @partitioncount > 1

            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

        EXEC (@command);

        PRINT N'Executed: ' + @command;

    END;

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO

Note: The script above was originally published by Microsoft. However, the fragmentation percent was modified from 30% to 10% for a more aggressive defragmentation process.

STATISTICS:

The importance of updating Statistics:

SQL uses Statistics to determine which index to use when executing a stored procedure and which mode to use on the index; either an Index Seek or an Index Scan. Over time Statistics will become stale resulting in incorrect information causing SQL to misuse the index which will result in poor SQL performance.

When an Index Logical Scan Fragmentation exceeds 50%, older versions of SQL Server (i.e., pre-2005) used to ignore the Statistics recommendations and perform a Table Scan instead. A Table Scan reads the entire first record from the table, then proceeds to the next record and so forth until the record(s) sought is found. A Table Scan is the most costly possible solution when accessing data, so it is important that both Index Fragmentation is kept low and Statistics are kept up to date in the older version of SQL Server. In newer versions, the index will still be used, but will have a significant delaying impact on anything that uses the index.

The maintenance step 'Update Statistics' will recompile every stored procedure in a database. During the recompiling, Statistics reads table column data to optimize data distribution within a table. Statistics will also determine the best method to use on an index, per stored procedure; either an Index Seek or an Index Scan.

Using the SQL AutoStat feature will automatically update a table’s Statistics after 20% of the table has been modified on tables with over 500 rows. This feature has a good and bad affect, On the good side, the Statistics are updated automatically. On the bad side, the overhead of Update Statistics normally occurs during heavy work load and will degrade performance, secondly stale Statistics might reside in tables randomly modified. Thus, adding ‘Update Statistics’ to the regularly scheduled maintenance is recommended.

Veritas’s standard SQL maintenance includes:
- Shrink databases (optional step that, if used, must be performed before backing up the transaction logs as the shrink operation causes significant growth in those logs)
- Rebuild Indexes
- Update Statistics

When a database is shrunk, performance may be degraded because the current Statistics mode is based on the low data retrieval prior to the database being shrunk and thus, the wrong index mode may be called. So logic states: When a database is shrunk, Update Statistics must be performed in order to maintain or improve SQL performance.

How to determine last Update Statistics run:

Run the following query against the appropriate database. The oldest Statistic will list first AND only Statistics that are older than one day. If the value NULL appears, this indicates no data in the table or a Statistics Update has never been performed. If no results are returned, all Statistics have been updated in the past 24 hours.

USE   DB_Name

SELECT o.name 'Table Name', i.name 'Index Name', i.type 'Index Type'

,STATS_DATE(i.[object_id], i.[index_id]) 'Last Statistics Update'

FROM sys.indexes i, sys.objects o

WHERE i.[object_id]=o.[object_id]

AND o.type='U' AND i.name IS NOT NULL

AND STATS_DATE(i.object_id, i.index_id) < DATEADD(DAY, -1, GETDATE())

ORDER BY 'Last Statistics Update' DESC

Issue/Introduction

Enterprise Vault (EV) or Accelerator processes perform slowly.

Was this article helpful?

thumb_up Yes

thumb_down No

Welcome to "KB Articles"