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
how to make this script check the table name and if it sees that tables name , build the index of that table completely?
Last Comment
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.
ASKER
ste5an,
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.
Priyanka ,
my script about has condition for that:
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);
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.
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 ?
Sure.
ASKER
this is what we are also doing, no other choice man..
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
Okay. Post the table DDL and index scripts.
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
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?
But:
Is the clustered index the only index?
Did you test a different fill factor?
Did you test compression?
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.
Cause sometimes defragmented indices are better left untouched.