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

The only recommendations I would make are:

• ensure you are writing non-string values as much as possible
• encapsulate your writes to the audit via stored procedures
• grab any other data from stored procedure calls or,
• consider using a separate just-for-this-purpose View in your audit sproc. Ensure its joins are as minimal as possible. This view would be for lookups for PK for audit messages, etc. when trying to locate the FK for your string data.
• not a recommendation, but a fact: less indexes mean faster inserts + slower selects.
• consider archiving away 'old' audit rows to another table. Keep the audit table as small as you can. Move those older audit rows to another table. For reporting/querying, create a view that will join or union the 'live' and 'older' audits.

Answer 2

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

Answer 3

If you are only appending to the audit tables and run reports that are going to always end up performing table scans, consider removing any indexes on the table.