SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Urgent :Index Rebuild Job not working as Expected


Urgent :Index Rebuild Job not working as Expected

Author
Message
rollercoaster43
rollercoaster43
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 435
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 Sad ).
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... Sad
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 Smile , 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...
Attachments
Index_fragmentation_Issue.xlsx (13 views, 11.00 KB)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98479 Visits: 33014
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
rollercoaster43
rollercoaster43
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 435
Hi Grant,

Thanks a Ton for pointing that out!!! My bad... Sad

Cheers!!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search