Archive for September, 2012

Defragmenting Databases in MS SQL

So I’ve been busy building an enterprise intelligence gathering application that uses MS SQL on the back-end.  The database has many tables and views.  One of the tables acts as storage for global log entries, to be able to see what user did what while using the software (for accountability purposes).  The problem I’ve found with tables that have a lot of I/O is eventually, database fragmentation occurs.  You’re probably already aware that you can query the fragmentation percentages using the built-in stored procedure sys.dm_db_index_physical_stats.  Essentially, what I did was to create a stored procedure, which I could later call as part of a maintenance task to defragment the database.

There are basically 2 ways to defragment a table.  You can do an offline rebuild of the indexes using “alter index all on ‘<your tablename>’ rebuild”, which is recommended for moderate to high fragmentation – or – you can do an online reorganization of the indexes using “alter index all on ‘<your tablename>’ reorganize, which is recommended for tables with low fragmentation.  40% or higher is considered moderate fragmentation.  Obviously, to do an offline rebuild, you will need all users to be not accessing the tables being deframented.

For a complete comparison of rebuilding vs. reorganizing see http://technet.microsoft.com/en-us/library/ms188388.aspx.

Below is the procedure I used defragment tables in my application.  Where ‘<your database>’, you should insert your own database name:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- Author:		Chris Brunner
-- Create date: 06/07/2012
-- Description:	Defragments the database by
-- checking the statistics for each table and
-- either reorganizing the indices or doing an
-- offline rebuild of the indices, depending on
-- the fragmentation percentage. A rebuild will
-- occur on any table with fragmentation &gt; 40%.
-- Otherwise, the indices are reorganized instead.
--
-- WARNING: An offline rebuild will lock the table
-- during processing. This will prevent modifications
-- to the table until processing is complete.
-- It is recommended that this procedure only be
-- executed when there are no users accessing the
-- database. Conversely, reorganizing the indices
-- does *not* require table locking. 
-- =============================================
create procedure [dbo].[DefragDatabase] 
as
begin
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	set nocount on;
 
        -- drop our temporary table if it already exists.
	if exists(select [name] from tempdb.dbo.sysobjects where xtype = 'U' and [name] like '#frag%')
	begin
	   drop table #frag
	end
 
	-- create new temp table to store our results in.
	-- get the table name and fragmentation percentage from the physical index stats
	-- system view and store them in the temp table.
	create table #frag(tableName varchar(200), p int)
	insert into #frag
	select 'dbo.' + object_name(object_id) as table_name, avg_fragmentation_in_percent
	from sys.dm_db_index_physical_stats
	(db_id(N'[YOUR DATABASE NAME]'), null, null, null, 'sampled')
        order by avg_fragmentation_in_percent desc
 
        declare @name as varchar(200)
        declare @sql varchar(1000)
        declare @percent as int
 
        -- go through each result and check fragmentation.
        while exists(select top 1 tableName from #frag)
        begin
		-- for tables that have less than moderate fragmentation, we just reorganize
		-- them.  Otherwise, we do an offline rebuild of the indexes. We consider
		-- "moderate" fragmentation to be 40% or higher.
		select top 1 @name = tableName, @percent = p from #frag
		if (@percent > 40)
		begin
		   set @sql = 'alter index all on ' + @name + ' rebuild'
		end
		else
		begin
		   set @sql = 'alter index all on ' + @name + ' reorganize'
		end
 
		-- delete each result from the table as we go. This is a dequeuing mechanism.
		-- then execute the rebuild or reorganize command.
		select @sql
		delete from #frag where tableName = @name
		exec(@sql)
	end
 
	-- destroy the temporary table.
	drop table #frag
 
	-- update the index statistics.
	exec sp_updatestats
end
go
Friday, September 7th, 2012 Programming, T-SQL No Comments