SQL Server Query Performance - think about page split and fragmentation, measures to reduce the behaviour and best use of dm_db_index_physical_stats DMV
It is an universal truth in the database field that due to the fragmentation and page split the performance will be affected even for a simple [SELECT <ColumnName> from....] type of query. So what you need to check or take action in terms of DBA role, on the execution of queries ensure that columns that are included in the WHERE clause of your frequently running queries ensure index(es) exists. Also you need to ensure if you constant updates to those columns then modifying the data on those columns will have ups and downs for the index.
This is what called 'fragmentation', that will cause performance issues and its vital to understand how this can impact performance before we go down the route for better performance. Due to this fragmentation the disks will have to do more work in terms of I/O then the usual page splitting scenario, that means say if a data page has not got much space to fit in the row then that will obviously split the data pages to forward. This will also have affect on query performance in addition to slowing down the INSERT/UPDATE operation on that table. The usual 8K data pages limit will be reached quickly for the tables that will have bunch of NVARCHAR columns with the usual VARCHAR based columns. This is the reason it will be stressed at all the times to use NVARCHAR sensibly as it will cause page-split scenario very easily if you are not performing usual index defragmentat best practices. Further in this aspect say if that table has got cluster index then data needs to be stored in sorted order as cluster index ensure physical order of data according to the cluster key, but having dependable non-clustered indexes on the frequently queried columns would help a lot for performance gain.
In order to find the fragmentation since SQL Server 2005 version DBAs has got useful DMV called "dm_db_index_physical_stats". So to get brief fragment information on a table simply execute:
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N’DatabaseName’), OBJECT_ID(N’dbo.TableName’), NULL, NULL , ‘DETAILED’);
Taking example for the sample databases that will return by querying the sys.dm_db_index_physical_stats dynamic management function to return the average fragmentation for all indexes on the Production.Product table.
USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
For the given Database name (Parameter 1) and Table Name (Parameter 2). Last parameter suggest the mode of analysis and BOL refers about mode as:
mode | NULL | DEFAULT
Is the name of the mode. mode specifies the scan level that is used to obtain statistics. mode is sysname. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED
This can remedy index fragmentation by either reorganizing an index or by rebuilding an index, a simple set of information that is already documented on the BOL is referenced here that will help you to build a script to rebuild or reorganize the indexes on frequently used tables:
The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Executing this query requires the VIEW DATABASE STATE permission. This example specifies DB_ID as the first parameter without specifying a database name. An error will be generated if the current database has a compatibility level of 80 or lower. To resolve the error, replace DB_ID() with a valid database name. For more information about database compatibility levels, see sp_dbcmptlevel (Transact-SQL)
|
|
-- 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.
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 > 10.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;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.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
|
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.