This is a follow up post to : Why SQL Fragmentation Remains High
As mentioned in this previous post, our dilemma was we were rebuilding every index on a nightly basis, and :
- the process proves to be very resource intensive
- we don’t see the benefit. The fragmentation for some of the indexes remain high.
After some digging, this is what we found:
Rebuild the index only if there will be at least 1000 pages affected. If < 1000 pages will be affected, fragmentation will not really be affected and potentially would remain high.
This is the script that we now use to selectively identify which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone. This script uses the following criteria in determining which action to take:
- current fragmentation %
- number of pages used by the index
The current logic is:
- reorganize index : if fragmentation is > 10 % but < 30% and number of pages > 1000
- rebuild index : if fragmentation is > 30 and number of pages > 1000
This script is adapted from “Rebuild or reorganize indexes (with configuration)” from MSDN Books Online
(http://msdn.microsoft.com/en-us/library/ms188917.aspx)”
1: -- http://www.sqlmusings.com
2: -- Ensure a USE <databasename> statement has been executed first.
3: SET NOCOUNT ON
4:
5: -- adapted from "Rebuild or reorganize indexes (with configuration)" from MSDN Books Online
6: -- (http://msdn.microsoft.com/en-us/library/ms188917.aspx)
7:
8: -- =======================================================
9: -- || Configuration variables:
10: -- || - 10 is an arbitrary decision point at which to
11: -- || reorganize indexes.
12: -- || - 30 is an arbitrary decision point at which to
13: -- || switch from reorganizing, to rebuilding.
14: -- || - 0 is the default fill factor. Set this to a
15: -- || a value from 1 to 99, if needed.
16: -- =======================================================
17: DECLARE @reorg_frag_thresh float SET @reorg_frag_thresh = 10.0
18: DECLARE @rebuild_frag_thresh float SET @rebuild_frag_thresh = 30.0
19: DECLARE @fill_factor tinyint SET @fill_factor = 80
20: DECLARE @report_only bit SET @report_only = 1
21:
22: -- added (DS) : page_count_thresh is used to check how many pages the current table uses
23: DECLARE @page_count_thresh smallint SET @page_count_thresh = 1000
24:
25: -- Variables required for processing.
26: DECLARE @objectid int
27: DECLARE @indexid int
28: DECLARE @partitioncount bigint
29: DECLARE @schemaname nvarchar(130)
30: DECLARE @objectname nvarchar(130)
31: DECLARE @indexname nvarchar(130)
32: DECLARE @partitionnum bigint
33: DECLARE @partitions bigint
34: DECLARE @frag float
35: DECLARE @page_count int
36: DECLARE @command nvarchar(4000)
37: DECLARE @intentions nvarchar(4000)
38: DECLARE @table_var TABLE(
39: objectid int,
40: indexid int,
41: partitionnum int,
42: frag float,
43: page_count int
44: )
45:
46: -- Conditionally select tables and indexes from the
47: -- sys.dm_db_index_physical_stats function and
48: -- convert object and index IDs to names.
49: INSERT INTO
50: @table_var
51: SELECT
52: [object_id] AS objectid,
53: [index_id] AS indexid,
54: [partition_number] AS partitionnum,
55: [avg_fragmentation_in_percent] AS frag,
56: [page_count] AS page_count
57: FROM
58: sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
59: WHERE
60: [avg_fragmentation_in_percent] > @reorg_frag_thresh
61: AND
62: page_count > @page_count_thresh
63: AND
64: index_id > 0
65:
66:
67: -- Declare the cursor for the list of partitions to be processed.
68: DECLARE partitions CURSOR FOR
69: SELECT * FROM @table_var
70:
71: -- Open the cursor.
72: OPEN partitions
73:
74: -- Loop through the partitions.
75: WHILE (1=1) BEGIN
76: FETCH NEXT
77: FROM partitions
78: INTO @objectid, @indexid, @partitionnum, @frag, @page_count
79:
80: IF @@FETCH_STATUS < 0 BREAK
81:
82: SELECT
83: @objectname = QUOTENAME(o.[name]),
84: @schemaname = QUOTENAME(s.[name])
85: FROM
86: sys.objects AS o WITH (NOLOCK)
87: JOIN sys.schemas as s WITH (NOLOCK)
88: ON s.[schema_id] = o.[schema_id]
89: WHERE
90: o.[object_id] = @objectid
91:
92: SELECT
93: @indexname = QUOTENAME([name])
94: FROM
95: sys.indexes WITH (NOLOCK)
96: WHERE
97: [object_id] = @objectid AND
98: [index_id] = @indexid
99:
100: SELECT
101: @partitioncount = count (*)
102: FROM
103: sys.partitions WITH (NOLOCK)
104: WHERE
105: [object_id] = @objectid AND
106: [index_id] = @indexid
107:
108: -- Build the required statement dynamically based on options and index stats.
109: SET @intentions =
110: @schemaname + N'.' +
111: @objectname + N'.' +
112: @indexname + N':' + CHAR(13) + CHAR(10)
113: SET @intentions =
114: REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
115: @intentions
116: SET @intentions = @intentions +
117: N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
118: N' PAGE COUNT: ' + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10)
119:
120: IF @frag < @rebuild_frag_thresh BEGIN
121: SET @intentions = @intentions +
122: N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
123: SET @command =
124: N'ALTER INDEX ' + @indexname +
125: N' ON ' + @schemaname + N'.' + @objectname +
126: N' REORGANIZE; ' +
127: N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname +
128: N' ' + @indexname + ';'
129:
130: END
131: IF @frag >= @rebuild_frag_thresh BEGIN
132: SET @intentions = @intentions +
133: N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
134: SET @command =
135: N'ALTER INDEX ' + @indexname +
136: N' ON ' + @schemaname + N'.' + @objectname +
137: N' REBUILD'
138: END
139: IF @partitioncount > 1 BEGIN
140: SET @intentions = @intentions +
141: N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
142: SET @command = @command +
143: N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
144: END
145: IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
146: SET @intentions = @intentions +
147: N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
148: SET @command = @command +
149: N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
150: END
151:
152: -- Execute determined operation, or report intentions
153: IF @report_only = 0 BEGIN
154: SET @intentions = @intentions + N' EXECUTING: ' + @command
155: PRINT @intentions
156: EXEC (@command)
157: END ELSE BEGIN
158: PRINT @intentions
159: END
160: PRINT @command
161:
162: END
163:
164: -- Close and deallocate the cursor.
165: CLOSE partitions
166: DEALLOCATE partitions
167:
168: GO
- Tweet
Gives an error when you try and run the script:
Msg 102, Level 15, State 1, Line 58
Incorrect syntax near ‘(‘.
sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)
@Tom what version of SQL Server and Service Pack are you running this in?
also make sure that LIMITED is enclosed in single quotes. sometimes when you paste this script to Word, the character for single quote changes. You may need to retype it in another editor or in SSMS.
Hi, sql 2005 sp3 developer version. Its loading the script via the link and appears to be the same as your code.
Ah, my db was set to compatibility level 80, changed it to 90 and it works fine now. Thanks for the code.
great!
If the fragmentation is > 30 would it be acceptable to do a reorganize over a rebuild? The reason I ask is I’m not running Enterprise edition and as such couldn’t do a rebuild with the online option set to on…I’m worried about the table (which is rather large) being inaccessible while the rebuild was occurring….
Hi Dave,
How much fragmentation do you have? How big is your table? A reorganize should be ok in a more regular maintenance schedule (ex every couple days, every week), but you will need to build into your schedule a full rebuild at some point (once a month, once every couple of months depending on your fragmentation). Unfortunately this will mean some downtime for you.
I went back and did a check, and the bigger tables actually have relatively small levels of fragmentation (5% on 388k pages) and the higher levels of fragmentation (> 30%) span less than 10 pages so I’m probably okay…
But I should definitely perform a rebuild periodically? If I perform a reorganize every few days or even once a week, will a rebuild make a noticeable performance difference?
Yes I will recommend rebuilding your indexes periodically.
For reorganizing, you will see a performance gain when the fragmentation is higher (ie reorganizing a 5% fragmented table, and reorganizing a 40% fragmented tables).
Paul Randal wrote an excellent article, coupled with excellent blog posts, on tradeoffs of reorganizing and rebuilding indexes, I am sure you will find it invaluable:
Top Tips for Effective Database Maintenance
http://technet.microsoft.com/en-us/magazine/cc671165.aspx
Thanks for the link! I’ve read through his article (and followed each of the links he provided as well) and found it extremely informative…
So it isn’t recommended to reorganize or rebuild indexes that are below 1000 pages no matter their fragmentation level?
Thanks for all your help!!
Great article, Belle !!! Btw, what text editor are you using for sql files, the look and feel is good.
Great article and script.. Belle…
Btw what text editor did use over here for sql files, the look is good!!!
Thanks Tarun. I am using WP-Syntax, a wordpress plugin
The script concatenates PARTITION= to the reorganize/rebuild commands. Don’t you think the script would throw an error in case of ALTER INDEX.. REORGANIZE; because the string is already terminated by semi-colon (;)??? And if we try to concatenate partition = to this??
Infact partition= would be concatenated after the update statistics..? It would throw an error rt.??
Very nice script, Belle. Thanks for sharing!
Great script!! – fillfactor in this script means that once the rebuild index operation performed will it set all the indexes to 80% fillfactor from original?
Thanks
John
yes it should
Belle, thank you so much for this wonderful script. For a newbie DBA like me it has proven invaluable already. The last person responsible for databases had set up maintenance jobs to reorganize AND rebuild all indexes in all databases every night; ouch! Although the server is small (in comparison to some) this maintenance plan was taking almost 3.5 hours to run. Your script reduced it to less than 20 minutes, and now is run once per week. I was wondering if you are planning on extending this script in the future? As I said I’m a newbie, only started into the DBA world a few months back so am still learning. I have been reading information on LOB, Online rebuilding (Enterprise edition) and such and wondering if things like that need to be in this script or if the default variables on ALTER INDEX will cover it? Thanks again, and cheers!
I have been using this code for quite some time. It is great. Thanks! I qwas wondering if this could be modified to scroll through all user databases on a server and perform the indexing instead of only on a prescribed database?
Thanks!
How do I apply this script to run on all databases on the server?
Hi the error
“Gives an error when you try and run the script:
Msg 102, Level 15, State 1, Line 58
Incorrect syntax near ‘(’.”
is because you have a SQL 2005 with DB in compatibility 80 you can fix this error just declar 1 more variable @DB_ID smallint and set @DB_ID=DB_ID()
DECLARE @DB_ID smallint
DECLARE @table_var TABLE(
objectid int,
indexid int,
partitionnum int,
frag float,
page_count int
)
— Conditionally select tables and indexes from the
— sys.dm_db_index_physical_stats function and
— convert object and index IDs to names.
set @DB_ID = DB_ID()
INSERT INTO @table_var
SELECT [object_id] AS objectid,
[index_id] AS indexid,
[partition_number] AS partitionnum,
[avg_fragmentation_in_percent] AS frag,
[page_count] AS page_count
FROM sys.dm_db_index_physical_stats (@DB_ID,NULL, NULL , NULL, ‘LIMITED’)
WHERE [avg_fragmentation_in_percent] > @reorg_frag_thresh
AND page_count > @page_count_thresh
AND index_id > 0
in addition to Tarun Rodrigues’s post (#14) – about statistics – which is correct I want to add about rebuilding partitioned indexes –
FILLFACTOR is NOT valid (http://msdn.microsoft.com/en-us/library/ms188388.aspx)- thus it has to be omitted for partitioned indexes
my resulting code for generating command line is below
IF @frag = @rebuild_frag_thresh BEGIN
SET @intentions = @intentions +
N’ OPERATION: REBUILD’ + CHAR(13) + CHAR(10)
SET @command =
N’ALTER INDEX ‘ + @indexname +
N’ ON ‘ + @schemaname + N’.’ + @objectname +
N’ REBUILD’
END
IF @partitioncount > 1 BEGIN
SET @intentions = @intentions +
N’ PARTITION: ‘ + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
SET @command = @command +
N’ PARTITION=’ + CAST(@partitionnum AS nvarchar(10))
END
ELSE BEGIN
IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
SET @intentions = @intentions +
N’ FILL FACTOR: ‘ + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
SET @command = @command +
N’ WITH (FILLFACTOR = ‘ + CAST(@fill_factor AS nvarchar) + ‘)’
END