SQL Server 中的数据库日志记录优化和维护

我正在处理的 Web 应用程序之一使用 NLog 在数据库中记录调试和错误上下文信息。基本上，它使用以下模式执行中等数量的插入（我很欣赏每天数万次）：

<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}"/>

为了尽量减少日志记录的影响，数据库查询是异步发出的（在不同的线程上）。但是，我必须小心不要用完线程池线程。

为了在查询日志时获得更好的性能，我为最常用的列放置了两个索引，log_data和log_user_name。但是，我知道这会对插入性能产生负面影响。entered_date上还有聚集索引，如下所示sp_help：

IX_nlog_entered_date clustered located on PRIMARY entered_date

Q1：有这些索引是可以的还是没有它们更好，并且在很少查询表时会受到惩罚？或者也许有更好的方法。

使用如下简单查询完成查询：

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

或者像这样：

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

显然，这可能会在执行时锁定表，从而延迟一些插入。我认为 usingWITH(NOLOCK)应该是一个不错的选择，但人们常常忘记它。

Q2：如何尽量减少阅读对桌子的影响？我正在考虑拒绝对表的读取访问，而是创建一个存储过程来执行读取NOLOCK，但这会导致更复杂。

一段时间后，应该删除旧记录。据我所知，从大表中删除许多行是一个繁重的查询。Web 应用程序有指定的时间段（晚上）执行维护工作，但我想改进这一步。那么，第三个问题：

Q3：如何将大删除的影响降到最低？. 我正在考虑表分区entered_date（默认为GETDATE()），但我不知道这是否是个好主意。

表和索引定义

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