SQL Index Fragmentation and Statistics
|Article:TECH168905|||||Created: 2011-09-06|||||Updated: 2014-05-12|||||Article URL http://www.symantec.com/docs/TECH168905|
Enterprise Vault (EV) or Accelerator processes perform slowly.
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.
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.
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%.
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.
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 Symantec recommends stopping all services prior to performing SQL maintenance.
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. Symantec 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 <databasename> statement has been executed first.
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.
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
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.
-- Loop through the 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)
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
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));
PRINT N'Executed: ' + @command;
-- Close and deallocate the cursor.
-- Drop the temporary table.
DROP TABLE #work_to_do;
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.
Symantec’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:
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
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
Article URL http://www.symantec.com/docs/TECH168905