July 27, 2009 at 9:45 am
Can someone help me to reduce my fragmentation? I tried rebuild and reorganize, nothing worked...
My Table
CREATE TABLE [dbo].[PatientShareGroup](
[PatientShareGroupID] [int] IDENTITY(1,1) NOT NULL,
[PatientShareGroupName] [varchar](75) NOT NULL,
[VersionID] [int] NOT NULL,
[PopulationID] [int] NOT NULL,
CONSTRAINT [PK_PatientShareGroup] PRIMARY KEY CLUSTERED
(
[PatientShareGroupID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [NUC_PatientShareGroupNameVersionIDPopulationID] UNIQUE NONCLUSTERED
(
[PatientShareGroupName] ASC,
[VersionID] ASC,
[PopulationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[PatientShareGroup] WITH CHECK ADD CONSTRAINT [FK_PatientShareGroup_Population] FOREIGN KEY([PopulationID])
REFERENCES [dbo].[Population] ([PopulationID])
GO
ALTER TABLE [dbo].[PatientShareGroup] CHECK CONSTRAINT [FK_PatientShareGroup_Population]
GO
ALTER TABLE [dbo].[PatientShareGroup] WITH CHECK ADD CONSTRAINT [FK_PatientShareGroup_Version] FOREIGN KEY([VersionID])
REFERENCES [dbo].[Version] ([VersionID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PatientShareGroup] CHECK CONSTRAINT [FK_PatientShareGroup_Version]
GO
July 27, 2009 at 10:04 am
How big are these indexes? How many pages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2009 at 11:06 am
As Gail mentioned above, the table does probably not have enough data to be "considered" for a reindex. I have a few tables with a couple hundred records that don't get below 87%.....
July 27, 2009 at 2:00 pm
Well, they'll be considered for reindexing, and SQL will run the reindex on them, but the eway pages are allocated for very small objects means it's unlikely that they'll get to 0% fragmentation.
It's not a concern. Fragmentation is an issue when doing large range scans of indexes and the reads are from disk. Small indexes (< 1000 pages) are likely to be in memory and, even if they are on disk, aren't large enough for fragmentation to have a significant impact.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply