SQL fast INSERTs with no UPDATEs

Question

We are using a single table for Auditing in a SQL Server 2008 DB.

The single table architecture works nicely, is simple to query, accommodates schema changes.

But it is a major bottleneck for the entire DB. All INSERTS and UPDATES have to go through the audit table.

We already use NOLOCK HINT for SELECT statements.

Since there are no UPDATEs to this table, is there any suggestions for improving the throughput of INSERT statements?

Answer 1

Make sure you have a INT (or BIGINT) IDENTITY primary clustered index on the table! And preferably no other indices (if possible) - those would slow down inserts.

It's a common misconception that since the table only needs INSERTs and hardly any reads, you should "save" yourself the trouble of a primary, clustered key.

As the Goddess of SQL Server Indexing, Kimberly Tripp, explains in her excellent blog post The Clustered Index Debate continues:

Inserts are faster in a clustered table (but only in the "right" clustered table) than compared to a heap. The primary problem here is that lookups in the IAM/PFS to determine the insert location in a heap are slower than in a clustered table (where insert location is known, defined by the clustered key). Inserts are faster when inserted into a table where order is defined (CL) and where that order is ever-increasing.

So a right clustered index can speed up your inserts, and right here means static (never changes), unique, as small as possible (INT or BIGINT) and preferably ever-increasing (no page splits and therefore no performance penalty).

Also if your table is only ever getting inserts and no updates / deletes, you should make sure to use a 100% FILLFACTOR on the clustered index to fully fill up those SQL server pages.

Marc