My favorites | Sign in
Project Home Downloads Wiki Issues Source
New issue   Search
for
  Advanced search   Search tips   Subscriptions
Issue 222: Improve SQL Server table clustered index
1 person starred this issue and may be notified of changes. Back to list
Status:  Fixed
Owner:  azizatif
Closed:  Apr 2011


Sign in to add a comment
 
Reported by pie...@gmail.com, Apr 21, 2011
currently Elmah is using the ErrorId as the clustered index, which means that each new row is inserted into a random location in the table. this leads to excessive page splitting, fragmentation, page reads, etc...

please change the primary key to be on the 'Sequence' column so new rows are added at the end of the table, ensuring none of the above badness.


CREATE UNIQUE CLUSTERED INDEX [PK_Sequence] ON [dbo].[ELMAH_Error] 
(
	[Sequence] ASC
)

CREATE UNIQUE NONCLUSTERED INDEX [IX_ErrorId] ON [dbo].[ELMAH_Error] 
(
	[ErrorId] ASC
)

Apr 22, 2011
Project Member #1 azizatif
This was addressed[1] in r131, back in 2007. Could it possible that you DB was created with an older version of the script. The latest version of the script[2] scheduled for release 1.2 has the primary key defined as non-clustered, as follows:

ALTER TABLE [dbo].[ELMAH_Error] WITH NOCHECK ADD 
    CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY NONCLUSTERED ([ErrorId]) ON [PRIMARY] 


[1] https://code.google.com/p/elmah/source/detail?r=131&path=/trunk/src/Elmah/Database.sql
[2] https://code.google.com/p/elmah/source/browse/branches/RB-1.1/src/Elmah/SQLServer.sql?r=643#102
Summary: Improve SQL Server table clustered index
Status: Fixed
Owner: azizatif
Labels: Component-Persistence
Sign in to add a comment

Powered by Google Project Hosting