A lot of applications store logging information in a log table.
These tables are special because you will only do inserts. You will never do updates. If you do deletes it will be a nightly job to get rid of old data.
The table has a datetime field which is typically strictly increasing. It is possible that the calling application will lose its connection to the db, and will stack up inserts, and at the point when it gets the connection it can't guarantee that it will do inserts in strict order. But in general I expect that it will be cheap to sort the table on insert if the datetime field is the clustered index.
Most queries will query on datetime, but these will be inequality queries.
With these special properties it feels like there should be a way to optimize it.
What are the best practices?
An example of a log table could be:
CREATE TABLE logMessages (
logTime datetime2(6) NOT NULL,
logSeverity varchar(10) NOT NULL,
logStatus varchar(10) NOT NULL,
A typical query:
SELECT logTime, logSeverity, logText
WHERE logTime >= '2020-10-01'
AND logTime < '2021-11-01'
AND logSeverity IN ('WARNING','ERROR','FATAL')
The datetime field is not unique, do we need a unique identifier?
Option 1: add a
logId BIGINT IDENTITY column
If we add a unique logId, it will not be used as a foreign key in any other tables.
If we use this as a clustered index alone and SQL Server decides that our query will retrieve too many rows for it to use a non-clustered index, it will do a full table scan.
Would it make sense to set the clustered index to (logTime, logId)? The reason I want this is that when the query optimizer will not use a non-clustered index on logTime because it expects too many rows, falling back on a good clustered index will reduce the number of rows to scan.
Option 2: not adding a logId
Set the clustered index to (logTime). Since logTime is not unique SQL Server will have to uniquify it by adding an invisible column. Is this a problem? At least I get a good clustered index that will help most of my queries.
logSeverity is a small int with predefined values (DEBUG,INFO,WARNING,ERROR,FATAL), would it be better to move these to a different table and represent them with a tinyint?
Experts typically say that you can use
WITH (NOLOCK) if you don't need accurate data. Are there any situation where you can use it and still get accurate data? In this case we will not do UPDATE or DELETE, only INSERT. The inserts will also typically be at the end of the table (the datetime field will be increasing). A query that will not look at the last few seconds should not be affected by an insert.
To sum it up:
- What should we do about unique key?
- How should our clustered index be?
- Should we use lookup tables?
- Can we use WITH (NOLOCK)?
- Any other best practices?