asked on

rebuild index for selected table using existing index rebuild script

hi,

We are using SQL server 2016 STANARD and we have index always defrag a lot daily, like 88% everyday, and we are using index rebuild script like:

  
-- Ensure a USE  statement has been executed first. 
SET NOCOUNT ON; 
DECLARE @objectid int; 
DECLARE @indexid int; 
DECLARE @partitioncount bigint; 
DECLARE @schemaname nvarchar(130); 
DECLARE @objectname nvarchar(130); 
DECLARE @indexname nvarchar(130); 
DECLARE @partitionnum bigint; 
DECLARE @partitions bigint; 
DECLARE @frag float; 
DECLARE @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names. 
SELECT 
    object_id AS objectid, 
    index_id AS indexid, 
    partition_number AS partitionnum, 
    avg_fragmentation_in_percent AS frag 
INTO #work_to_do 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') 
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; 
-- Declare the cursor for the list of partitions to be processed. 
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; 
-- Open the cursor. 
OPEN partitions; 
-- Loop through the partitions. 
WHILE (1=1) 
    BEGIN; 
        FETCH NEXT 
           FROM partitions 
           INTO @objectid, @indexid, @partitionnum, @frag; 
        IF @@FETCH_STATUS < 0 BREAK; 
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) 
        FROM sys.objects AS o 
        JOIN sys.schemas as s ON s.schema_id = o.schema_id 
        WHERE o.object_id = @objectid; 
        SELECT @indexname = QUOTENAME(name) 
        FROM sys.indexes 
        WHERE  object_id = @objectid AND index_id = @indexid; 
        SELECT @partitioncount = count (*) 
        FROM sys.partitions 
        WHERE object_id = @objectid AND index_id = @indexid; 
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. 
        IF @frag < 20.0 
            SET @command = N'SET QUOTED_IDENTIFIER ON; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
        IF @frag >= 20.0 
            SET @command = N'SET QUOTED_IDENTIFIER ON; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
        IF @partitioncount > 1 
                    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); 
                            EXEC (@command); 
        --PRINT N'Executed: ' + @command; 
    END; 
-- Close and deallocate the cursor. 
CLOSE partitions; 
DEALLOCATE partitions; 
-- Drop the temporary table. 
DROP TABLE #work_to_do; 
GO

Open in new window

how to make this script check the table name and if it sees that tables name , build the index of that table completely?

ste5an

When the table defrags *daily* to that extend, then you need to review the reasons behind first. And also evaluate the use of rebuilding the index.

Cause sometimes defragmented indices are better left untouched.

Jim Horn

ste5an is correct. Just for kicks and giggles, script out the CREATE TABLE statement, with the index, and give us a demo of how this table is populated every day.

marrowyung

ASKER

ste5an,

When the table defrags *daily* to that extend, then you need to review the reasons behind first.

you are right but it is just a lot data is going in these 2 x tables everyday and we have to make sure index are good everyday.

the index rebuild job runs everyday but recently seems only out of order for these tables.
It is just like in less than 1 hour the avg fragmentation in percent of that tables can go 50 %, and use will complain.

And also evaluate the use of rebuilding the index.

you mean drop unused index after review ?

Priyanka ,

, it’s important to know how to fix SQL Server index fragmentation if performance is suffering.

we only rebuild the index of these 2 x tables when user complain.

my script about has condition for that:

IF @frag < 20.0 
            SET @command = N'SET QUOTED_IDENTIFIER ON; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; 
        IF @frag >= 20.0 
            SET @command = N'SET QUOTED_IDENTIFIER ON; ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; 
        IF @partitioncount > 1 
                    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); 
                            EXEC (@command);

Open in new window

ste5an

It is just like in less than 1 hour the avg fragmentation in percent of that tables can go 50 %, and users will complain.

This is an indicator for bad design.

Review the data model and the implementation.
Review the processes.

Without knowing anything about this, I would consider a fixed job for rebuilding the index of that table without testing the fragmentation before.

marrowyung

ASKER

I would consider a fixed job for rebuilding the index of that table without testing the fragmentation before.

you mean only for that table ?

ste5an

Sure.

marrowyung

ASKER

this is what we are also doing, no other choice man..

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

does it need partition to avoid that?

so partition can help on index fragmentation ?
the tables has more than 50000 rows and can consider data partition.

Can you use filtered indices?

it has a none clustered columnstore index. and it seems it is the columnstore index has this problem, I am thinking about drop that.

that index is suggested by the DTA.

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

Without knowing anything about the process behind and the query pattern, you cannot trust the DTA.

sure, someone suggest me to use it and I start not trusting that now.

Why does it fragment? I would really look into the clustered index first.

I think I should drop that columnstore index, today is normal, fragmentation is normal, 7x % for the whole day.

would really look into the clustered index first.

clustered index is just int and one column only and is the PK.

ste5an

Okay. Post the table DDL and index scripts.

marrowyung

ASKER

DDL


CREATE TABLE [dbo].[PendingOrders](
   [PlannedOrderID] [int] NOT NULL,
   [PendingOrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [OrderID] [int] NULL,
   [PatientID] [int] NOT NULL,
   [IssueUserID] [int] NOT NULL,
   [IssueDate] [datetime] NOT NULL,
   [OrderCategoryID] [smallint] NULL,
   [Continuous] [bit] NOT NULL,
   [Comments] [nvarchar](1000) NULL,
   [Warning] [bit] NOT NULL,
   [Route] [smallint] NULL,
   [PrevPendingID] [int] NULL,
   [Status] [smallint] NOT NULL,
   [GivenInPerKg] [smallint] NULL,
   [PositionInSet] [smallint] NULL,
   [PumpID] [int] NULL,
   [RowOrderID] [int] NULL,
   [LinkToOpenOrderID] [int] NULL,
   [PatientWeight] [decimal](38, 14) NULL,
   [LocationID] [smallint] NULL,
   [CallerID] [uniqueidentifier] NULL,
   [AttachedFormID] [uniqueidentifier] NULL,
   [TS] [timestamp] NULL,
   [Completion_status] [tinyint] NULL,
   [CancelUserID] [int] NULL,
   [CancelDate] [datetime] NULL,
   [ConfirmSessionID] [int] NULL,
   [ActionType] [smallint] NOT NULL,
   [CancelledOrderID] [int] NULL,
   [ValidationCoSign] [int] NULL,
   [ValidationCoSignTime] [datetime] NULL,
   [ValidationUserID] [int] NULL,
   [ValidationTime] [datetime] NULL,
   [CancelReason] [smallint] NULL,
   [ReplacePendingID] [int] NULL,
   [Duration] [decimal](38, 14) NULL,
   [DurationUnitID] [smallint] NULL,
   [TotalVolume] [decimal](38, 14) NULL,
   [TotalVolumeUnitID] [smallint] NULL,
   [UserLockIndex] [tinyint] NULL,
   [LocksValue] [int] NULL,
   [IvLineVolume] [decimal](38, 14) NULL,
   [IsLinkedToPump] [bit] NULL,
   [RateExcluded] [bit] NULL,
   [History] [int] NOT NULL,
   [OrignalTotalSolutionRate] [decimal](38, 14) NULL,
   [AllergySecondSignature] [int] NULL,
   [SecondApprovalSignature] [int] NULL,
   [WasExported] [smallint] NULL,
   [OrderRecordTS] [bigint] NOT NULL,
   [PlannedHistoryID] [int] NULL,
   [SecondApprovalDate] [datetime] NULL,
   [MiscID] [int] NULL,
   [LinkedToPendingID] [int] NULL,
   [OriginalPendingID] [int] NULL,
   [PatientWeightSignalDate] [datetime] NULL,
   [DailyTotalVolume] [decimal](38, 14) NULL,
   [DailyTotalVolumeUnitID] [int] NULL,
   [DailyTotalVolumeUnitOfMessure] [tinyint] NULL,
   [DailyTotalVolumeMode] [bit] NULL,
   [PatientBSA] [decimal](38, 14) NULL,
   [PrevPatientBSA] [decimal](38, 14) NULL,
   [OrderingStyle] [smallint] NOT NULL,
   [DoseFormID] [smallint] NULL,
   [TemplateID] [int] NULL,
   [TotalSolutionDripUnitID] [smallint] NULL,
   [PreparationStatus] [tinyint] NOT NULL,
   [PreparationCoSign] [int] NULL,
   [PreparationCoSignTime] [datetime] NULL,
   [Dose] [decimal](38, 14) NULL,
   [DoseDripUnitID] [smallint] NULL,
   [AdministrationDetailsId] [int] NULL,
   [LockerTemplateId] [smallint] NULL,
   [LockerTemplateState] [nvarchar](max) NULL,
   [OrderActionId] [int] NULL,
   [DoseGuid] [uniqueidentifier] NOT NULL,
   [UpdatedSectionId] [int] NULL,
   [DoseUpperRange] [decimal](38, 14) NULL,
   [DurationUpperRange] [decimal](38, 14) NULL,
   [OrignalTotalSolutionRateUpperRange] [decimal](38, 14) NULL,
   [DailyTotalVolumeUpperRange] [decimal](38, 14) NULL,
   [TitrationMode] [bit] NULL,
   [DoseLowerRange] [decimal](38, 14) NULL,
   [DurationLowerRange] [decimal](38, 14) NULL,
   [OrignalTotalSolutionRateLowerRange] [decimal](38, 14) NULL,
   [DoseRangeDripUnitId] [smallint] NULL,
   [DurationRangeUnitId] [smallint] NULL,
   [TotalSolutionRangeDripUnitID] [smallint] NULL,
   [PatientWeightType] [smallint] NOT NULL,
 CONSTRAINT [PK_PendingOrders] PRIMARY KEY CLUSTERED 
(
   [PendingOrderID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrd_Continuous_1__11]  DEFAULT ((0)) FOR [Continuous]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrd_Warning_2__11]  DEFAULT ((0)) FOR [Warning]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrd_PrevPending1__12]  DEFAULT ((0)) FOR [PrevPendingID]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_GivenInPerKg]  DEFAULT ((0)) FOR [GivenInPerKg]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_PositionInSet]  DEFAULT ((0)) FOR [PositionInSet]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_RowOrderID]  DEFAULT (NULL) FOR [RowOrderID]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_Completion_status]  DEFAULT ((0)) FOR [Completion_status]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_ActionType]  DEFAULT ((0)) FOR [ActionType]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_CancelReson]  DEFAULT ((0)) FOR [CancelReason]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_RateExcluded]  DEFAULT ((0)) FOR [RateExcluded]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_History]  DEFAULT ((0)) FOR [History]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_WasExported]  DEFAULT ((0)) FOR [WasExported]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_OrderRecordTS]  DEFAULT ((0)) FOR [OrderRecordTS]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_PlannedHistoryID]  DEFAULT (NULL) FOR [PlannedHistoryID]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF_PendingOrders_PreparationStatus]  DEFAULT ((0)) FOR [PreparationStatus]
GO

ALTER TABLE [dbo].[PendingOrders] ADD  CONSTRAINT [DF__PendingOr__Patie__518D3740]  DEFAULT ((0)) FOR [PatientWeightType]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_DoseDripUnits] FOREIGN KEY([DoseDripUnitID])
REFERENCES [dbo].[DripUnits] ([DripUnitID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_DoseDripUnits]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_DripUnits] FOREIGN KEY([TotalSolutionDripUnitID])
REFERENCES [dbo].[DripUnits] ([DripUnitID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_DripUnits]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_DurationUnitID_Units] FOREIGN KEY([DurationUnitID])
REFERENCES [dbo].[Units] ([UnitID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_DurationUnitID_Units]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_FB_FORMS] FOREIGN KEY([AttachedFormID])
REFERENCES [dbo].[FB_FORMS] ([FormID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_FB_FORMS]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_LockerTemplateId] FOREIGN KEY([LockerTemplateId])
REFERENCES [dbo].[ORD_LocksTemplates] ([LockerTemplateID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_LockerTemplateId]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Ord_Reasons] FOREIGN KEY([MiscID])
REFERENCES [dbo].[Ord_Reasons] ([ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Ord_Reasons]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_OrderCategories] FOREIGN KEY([OrderCategoryID])
REFERENCES [dbo].[OrderCategories] ([OrderCategoryID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_OrderCategories]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_OrderEntry_LinkToOpenOrderID] FOREIGN KEY([LinkToOpenOrderID])
REFERENCES [dbo].[OrderEntry] ([OrderID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_OrderEntry_LinkToOpenOrderID]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Orders_DoseForms] FOREIGN KEY([DoseFormID])
REFERENCES [dbo].[Orders_DoseForms] ([DoseFormID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Orders_DoseForms]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Orders_Locations] FOREIGN KEY([LocationID])
REFERENCES [dbo].[Orders_Locations] ([LocationID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Orders_Locations]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Orders_Routes] FOREIGN KEY([Route])
REFERENCES [dbo].[Orders_Routes] ([RouteID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Orders_Routes]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Patients] FOREIGN KEY([PatientID])
REFERENCES [dbo].[Patients] ([PatientID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Patients]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_PlannedHistoryID] FOREIGN KEY([PlannedHistoryID])
REFERENCES [dbo].[PlannedOrdersHistory] ([PlannedHistoryID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_PlannedHistoryID]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_PlannedOrders] FOREIGN KEY([PlannedOrderID])
REFERENCES [dbo].[PlannedOrders] ([PlannedOrderID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_PlannedOrders]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_TotalVolumeUnitID_Units] FOREIGN KEY([TotalVolumeUnitID])
REFERENCES [dbo].[Units] ([UnitID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_TotalVolumeUnitID_Units]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Users_AllergySecondSignature] FOREIGN KEY([AllergySecondSignature])
REFERENCES [dbo].[Users] ([UserID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Users_AllergySecondSignature]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Users_CancelUserID] FOREIGN KEY([CancelUserID])
REFERENCES [dbo].[Users] ([UserID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Users_CancelUserID]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Users_IssueUserID] FOREIGN KEY([IssueUserID])
REFERENCES [dbo].[Users] ([UserID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Users_IssueUserID]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Users_PreparationCoSign] FOREIGN KEY([PreparationCoSign])
REFERENCES [dbo].[Users] ([UserID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Users_PreparationCoSign]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Users_SecondApprovalSignature] FOREIGN KEY([SecondApprovalSignature])
REFERENCES [dbo].[Users] ([UserID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Users_SecondApprovalSignature]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Users_ValidationCoSign] FOREIGN KEY([ValidationCoSign])
REFERENCES [dbo].[Users] ([UserID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Users_ValidationCoSign]
GO

ALTER TABLE [dbo].[PendingOrders]  WITH NOCHECK ADD  CONSTRAINT [FK_PendingOrders_Users_ValidationUserID] FOREIGN KEY([ValidationUserID])
REFERENCES [dbo].[Users] ([UserID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PendingOrders] CHECK CONSTRAINT [FK_PendingOrders_Users_ValidationUserID]
GO

Open in new window

ste5an

While it won't help you short term, you should talk with the devs. That table is for sure not properly normalized. Too many nullable columns, too many foreign key relationships indicating missing 2NF and 3NF normalization.

But:
Is the clustered index the only index?
Did you test a different fill factor?
Did you test compression?

marrowyung

ASKER

Did you test compression?

no

so this related to the rebuild of index ?

Is the clustered index the only index?

no, there is a lot of other none clustered index.

User generated image

Did you test a different fill factor?

no, we use 80

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

In combination with the high number of nullable columns I

so from your point of view , how will this impact the index rebuild ?

ste5an

At the first glance, some of the columns seem to form their own tables. Thus those subparts can be extracted. Thus the no longer need to be in the main tables indices, thus reducing its size. Smaller index means faster access, even when it is fragmented.

marrowyung

ASKER

At the first glance, some of the columns seem to form their own tables. Thus those subparts can be extracted.

is that mean we should drop unnecessary column so that the index size is smaller?

ste5an

Yup.

marrowyung

ASKER

at this moment, always that columnstore index shows this kind of fragmentation, is it normal?
the rest of none columnstore index do not have this problem.

ste5an

I cannot tell this without thorough analysis. But this kind of table is for sure not a typical candidate for column store indices. Thus I guess it can be already an basic design problem.

Identify the queries which use the column store index.
Measure these queries and store their query plans and statistics.
Replace that index with a row store index.
Measure the queries again. Compare the query plans and statistics.

My gut feeling is that this index is not used that much and its performance gain isn't that big. So I guess replacing it with a row store index should work.

marrowyung

ASKER

tks.

marrowyung

ASKER

tks.

marrowyung

ASKER

Replace that index with a row store index.

seems the index is created by the application and we do'nt have a clue if we can drop that index.

other thing is yesterday the same thing happen, and what I also found out is, when query store of a database reached MAX, it become read only and that user database will be slow on a lot of thing.

so we purge the store data and everything seems back to normal.