Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Urgent :Index Rebuild Job not working as Expected Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 4:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:55 AM
Points: 64, Visits: 427
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...


  Post Attachments 
Index_fragmentation_Issue.xlsx (1 view, 11.75 KB)
Post #1566289
Posted Wednesday, April 30, 2014 5:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 14,034, Visits: 28,406
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566312
Posted Wednesday, April 30, 2014 6:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:55 AM
Points: 64, Visits: 427
Hi Grant,

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

Cheers!!!


Post #1566342
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse