November 12, 2015 at 1:14 am
I have following table structure:
CREATE TABLE [dbo].[TL_CST_Account_Mailing_Address](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[AccountId] [decimal](18, 0) NOT NULL,
[AddressSerialID] [tinyint] NULL,
[CountryID] [decimal](3, 0) NOT NULL,
[CityID] [decimal](4, 0) NOT NULL,
[ProvinceID] [decimal](4, 0) NOT NULL,
[DistrictId] [decimal](4, 0) NULL,
[Street] [nvarchar](100) NULL,
[Town] [nvarchar](100) NULL,
[PostalCode] [nvarchar](20) NULL,
[Address] [nvarchar](100) NOT NULL,
[PhoneNumberOfficeBusiness] [nvarchar](20) NULL,
[PhoneNumberResidence] [nvarchar](20) NULL,
[PhoneNumberMobile] [nvarchar](20) NULL,
[MobileOperatorId] [tinyint] NULL,
[AddressTypeId] [smallint] NULL,
[IsActive] [bit] NULL CONSTRAINT [DF__TL_CST_Ac__IsAct__6A518D31] DEFAULT ((1)),
[FaxNumber] [nvarchar](20) NULL,
[Email] [nvarchar](50) NULL,
CONSTRAINT [PK_TL_CST_Account_Mailing_Address_1] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] WITH CHECK ADD CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_CST_Account] FOREIGN KEY([AccountId])
REFERENCES [dbo].[TL_CST_Account] ([AccountId])
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] CHECK CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_CST_Account]
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] WITH CHECK ADD CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_CST_MST_Mobile_Operator] FOREIGN KEY([MobileOperatorId])
REFERENCES [dbo].[TL_CST_MST_Mobile_Operator] ([OperatorId])
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] CHECK CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_CST_MST_Mobile_Operator]
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] WITH CHECK ADD CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_SRV_Address_Type] FOREIGN KEY([AddressTypeId])
REFERENCES [dbo].[TL_SRV_Address_Type] ([AddressTypeId])
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] CHECK CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_SRV_Address_Type]
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] WITH NOCHECK ADD CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_SRV_Country] FOREIGN KEY([CountryID])
REFERENCES [dbo].[TL_SRV_Country] ([CountryId])
GO
ALTER TABLE [dbo].[TL_CST_Account_Mailing_Address] CHECK CONSTRAINT [FK_TL_CST_Account_Mailing_Address_TL_SRV_Country]
GO
When I ran following query to check highly fragmented indexes to be rebuild or re-organize, It was showing 82.6
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'MyDB'), NULL, NULL, NULL , 'SAMPLED')
--where OBJECT_NAME(OBJECT_ID) = 'TL_CST_Account_Mailing_Address'
ORDER BY avg_fragmentation_in_percent DESC
after running rebuild index script:
ALTER INDEX [PK_TL_CST_Account_Mailing_Address_1] ON [dbo].[TL_CST_Account_Mailing_Address] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
It is not getting lower than 62.5 at all. Please let me know if this is default and won't be lowered between 3 and 5 or I need to do something else which I am missing? Re-design the table schema or run some other scripting?
Shamshad Ali
November 12, 2015 at 2:33 am
November 12, 2015 at 6:48 am
Whats the page_count value for the table in question.
Generally MS recommends only rebuilding indexes on tables with a page_count > 1000
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply