7

First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on.

I have Ola's script set as

@Databases nvarchar(max)
,@FragmentationLow nvarchar(max) = null
,@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE'
,@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE'
,@FragmentationLevel1 int = 50
,@FragmentationLevel2 int = 75
,@PageCountLevel int = 400
,SortInTempdb nvarchar(max) = 'N'
,maxdop int = null
,fillfactor int = null
,PadIndex nvarchar(max) = null
,LOBCompaction nvarchar(max) = 'Y'
,UpdateStatistics nvarchar(max) = 'ALL'
,OnlyModifiedStatistics nvarchar(max) = 'Y'
,StatisticsSample int = null
,StatisticsResample nvarchar(max) = 'N'
,PartitionLevel nvarchar(max) = 'Y'
,MSShippedObjects nvarchar(max) = 'N'
,Indexes nvarchar(max) = 'MyDatabase.dbo.Table'
,TimeLimit int = null
,delay int = null
,WaitAtLowPriorityMaxDuration int = null
,WaitAtLowPriorityAbortAfterWait nvarchar(max) = null
,LockTimeout int = null
,LogToTable nvarchar(max) = 'Y'
,

execute nvarchar(max) = 'Y'

The index in question is as follows:

Page Fullness: 75.77%
Fragmentation: 99.14%
Avg. Row Size: 33
Depth: 4
Index Type: Nonclustered
Leaf-lvl rows: 11130800
Max Size: 33
Min Size: 33
Pages: 63585
Partition ID: 1

It's set so low because the 1000 mark isn't hitting the index. What's odd is that stats are being updated but the index isn't being reorganized or rebuilt.

I'm looking for things to explore or concepts to think about that will help me solve this issue.

Again, if this has been asked and answered, please forgive me and forgive me if the answer is so ridiculously easy and stupid that it's staring me in the face.

Thanks.

Table Schema

CREATE TABLE [dbo].[Table](
    [column1] [uniqueidentifier] NOT NULL,
    [column2] [uniqueidentifier] NOT NULL,
    [column3] [uniqueidentifier] NOT NULL,
    [column4] [uniqueidentifier] NOT NULL,
    [column5] [nvarchar](50) NULL,
    [column6] [nvarchar](128) NULL,
    [Column7] [money] NOT NULL,
    [column8] [money] NOT NULL,
    [column9] [nvarchar](128) NULL,
    [column10] [nvarchar](64) NULL,
    [column11] [image] NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
(
    [Column1_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_Index] ON [dbo].[Table] 
(
    [column2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
GO
  • is this standard edition? Standard Edition does not support online index rebuilds. – rottengeek Aug 12 '15 at 20:30
  • @jbkelly -- if you are doing this based on the performance team's recommendations I would look at this post from Brent Ozar on fragmentation. I have similar table structures with uniqueidentifiers as primary keys and index keys and I've found that reindexing didn't actually make any real performance improvement. Also, depending on how you are assigning values to the uniqueidentifier you might be fighting a never ending fragmentation battle as it only take a few hundred inserts with newid to get over 70% fragmentation – Aaron Aug 12 '15 at 20:32
  • Enterprise. Some indexes rebuild and some don't. – jbkelly Aug 12 '15 at 20:33
  • @Aaron, that's kind of what I've been reading but that's another battle. I guess I'm just really curious as to why other indexes get rebuilt through the scripts and some don't. It's more about knowing why more than anything else at this point. – jbkelly Aug 12 '15 at 20:34
9

Ah! It's the image column.

[column11] [image] NULL,

Online only works on tables without blobs.

Guidelines for Performing Online Index Operations

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.