April 30, 2014 at 4:22 am
Dear All,
Problem Statement 1 :I have been using ola hallengren's script on the below link for index maintenance. HOwever I have noticed that its not working as expected(Noticed this after a long time though after the performance was degraded 🙁 ).
I have a number of fragmented indexes which are not getting rebuilt or reorganized inspite of providing the correct parameters
My job script was as below :
EXECUTE dbo.IndexOptimize
@databases = 'MY_DB_Name',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 20,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
I have attached a detailed report of the fragmentation levels before and after the script was run and there was no difference except that only one table was taken care of and this has been happening since a long time.
I have also verified the row count for the tables are more than 1000 in most of the cases... 🙁
Is there anything wrong that I am doing?
Problem statement 2 : For the above problem, I created a maintenance plan(Rebuild offline) for rebuilding all indexes and the result was better than that previous one(The one with Ola hallengrens script), however, there were still some indexes which were left over. I ran the maintenance Plan over and over again 🙂 , however, they still remain the way they are..
I have attached the list for the maintenance Plan before and after report as well.
Can someone please tell me if this is a default behaviour of both these approaches to skip some fragmented indexes? If yes then on what filter criteria?
Thanks in advance...
April 30, 2014 at 5:08 am
You've conflated rows with pages. The number of rows doesn't really matter. It's the number of pages that affects how index defragmentation occurs within the index. If the index is less than 8 pages in size, it will never defrag. A page can hold 8k of data, so that's a minimum of 64k you have to have before a defrag can occur (which may be, depending on the data, well over 1000 rows). But, for anything less than 100 pages, I wouldn't bother defragging. Many people say anything less than 1000 pages in size you shouldn't mess with. I'm pretty sure Ola's script uses the 1000 pages as a default setting. So if your indexes are less than 1000 pages, it's just not bothering to defragment them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 30, 2014 at 6:14 am
Hi Grant,
Thanks a Ton for pointing that out!!! My bad... 🙁
Cheers!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply