How to identify and solve the fragmentation in SQL Server?
How does fragmentation occur in SQL Server?
If there is an index on the object, the data is ordered in the page using the slot array. When a new insert comes, SQL engine will search an available page to keep the data by using the index. The data should be saved in the specific page because it should be ordered. The data should be in the specific page but if there is no enough space in the page, a new page will be allocated and the original page will be split and previous, next page numbers will be updated. This process will cause fragmentation. It can be monitored using the “page split/sec” counter.
Another reason is because of the data type. Let’s think about that we have one table with a column which is varchar(100) data type. If 10 characters are inserted, it will allocate 10 bytes and if the page is full and if the data is updated to 20 characters it will look for 10 more bytes in the same page. But the page is full so it will place a pointer to forward the date to a new page. This process will also cause fragmentation. It can be monitored using the “forwarded records/sec” counter.
If the pages are fragmented, it will increase the disk search time and it will cause the performance issue.
How can we monitor the fragmentation and How can we fix the fragmentation issue?
Maintenance Plans can be used like below.
 |
 |
| Create a maintenance plan | Maintenance Tasks |
If the fragmentation level is greater than 25 or 30 percent, Rebuild Index Task is recommended otherwise Reorganize Index Task can be used. But there is no any option to give the threshold in those tasks.
Rebuild Index Task also updates the statistics but Reorganize Index Task does not.
Rebuild Index Task can take longer time to be completed. There may be some big indexes on read-only tables so it is not useful to rebuild them. It will only cause longer time. How can we eliminate those indexes from the plan? The answer is custom script.
You can check the script below and implement it on your environment. You can make some customization on that. Please let us know if you have any improvement on that
--part 1: create the objects
SET NOCOUNT ON;
if not exists (select name from sys.databases where name = 'SQLAdmin')
create database SQLAdmin
go
use SQLAdmin
go
if not exists (select name from sys.objects where name = 'fragmentation_history')
create table fragmentation_history(
history_id int identity(1,1),
database_id smallint NULL,
database_name sysname NULL,
schema_id int null,
schema_name sysname null,
object_id int NULL,
object_name sysname NULL,
index_id int NULL,
index_name sysname NULL,
partition_number int NULL,
avg_fragmentation_in_percent_before float NULL,
avg_fragmentation_in_percent_after float NULL,
alter_start datetime NULL,
alter_end datetime NULL,
progress datetime NULL
) ON [PRIMARY]
if not exists (select name from sys.objects where name = 'sql_errors')
create table sql_errors(
error_id int identity(1,1),
command varchar(4000) null,
error_number int null,
error_severity smallint null,
error_state smallint null,
error_line int null,
error_message varchar(4000) null,
error_procedure varchar(200) null,
time_stamp datetime null,
primary key clustered
(
error_id asc
) ON [PRIMARY]
) ON [PRIMARY]
GO
if exists (select name from sys.objects where name = 'p_error_handling')
drop procedure p_error_handling
go
create procedure p_error_handling
@command varchar(4000)
as
DECLARE @error_number int
DECLARE @error_severity int
DECLARE @error_state int
DECLARE @error_line int
DECLARE @error_message varchar(4000)
DECLARE @error_procedure varchar(200)
DECLARE @time_stamp datetime
SELECT @error_number = isnull(error_number(),0),
@error_severity = isnull(error_severity(),0),
@error_state = isnull(error_state(),1),
@error_line = isnull(error_line(), 0),
@error_message = isnull(error_message(),'NULL Message'),
@error_procedure = isnull(error_procedure(),''),
@time_stamp = GETDATE();
INSERT INTO dbo.sql_errors (command, error_number, error_severity, error_state, error_line, error_message, error_procedure, time_stamp)
SELECT @command, @error_number, @error_severity, @error_state, @error_line, @error_message, @error_procedure, @time_stamp
GO
--part 2: Collect the fragmentation data
SET NOCOUNT ON;
DECLARE @command varchar(8000);
DECLARE @databaseid int;
DECLARE @databasename sysname;
DECLARE database_list CURSOR FOR
SELECT database_id, name
FROM sys.databases
where database_id > 4 and state not in (1)
order by name
-- Open the cursor.
OPEN database_list
-- Loop through the partitions.
FETCH NEXT FROM database_list
INTO @databaseid, @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
--set @databasename = 'AdventureWorks2008'
set @command = 'use [' + @databasename + '];'
set @command = @command + '
insert into SQLAdmin.dbo.fragmentation_history (database_id, database_name, schema_id, schema_name, object_id, object_name, index_id, index_name, partition_number, avg_fragmentation_in_percent_before)
SELECT D.database_id, D.name, O.schema_id, s.name, IPS.object_id, O.name, IPS.index_id, I.name, partition_number, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (' + convert(varchar(3), @databaseid) + ', NULL, NULL , NULL, ''LIMITED'') IPS
join sys.databases D on IPS.database_id=D.database_id
join sys.objects O on IPS.object_id = O.object_id
join sys.schemas as s ON s.schema_id = O.schema_id
join sys.indexes I on IPS.object_id = I.object_id and IPS.index_id = I.index_id
WHERE D.state not in (1) and avg_fragmentation_in_percent > 5.0 AND IPS.index_id > 0
and page_count>1000
'
exec (@command)
FETCH NEXT FROM database_list
INTO @databaseid, @databasename
END;
-- Close and deallocate the cursor.
CLOSE database_list;
DEALLOCATE database_list;
--part 3: fix the fragmentation with rebuild or reorganize according to the threshold
SET NOCOUNT ON;
DECLARE @historyid int;
DECLARE @command varchar(8000);
DECLARE @command1 varchar(7950);
DECLARE @command2 varchar(50);
DECLARE @databaseid int;
DECLARE @databasename sysname;
DECLARE @schemaid int;
DECLARE @schemaname sysname;
DECLARE @objectid int;
DECLARE @objectname sysname;
DECLARE @indexid int;
DECLARE @indexname sysname;
DECLARE @partitionnumber bigint;
DECLARE @frag_before float;
DECLARE @frag_after float;
DECLARE @alterstart datetime;
DECLARE @alterend datetime;
DECLARE @progress datetime;
set @progress = getdate()
DECLARE fragmentation_list CURSOR FOR
SELECT history_id, database_id, database_name, schema_id, schema_name, object_id, object_name, index_id, index_name, partition_number, avg_fragmentation_in_percent_before
FROM SQLAdmin.dbo.fragmentation_history
where progress is null;
-- Open the cursor.
OPEN fragmentation_list;
-- Loop through the partitions.
FETCH NEXT
FROM fragmentation_list
INTO @historyid, @databaseid, @databasename, @schemaid, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnumber, @frag_before;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- 25 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag_before < 25.0
BEGIN;
set @command1 = 'use ' + @databasename + ';'
set @command1 = @command1 + '
DECLARE @partitioncount bigint;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = ' + convert(varchar(10), @objectid) + ' AND index_id = ' + convert(varchar(10), @indexid) + ';
IF @partitioncount > 1
ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE' + ' PARTITION=' + CONVERT (CHAR, @partitionnumber) + ';
else
ALTER INDEX [' + @indexname + '] ON ' + @schemaname + '.[' + @objectname + '] REORGANIZE' + ';
UPDATE STATISTICS ' + @schemaname + '.[' + @objectname + '] [' + @indexname + '];
'
set @command = @command1
END;
IF @frag_before >= 25.0
BEGIN;
set @command1 = 'use ' + @databasename + ';'
set @command1 = @command1 + '
DECLARE @partitioncount bigint;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = ' + convert(varchar(10), @objectid) + ' AND index_id = ' + convert(varchar(10), @indexid) + ';
IF @partitioncount > 1
ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD' + ' PARTITION=' + CONVERT (CHAR, @partitionnumber) + '
else
ALTER INDEX [' + @indexname +'] ON ' + @schemaname + '.[' + @objectname + '] REBUILD' + '
'
set @command2 = 'with (online=ON)'
set @command = @command1 + ' ' + @command2
END;
begin try
set @alterstart = getdate()
EXEC (@command);
set @alterend = getdate()
select @frag_after = avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(@databaseid, @objectid, @indexid, @partitionnumber, 'LIMITED')
update SQLAdmin.dbo.fragmentation_history
set alter_start = @alterstart,
alter_end = @alterend,
avg_fragmentation_in_percent_after = @frag_after,
progress = @progress
where history_id = @historyid
end try
begin catch
if error_number() = 2275 or error_number() = 153
begin
set @alterstart = getdate()
EXEC (@command1);
set @alterend = getdate()
select @frag_after = avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(@databaseid, @objectid, @indexid, @partitionnumber, 'LIMITED')
update SQLAdmin.dbo.fragmentation_history
set alter_start = @alterstart,
alter_end = @alterend,
avg_fragmentation_in_percent_after = @frag_after,
progress = @progress
where history_id = @historyid
end
--select @command command, @databasename database_name, @schemaname schema_name, @objectname object_name, @indexname index_name, @partitionnumber partition_number,
-- error_number() error_number , ERROR_SEVERITY() error_severity, ERROR_STATE() error_state, ERROR_LINE() error_line, ERROR_MESSAGE() error_message
exec p_error_handling @command
end catch
FETCH NEXT
FROM fragmentation_list
INTO @historyid, @databaseid, @databasename, @schemaid, @schemaname, @objectid, @objectname, @indexid, @indexname, @partitionnumber, @frag_before;
END;
-- Close and deallocate the cursor.
CLOSE fragmentation_list;
DEALLOCATE fragmentation_list;
--part 4: get the most expensive index maintenance by duration
select datediff(ms, alter_start, alter_end) duration_ms, alter_start, alter_end,
database_name, schema_name, object_name, index_name, avg_fragmentation_in_percent_before,
avg_fragmentation_in_percent_after from SQLAdmin.dbo.fragmentation_history
order by duration_ms desc
Comments
- Anonymous
May 01, 2013
Hey Batuhan, you can use AdaptiveIndexDefrag to solve fragmentation and statistics maintenance: blogs.msdn.com/.../adaptive-index-defrag.aspxCheers! - Anonymous
July 19, 2014
Thanks for ur Artical it's going to be useful for sql server performance tuning with index column