Otimização e manutenção de logs de banco de dados no SQL Server

Um dos aplicativos da Web em que estou trabalhando usa NLog para registrar informações de contexto de depuração e erro no banco de dados. Basicamente, ele executa um número moderado de inserções (aprecio algumas dezenas de milhares por dia) usando o seguinte padrão:

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

Para minimizar o impacto do registro, as consultas do banco de dados são emitidas de forma assíncrona (em um encadeamento diferente). No entanto, tenho que ter cuidado para não ficar sem threads do Thread Pool.

Para melhor desempenho na consulta do log, coloquei dois índices para as colunas mais utilizadas, log_data e log_user_name . No entanto, entendo que isso terá um impacto negativo no desempenho das inserções. Há também um índice clusterizado em enter_date , conforme mostrado por sp_help:

IX_nlog_entered_date clustered located on PRIMARY entered_date

P1: tudo bem ter esses índices ou é melhor não tê-los e sofrer a penalidade por raramente consultar a tabela? Ou talvez haja uma abordagem melhor.

A consulta é feita usando consultas simples como as seguintes:

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

ou assim:

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

Claramente, isso pode bloquear a tabela durante a execução, atrasando algumas inserções. Acho que usar WITH(NOLOCK)deveria ser uma boa opção, mas muitas vezes as pessoas se esquecem disso.

P2: como posso minimizar o impacto da leitura na mesa? Estou pensando em negar o acesso de leitura à tabela e, em vez disso, criar um procedimento armazenado para realizar a leitura com NOLOCK, mas isso leva a mais complexidade.

Depois de um tempo, os registros antigos devem ser excluídos. Pelo que sei, excluir muitas linhas de tabelas grandes é uma consulta pesada. A aplicação Web tem um período designado (à noite) para realizar trabalhos de manutenção, mas gostaria de melhorar esta etapa. Então, a terceira pergunta:

P3: como posso minimizar o impacto de grandes exclusões? . Estou pensando em particionar tabelas por entered_date(tem o padrão deGETDATE() ), mas não sei se é uma boa ideia.

Definições de tabelas e índices

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