August 9, 2011 at 10:52 am
I am having a little trouble understanding the behavior of some PKs in a database I just inherited.
Here are what I think are the pertinent facts:
SQL SERVER 2005 SP4
Table is an insert only and all records are added at the end of the table
Table Contains 925k rows
Primary Key is defined as follows:
[OrderIDX] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL
CONSTRAINT [PK_FNCOrdersArchive] PRIMARY KEY CLUSTERED
(
[OrderIDX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
My confusion is that after 3-4 days the avg_fragmentation_in_percent from sys.dm_db_index_physical_stats hits the 90+ range.
As I said I'm new to this database and still trying to figure out what exactly is being done to it. Anyone have any suggestions as to where I might start looking?
Thanks
August 9, 2011 at 11:02 am
Updates that increase the size of the row
Database or file shrinks.
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
August 9, 2011 at 11:03 am
Only 1 really likely suspect.
The application is doing insert into default values. Or something like that.
This reserves them the next id. After that they run an update filling all the columns. This causes the row to grow and eventually a page split happens.
Easy fix is to only do the insert once all the fields are known and then avoiding 99% of the updates.
Lower fill factor may help a little bit here, but that's not the root cause of the issue... and I wouldn't want to have to put 30% FF just to avoid the split.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply