November 12, 2015 at 1:12 am
I have following table structure:
/****** Object: Table [dbo].[TL_CST_Account_Mailing_Address] Script Date: 11/12/2015 12:59:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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 11:42 am
If the table is small you may not be able to remove any more fragmentation because of how the data is stored internally.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply