I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always added at the end. The average row size is 1570 bytes.

There are a lot of page splits because new rows are added frequently. No rows get updates/deleted and there is a non-clustered index on the table so rows can be selected. Due to the page splits, the clustered index is always fragmented > 65%.

I wonder my table would benefit of removing the clustered index and make it a heap table?

This is how my table + non-clustered index looks like:

CREATE TABLE [dbo].[LogEntry](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Application] [varchar](20) NOT NULL,
[EntityFullName] [varchar](80) NOT NULL,
[Action] [int] NOT NULL,
[UserName] [varchar](25) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[EntityId] [varchar](50) NOT NULL,
[WhatChanged] [nvarchar](max) NULL,
CONSTRAINT [PK_LogEntry] PRIMARY KEY CLUSTERED(     
   [Id] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] ) 
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE NONCLUSTERED INDEX [ID_Application_Entity_FullName_TimeStamp] ON [dbo].[LogEntry]
(
    [Application] ASC,
    [EntityFullName] ASC,
    [TimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO

Update: Someone had enabled auto-shrink behind my back => this will be the cause of the fragmentation

  • If there are not a lot of scans and reading occurring on this table, you could consider storing the nvarchar(max) column off row. I am not sure why you are getting fragmentation though with no updates. sp_tableoption N'MyTable', 'large value types out of row', 'ON' – Chad Mattox May 11 '16 at 16:26
  • When the log entries are added, are they added in bunches or by parallel processes? The identity value is created before the row is written to disk, and is a potential cause of fragmentation. – AlwaysLoadingData May 11 '16 at 22:06
up vote 5 down vote accepted

This started as a comment/questions but it got to long so I moved it here:

I'm really thrown by this question. 1.5mil rows isn't really all that big. And the point behind an identity is that it's ever increasing. If that's your CL you shouldn't be doing inserts into the middle of a page, certainly not often enough to cause the level of fragmentation you're seeing.

Couple of questions:

Are you doing IDENTIY_INSERTS? Basically specifying what the identity value should be? Or have you re-set the identity at some point so that you are inserting into the middle of the range?

Typically if you are doing inserts it looks like this:

5 6 7 8 < Next insert goes here >

But if you have something like this (assume your next identity value is 4)

 1 2 3 < Next insert goes here > 100 101  

Then you could be seeing quite a few page splits. But in the normal course of things you shouldn't be.

Is there any chance you are shrinking your database? Auto_shrink or a maint plan/job that does shrinks? If so it's the shrink that's causing your fragmentation not the clustered index.

In general there is nothing wrong with a HEAP and they can be faster for INSERTs. My biggest concern with them tends to be if you are doing large numbers of deletes or updates (which you say you aren't). In those cases you can get a space leak and end up with a table that is multiple GBs in size but has 0 rows.

Actual answer

Given you have a log file, and are only ever inserting, you could try dropping the PK and see how performance goes (in a test environment first of course). Once you've run some tests using your workload and seeing how things go then make your change in production and monitor there for a while. You might even consider dropping the identity column entirely.

Do check that SHRINK thing though. That's a killer.

  • I wonder if there's a MAX value in the Identity column that is higher than the last inserted identity value? (Understanding that max, higher and such depends on what the seeds and increments have been in the past). And I'm also with you on I don't know of a reason to have that PK on this table, of course with a whole lot of assumptions being made. I'd also throw in that the trendy thing to do is to not even put this sort of data in a table. – Karen Lopez May 11 '16 at 17:07
  • 1
    If the Clustered index is created DESCENDING, then inserts would constantly be at the start of the index, which might affect fragmentation, too(??). – AMtwo May 11 '16 at 17:15
  • Please also note that when doing individual INSERTs, Heaps do not fill their pages as fully as Clustered tables do. And I agree that something is going on here that is not evident from the info provided, so +1 for pushing for investigation before proceeding with any changes :-). – Solomon Rutzky May 11 '16 at 17:16
  • 1
    @AMtwo Yep. Example of that here stackoverflow.com/questions/743858/… – Martin Smith May 11 '16 at 17:54

You might find it interesting to check Thomas Kejser's take on indexes in SQL Server. Although clustered indexes are very useful, there can be reasons to keep a heap. For example, read this post:

http://kejser.org/clustered-indexes-vs-heaps/

Particularly look at the topic: Fragmentation Prone tables with lots of INSERT activity

This topic seems to exactly describe the issue that you are facing with fragmentation. So...

Consider the alternative: an indexing strategy where you pick a sufficiently “random” key (like a GUID) and instead of blindly declaring that key a cluster index, you instead leave the table as a heap and just put a good old fashioned unique, non-clustered key index on the GUID. The majority of the table growth from INSERTs now goes to the heap which will nicely fill up and stay un-fragmented.

Another comment from technet: http://technet.microsoft.com/en-us/library/hh213609.aspx

"Sometimes data architects use heaps when data is always accessed through nonclustered indexes and the RID is smaller than a clustered index key. "

Those posts may give you something to think about on your issue.

  • 3
    Interestingly @aaronbertrand did a speed test on rid lookups vs key lookups although he did point out YMMV. t.co/MdaktFpbnz Also (not a problem in this case) but you have to watch out on heaps where you do a lot of deletes. You can find yourself with a space leak. I have a table that frequently takes up multi GB w 0 rows. – Kenneth Fisher May 11 '16 at 15:26

The FILLFACTOR on the table is 100, thus there are no free pages in the cluster index. If you're doing lots of inserts try setting FILLFACTOR to something like 80. And read the SQL Server books online regarding FILLFACTOR. ;-)

  • 2
    For the use case described in the question of inserts into an ever increasing key and no updates this would just be a waste of space. – Martin Smith May 12 '16 at 13:31
  • Fillfactor is not a problem (only last page inserts due to identity key), autoshrink was the problem – Frederik Vanderhaegen May 13 '16 at 6:28

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.