January 23, 2015 at 1:50 pm
Hello All,
I’m trying to defrag databases for maintenance purposes. I’m using a script to identify the databases/tables where fragmentation is high. The script captures avg_fragmentation_in_percent greater than 50%.
I’m using the following script to achieve this (I found it out on the web – it works pretty good):
DECLARE @command VARCHAR(5000)
-- Create a temporary table
create table #Index_Fragmentation (
database_name varchar (100),
Eschema varchar(100),
xTable varchar (200),
xIndex varchar (max),
avg_fragmentation_percent int,
page_count int
)
--load the @command variable with the query
SELECT @command = 'Use [' + '?' + ']
SELECT db_name(database_id) db_Name,
dbschemas.[name] as ESchema,
dbtables.[name] as xTable,
dbindexes.[name] as xIndex,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc'
-- insert data into temporary table
insert into #Index_Fragmentation
EXEC sp_MSForEachDB @command --executes against all databases in current server\instance
-- here you can change the query for your needs
select * from #Index_Fragmentation
where database_name not in ('msdb','master')
and avg_fragmentation_percent > 50
order by database_name, xTable, avg_fragmentation_percent desc;
-- clean up
drop table #Index_Fragmentation;
Here is my output:
(SEE ATTACHMENT - I couldn't figure out how to insert an image)
Notice the avg_fragmentation_percent column numbers are all over 50%. I want to reduce that number to under 50%.
The first option I used is Ola Hallengren’s tool for index maintenance. Based on the information from that site for the various parameters, here’s what I’m running:
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE dbo.IndexOptimize @databases = 'DATA_COLLECTION', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REORGANIZE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @Indexes = 'ALL_INDEXES', @UpdateStatistics = 'ALL', @LogToTable = 'Y'" -b
I run the script in a SQL Agent job. It succeeds without any errors. See output:
Message
Executed as user: ITSERVICES\M00339. Date and time: 2015-01-21 16:05:52 Server: WIWAUK4SMSTST02 Version: 11.0.5548.0 Edition: Standard Edition (64-bit) Procedure: [master].[dbo].[IndexOptimize] Parameters: @databases = 'DATA_COLLECTION', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REORGANIZE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = 'ALL_INDEXES', @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y' Source: https://ola.hallengren.comDate and time: 2015-01-21 16:05:52 Database: [Data_Collection] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[CommandLog] [PK_CommandLog] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52 Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[DISKSPACE] [_WA_Sys_00000002_0F975522] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52 Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[DISKSPACE] [_WA_Sys_00000003_0F975522] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52 Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[Personnel_Assignments] [PK__Personne__9CCF25170CBAE877] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52 Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[SQLImportHistory] [PK__SQLImpor__3214EC2708EA5793] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[sqlLogins] [_WA_Sys_00000001_24927208] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[tempsqlLogins] [_WA_Sys_00000001_29572725] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[tempsqlLoginsS] [_WA_Sys_00000001_20C1E124] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52 Date and time: 2015-01-21 16:05:52 Command: UPDATE STATISTICS [Data_Collection].[dbo].[tempsqlLoginsS] [_WA_Sys_00000002_20C1E124] Outcome: Succeeded Duration: 00:00:00 Date and time: 2015-01-21 16:05:52 Date and time: 2015-01-21 16:05:53. Process Exit Code 0. The step succeeded.
When I run the above script again to query for fragmentation, I get the same results for the database (DATA_COLLECTION) in row 1 of the output (I realize that the command only runs against the first database)
The other 2 options I tried were creating a Maintenance Plan and just running the ALTER INDEX …Rebuild / Reorganize and still no change in the output.
Is there something I need to do differently to reduce the avg_fragmentation_in_percent?
Is it not reducing because the xIndex column for that database is NULL?
From what I’ve read, avg_fragmentation_in_percent represents the HEAP and in one article, it mentioned creating a clustered_index which can't be the solution that has to be performed for each table.
Sorry for information overload but I wanted to give a full picture.
Thanks in advance!!
Ronnie
January 23, 2015 at 2:05 pm
Most of your tables have 1k pages or below. You can't get rid of fragmentation on small tables like that. In fact, you should not put too much effort on defrag those. Even Ola's job takes this into consideration, skipping by default (if I'm not mistaken) tables with less than 1k pages.
Focus your attention on tables with 50k pages or more.
January 23, 2015 at 2:27 pm
I personally think 50K pages is a bit high, but agree with the principal that you should ignore small tables.
And you should definitely switch to Ola.hallengren.com's stuff for doing all of your database mx tasks!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 23, 2015 at 4:26 pm
Thank you guys for your reply!
So, you're saying that I shouldn't worry about the avg_fragmentation_in_percent number in my result set? I'm trying to setup routine maintenance and just want to make sure that it's working. I've read a few article that said f the avg_fragmentation_in_percent was over 50, then I should defrag.
Thanks,
January 23, 2015 at 4:56 pm
I think what everyone is saying is try not to reinvent the wheel with your own specific jobs. Ola's maintenance scripts work really well, are configurable and are updated as needed. You can set the min number of pages (1000 by default) and at which point you defrag vs rebuild the index.
January 23, 2015 at 9:12 pm
Ronnie Jones (1/23/2015)
Thank you guys for your reply!So, you're saying that I shouldn't worry about the avg_fragmentation_in_percent number in my result set? I'm trying to setup routine maintenance and just want to make sure that it's working. I've read a few article that said f the avg_fragmentation_in_percent was over 50, then I should defrag.
Thanks,
50% is too high to wait for a defrag IMNSHO. And you should ignore smaller tables.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 26, 2015 at 9:47 am
Thank you Kevin!!
I think I need to sharpen my skill on really understanding fragmentation...
Ronnie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply