Sql-server – Database logging optimization and maintenance in SQL Server

One of the Web applications I am working on, uses NLog to log debug and error context information in the database. Basically, it performs a moderate number of inserts (I appreciate some tens of thousands per day) using the following pattern:

<commandText>
  insert into dbo.nlog
  (log_date, log_level_id, log_level, logger, log_message, machine_name, log_user_name, call_site, thread, exception, stack_trace, full_exception_info)
  values(@timestamp, dbo.func_get_nlog_level_id(@level), @level, @logger, @message, @machinename, @username, @call_site, @threadid, @log_exception, @stacktrace, @FullExceptionInfo);
</commandText>
<parameter name="@timestamp" layout="${longdate}"/>
<parameter name="@level" layout="${level}"/>
<parameter name="@logger" layout="${logger}"/>
<parameter name="@message" layout="${message}"/>
<parameter name="@machinename" layout="${machinename}"/>
<parameter name="@username" layout="${windows-identity:domain=true}"/>
<parameter name="@call_site" layout="${callsite:filename=true}"/>
<parameter name="@threadid" layout="${threadid}"/>
<parameter name="@log_exception" layout="${exception}"/>
<parameter name="@stacktrace" layout="${stacktrace}"/>
<parameter name="@FullExceptionInfo" layout="${gdc:FullExceptionInfo}"/>

In order minimize logging impact, database queries are issued asynchronously (on a different thread). However, I have to be careful not to run out of Thread Pool threads.

For better performance when querying the log, I have put two indexes for the most used columns, log_data and log_user_name. However, I understand that this will have a negative impact of inserts performance. There is also on clustered index on entered_date as shown by sp_help:

IX_nlog_entered_date clustered located on PRIMARY entered_date

Q1: is it ok to have these indexes or is it better to not have them and suffer the penalty when rarely querying the table? Or maybe there is a better approach.

Querying is done using simple queries like the following:

-- just see the latest logged activity
SELECT TOP 1000 *
FROM nlog 
ORDER BY nlog_id DESC

or like this:

SELECT TOP 200*
FROM nlog 
WHERE log_user_name = 'domain\username'
ORDER BY nlog_id DESC

Clearly, this may put a lock on the table while executing, thus delaying some inserts. I think using WITH(NOLOCK) should be a good option, but people often forget about it.

Q2: how can I minimize reading impact on the table? I am thinking about denying read access to the table and instead creating a stored procedure to perform the read with NOLOCK, but this leads to more complexity.

After a while, old records should be deleted. From what I know, deleting many rows from large tables is a heavy query. Web application has a designated period (at night) to perform maintenance jobs, but I would like to improve this step. So, the third question:

Q3: how can I minimize the impact of large deletes?. I am thinking about table partitioning by entered_date (has default of GETDATE()), but I do not know if this is a good idea.

Table and index definitions

CREATE TABLE [dbo].[nlog](
    [nlog_id] [int] IDENTITY(1,1) NOT NULL,
    [entered_date] [datetime2](7) NOT NULL CONSTRAINT [DF_nlog_log_time]  DEFAULT (getdate()),
    [log_app_name] [nvarchar](255) NULL,
    [log_date] [nvarchar](64) NULL,
    [log_level_id] [tinyint] NOT NULL,
    [log_level] [nvarchar](64) NULL,
    [logger] [nvarchar](1024) NULL,
    [log_message] [nvarchar](max) NULL,
    [machine_name] [nvarchar](255) NULL,
    [log_user_name] [nvarchar](255) NULL,
    [call_site] [nvarchar](4000) NULL,
    [thread] [nvarchar](255) NULL,
    [exception] [nvarchar](max) NULL,
    [stack_trace] [nvarchar](max) NULL,
    [full_exception_info] [nvarchar](max) NULL,
 CONSTRAINT [PK_nlog] PRIMARY KEY NONCLUSTERED 
(
    [nlog_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [IX_nlog_entered_date] ON [dbo].[nlog]
(
    [entered_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO