search

maoyuan121 / elmah

Automatically exported from code.google.com/p/elmah
Apache License 2.0
0 stars 0 forks source link

Improve SQL Server table clustered index #222

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
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
)

Original issue reported on code.google.com by pie...@gmail.com on 21 Apr 2011 at 9:34

GoogleCodeExporter commented 9 years ago 
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] 
http://code.google.com/p/elmah/source/detail?r=131&path=/trunk/src/Elmah/Databas
e.sql
[2] 
http://code.google.com/p/elmah/source/browse/branches/RB-1.1/src/Elmah/SQLServer
.sql?r=643#102

Original comment by azizatif on 22 Apr 2011 at 11:01