Avatar of marrowyung
marrowyung
 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?



Microsoft SQL Server

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
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



Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
marrowyung

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

ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.


All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.




Did you test a different fill factor?
no, we use 80 

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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.