Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our products' database contains an audit trail table:

CREATE TABLE gn_AuditTable(
  gn_ObjectId int NULL,
  gn_Action smallint NULL,
  gn_Time datetime NULL,
  gn_UserId int NULL,
  gn_Login varbinary(16) NULL,
  gn_ExtraObjectId int NULL,
  gn_ExtraInfo int NULL
) ON [PRIMARY]

We add rows to this table with a simple insert statement - no stored procedures:

insert into gn_AuditTable (
  gn_ObjectId,gn_Time,gn_UserId,gn_Login,gn_Action,gn_ExtraObjectId,gn_ExtraInfo
) values (
  ?,?,?,?,?,?,?
)

passing the values of the columns as parameters to the query; there are also no triggers.

The problem is that sometimes this insert is super-slow - taking in some cases more than 30 seconds and timing-out.

We have multiple installations of the same product, and we are experienceing the problem in some - but not all - of them.

The audit table can be quite big - up to some million of rows. The very slow insert happens with databases that have big tables (quite naturally) - but we have other databases with simliar number of rows that run fine, and even the databases experiencing the problem have it intermittently, in most cases the insert is reasonably fast (sub-second).

The table has 6 non-clustered, non-unique indexes:

  • gn_Login;

  • gn_ObjectId;

  • gn_Action and gn_ExtraInfo;

  • gn_ExtraObjectId;

  • gn_Time;

  • gn_UserId.

Having all these indexes could cause the problem, but again, we have the same indexes everywhere and the problem only somewhere / sometimes.

We had the insert timing-out at least once on a test database with only one user, so the problem does not appear to be related to the overall load but to the insert and the table themselves.

We used to have a clustered index on gn_ObjectId, but we got rid of it as an attempt to solve this problem. gn_ObjectId is not sequential and so we thought that it is a poor choice for a clustered index, forcing SQL Server to re-order rows on inserts. Probably gn_Time - that is always increasing - would be a better choice.

There is no primary key because there is no column or combination of columns that is 'naturally' unique - we would have to add an id column, and it seems just overhead.

Does anyone have any suggestion at what we could look at to diagnose the problem? What is an insert doing that can take so much time - intermittently?

share|improve this question
    
Based on my very lightweight understanding of the internals stuff, for each insert you're doing into gn_AuditTable you're having 6 additional insert operations to keep those indexes up to date. 7 writes that all have to succeed or fail together. Do you have any idea whether those indexes are even being used? SQL Server does, see this article from Mladen –  billinkc Apr 30 '12 at 15:56
    
Thanks @billinkc. Yes, those indexes have been added (over time) to speed up various queries the system does - based on query execution plans returned by SQL Server. –  MiMo Apr 30 '12 at 16:08

3 Answers 3

Why no clustered index? Why no primary key?

This is most likely your problem: you don't have any order to the table (in the sense of, say, an IDENTITY column) this you are inserting into a heap

See

Edit, after update.

Having a non-unique clustered index requires a uniquifier (DBA.se links) per duplicate gn_ObjectId entry. This is not a good clustering key, which is best when it is

  • numeric
  • monotonically increasing
  • narrow

I also assume you haven't tried a IDENTITY column then...? Try it, report back..

Edit 2, as @JNK says, an ID column is not an overhead in this situation because of how your writes will be managed on disk

share|improve this answer
    
Thanks. I am editing my question to answer your questions... –  MiMo Apr 30 '12 at 12:41
1  
FYI, ID columns aren't "just overhead" - they work well for situations like this where you have no other unique identifier per row. –  JNK Apr 30 '12 at 13:45
3  
Plus note that all of the fields are nullable. Good times. –  Brent Ozar Apr 30 '12 at 14:15
1  
@BrentOzar: sorry, I don't understand your comment. Is there a possible relation between the columns being nullable and the insert time? –  MiMo Apr 30 '12 at 15:56
    
@JNK: 'overhead' in the sense that it would be a column that is never read nor referened anywhere –  MiMo Apr 30 '12 at 15:57

Is your database large enough? Are you sure your INSERT don't trigger auto-growth? If your deployements don't have Instant File Initialization enabled then this is exactly the behavior one would expect when a database file growth is triggered: random blocking of writes for the duration of file growth and initialization. You could also be experiencing log file growth (this at least can eb quickly identified by looking at the Log Growths performance counter) but for log growths to occur you need to have a non-simple recovery model and a backup strategy that is basically absent (ie. log truncation does not occur).

As for the design of your table: time series are usually clustered by the time value, because most queries cover time ranges (eg. 'select all events in the past 3 days') so that would make gn_Time your likely non-unique clustered key candidate.

share|improve this answer
    
+1 Should have noted this myself (as per stackoverflow.com/a/2009693/27535) Could by a regular or auto shrink going on, for example –  gbn Apr 30 '12 at 15:32
    
Thanks. Log files are trucated regularly, but a growth of the data file is indeed possible. My understanding though was that the operation on the data files are done by the lazy writer, and so they don't block the query - only the writes to the log are synchronous. Am I wrong? –  MiMo Apr 30 '12 at 16:00
2  
@MiMo: you are wrong unfortunately. The lazy writer is tasked with flushing dirty pages, something completely different. A database growth event is a big freeze on the database, all queries and all writes are blocked until the file is grown and initialized. –  Remus Rusanu Apr 30 '12 at 16:12
    
Thanks! This looks like it - we'll go and check file growth. –  MiMo Apr 30 '12 at 16:18
up vote 1 down vote accepted

We found the problem:

  • there is a maintenance procedure that removes data from the database, including rows from gn_AuditTable;

  • the delete gn_AuditTable . . is in a transaction together with other statements (bad)

  • it can happen that quite a lot of rows are deleted in one go - in such a case SQL Server locks the entire table - and then insert gn_AuditTable time-out waiting for the transaction to finish

We discovered this because we were able to have a look at the locks when we got a time-out, and we noticed the table lock. We are moving the delete gn_AuditTable . . . out of the transaction, and possible split it into smaller batches, not to trigger a table lock.

We are implementing a clustered index on gn_Time and the Instant File Initialization - both are beneficial independently from this specific problem - thanks!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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