Alexei's questions

Um de nossos DBAs reclamou com nossa equipe que notou que cerca de uma dúzia de conexões adormecidas são praticamente permanentes. Cada um deles indica uma consulta muito curta (um único registro de um único registro foi retornado filtrado por seu PK clusterizado).

Minha suposição é que a causa é o uso do pool de conexões ADO.NET e o aplicativo que faz a mesma consulta com bastante frequência.

Estou tentando descobrir se essas conexões adormecidas podem ter um impacto significativo no desempenho do SQL Server. Por padrão, o pool de conexões ADO.NET tem um limite de 100 conexões, então minha suposição é que uma dúzia de conexões adormecidas deve ser insignificante.

Só encontrei informações neste tópico :

O custo mínimo por sessão de sono é da ordem de 32kb de RAM - muito, muito modesto!

Provavelmente há algum custo adicional minúsculo em sobrecarga de CPU, mas será quase indetectável mesmo para 10.000 sessões.

Em relação às sobrecargas de sessão/conexão, o SQL Server é muito bem comportado!

Esta informação é precisa?

Estou trabalhando em um projeto .NET bastante antigo e introduzi alguns novos recursos (no topo) que geraram o seguinte efeito colateral: todos os SELECTs gerados (ou grupos de) são agrupados em BEGIN TRAN ... COMMITinstruções.

Isso parece bobo, mas livrar-se disso requer muitas mudanças e não posso me dar ao luxo de fazê-lo. Minha suposição é que isso basicamente significa uma pequena sobrecarga para cada grupo de SELECTs (ida e volta entre o aplicativo e o SQL Server para BEGIN TRAN e COMMIT).

Eu estou querendo saber se há mais para isso (bloqueio extra?).

Pergunta: Existe algum efeito colateral se as instruções select forem encapsuladas em BEGIN TRAN ... COMMIT?

Este é um acompanhamento da pergunta ALTER VIEW descarta o Índice da Visualização .

A resposta aceita indica que ALTER VIEW eliminará automaticamente quaisquer índices definidos.

Isso é um pouco contra intuitivo para como outras operações semelhantes estão acontecendo:

• é natural esperar que coisas relacionadas, como permissões ou índices, sejam descartadas no objeto, não no alter
• alterar uma tabela não descarta nenhum índice (na verdade, você precisa descartar coisas como índices, restrições etc.)

Eu estou querendo saber por que ter esse comportamento por padrão em primeiro lugar (ou seja, há algum aspecto objetivo como redução de desempenho em alguns casos devido à alteração de exibição).

Pergunta: Qual é a lógica de descartar todos os índices para uma exibição alterada?

Eu tenho um ambiente de teste em execução no SQL Server 2017 Express que usa apenas dados recentes (um trabalho remove dados com mais de três meses uma vez por mês).

O trabalho faz o seguinte:

1. Usando um loop while, removeu dados com mais de três meses
2. Encolhe o banco de dados, se seu tamanho estiver acima de algum limite
3. Reindexa todas as tabelas

Este artigo descreve como é terrível reduzir o banco de dados devido à fragmentação do índice e por que a reindexação também requer espaço extra.

A última execução começou a partir de um banco de dados de 9 GB. A exclusão leva de 3 a 4 minutos e a reindexação leva cerca de 30 segundos. O banco de dados foi reduzido para cerca de 5 GB.

Como este é um ambiente de teste, posso arcar com esse tempo de inatividade sem problemas.

Pergunta: É ruim encolher + reindexar depois de remover uma parte significativa dos dados?

Esta pergunta é uma continuação dessas duas:

• Reversão automática de transações explícitas após X período de tempo
• O arquivo de banco de dados …_log tem um tamanho máximo de arquivo definido como x MB. Se ficar sem espaço, o banco de dados parará de funcionar

A partir do segundo, descubro que um usuário de SQL descuidado pode deixar uma transação muito longa e levar à alocação de uma grande quantidade de disco para o arquivo de log.

O primeiro indica que matar uma transação após um certo período de tempo pode ser alcançado por meio de um trabalho, embora essa abordagem não seja recomendada.

Se eu tiver certeza de que nenhuma das transações abertas em um banco de dados não deve alocar mais do que uma certa quantidade de log, existe uma maneira de descobrir quanto log está alocado para um SPID, para que eu possa matá-lo?

Resumidamente, estou interessado em limitar o uso máximo do arquivo de log para uma transação.

Pergunta: Existe uma maneira de limitar o uso do arquivo de log para uma transação no SQL Server?

Este é um acompanhamento desta questão . Também está relacionado a essa solicitação de recurso da Microsoft.

No entanto, muitos anos se passaram e vários lançamentos importantes chegaram ao mercado desde que foi relatado.

Pergunta: o SQL Server 2017 fornece algum mecanismo para facilitar a descoberta da causa raiz desse erro? Ou é tão difícil de investigar quanto há 9 anos, quando o problema foi relatado?

Em alguns cenários como este , o SQL Server truncará silenciosamente os valores (N)VARCHAR, levando a uma séria perda de dados ao declarar variáveis ​​incorretamente.

Descrição: O SQL Server pode ser configurado para não truncar silenciosamente os valores VARCHAR? (e emitir um erro/gerar uma exceção em vez disso)

Estou usando BrentOzaro SQL Server First Responder Kit desde que me enquadro na categoria "desenvolvedor que gerencia Microsoft SQL Servers. A culpa é sua se eles estiverem inativos ou lentos".

Um dos avisos de execução sp_blitzdiz o seguinte:

O arquivo de banco de dados [DB] DB_log tem um tamanho máximo de arquivo definido como 10240 MB. Se ficar sem espaço, o banco de dados parará de funcionar, mesmo que haja espaço em disco disponível.

Os arquivos de log estão em uma unidade separada do sistema operacional e, de fato, limitei seu crescimento. Todos os bancos de dados têm modelo de recuperação simples (a maioria dos dados é obtida automaticamente por meio de replicação e ETL e a recuperação do último backup é suficiente para mim).

O banco de dados tem cerca de 20 GB de dados.

Pergunta: como o banco de dados pode parar de funcionar se o modelo de recuperação não me forçar a fazer backup do log?

Estou tentando realizar uma operação de limpeza em uma tabela usando DELETEe recebo o seguinte erro:

Não foi possível alocar espaço para armazenamento temporário de execução do objeto 'dbo.SORT: 140767697436672' no banco de dados 'tempdb' porque o grupo de arquivos 'PRIMARY' está cheio. Crie espaço em disco excluindo arquivos desnecessários, descartando objetos no grupo de arquivos, adicionando arquivos adicionais ao grupo de arquivos ou definindo o crescimento automático para arquivos existentes no grupo de arquivos.

Antes de executar o DELETEtenho mais de 11G de espaço livre em disco. Quando o erro é emitido, não tenho quase nada nessa partição. As informações de contexto estão abaixo:

1) Consulta problemática:

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE()) 
delete from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)

2) Cardinalidade para tabelas envolvidas

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
select count(1) from art.Article    -- 137181
select count(1) from art.Article where PublishDate < @deleteDate    -- 111450
select count(1) from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)      -- 12153045
exec sp_spaceused 'art.ArticleConcept'
-- name             rows       reserved     data        index_size   unused
-- ArticleConcept   14624589   1702000 KB   616488 KB   1084272 KB   1240 KB

3) Índices

-- index_name   index_description   index_keys
-- IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq   nonclustered located on PRIMARY ArticleId

CREATE NONCLUSTERED INDEX [IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq] ON [art].[ArticleConcept]
(
[ArticleId] ASC
)
INCLUDE (   [LexemId],
[Freq]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

4) Servidor

Select @@version
-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
-- Feb 20 2014 20:04:26 
-- Copyright (c) Microsoft Corporation
-- Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

5) Plano de execução (estimado)

Entendo que estou executando um DELETE grande, mas não consigo entender por que isso requer tanto espaço: a ArticleConcepttabela inteira tem menos de 2 GB (espaço reservado), mas para remover registros dela é necessário mais de 11 GB.

Pergunta: Por que meu comando DELETE requer uma grande quantidade de armazenamento temporário de execução?

Eu removi todos os índices secundários e pude executar o DELETE. No entanto, por que é necessário tanto mais espaço para realizar as tarefas DELETEao tê-las, parece-me estranho.

Estou tentando excluir 12.153.045 de 14.624.589 registros (muitos). Não monitorei o log de transações, mas uma vez recebi um erro relacionado a ele:

O log de transações do banco de dados... está cheio devido a 'ACTIVE_TRANSACTION'

Eu tenho algumas tabelas de folhas (sem FK para elas) com algumas centenas de milhares de registros que são usados ​​para sincronizar alguns dados externos usando o Entity Framework ORM. Isso envolve alguns DELETEs seguidos por BULK INSERTs.

Para a maioria das tabelas, alguns valores antigos podem ficar para sempre, então não posso usar SEQUENCEs com CYCLE, como sugerido em um dos comentários.

Um efeito é que os valores de identidade continuarão aumentando ao longo do tempo e eu quero ser capaz de reduzir seus valores.

Esta pergunta e suas respostas explicam que os valores de identidade não podem ser atualizados, mesmo que identity_insertestejam ativados para a tabela.

Uma maneira rápida de fazer isso é transferir todos os dados para uma tabela de buffer e realizar a troca via renomear. Algo como o seguinte:

-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready

begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);  

-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.    

select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0

DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)    

insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak

drop table ActiveDirectoryCache_bak

alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';

select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.  

-- this should be replaced with commit when not in test mode
rollback

Eu posso fazer esse tipo de operação durante a noite e eles parecem levar apenas alguns segundos para o meu volume de dados (4 segundos para este exemplo).

Pergunta: Existem métodos para evitar a execução de uma cópia completa de dados para obter valores menores para colunas de identidade? Ou esta é uma das melhores abordagens no meu contexto (volume de dados razoável e poder bloquear algumas tabelas por vários segundos).

Esta pergunta e sua resposta explicam como as inserções em massa serão renderizadas FKcomo não confiáveis, pois as inserções em massa não são completamente verificadas (somente PKe UNIQUEs) . Também sei que a desativação temporária de um FKpode levar ao status não confiável.

Meu projeto atual usa um banco de dados bastante pequeno (< 3 GB) e a contagem típica de tabelas é inferior a 200 mil linhas. No entanto, a integridade é muito importante, então confio muito nas FKrestrições (a integridade é muito mais importante que a velocidade).

Pergunta: existe algum outro mecanismo (além de disable e BULK INSERT) que possa produzir chaves estrangeiras não confiáveis?

Eu sou principalmente um desenvolvedor .NET usando Entity Framework ORM. Porém, como não quero falhar na utilização do ORM , estou tentando entender o que acontece dentro da camada de dados (banco de dados). Basicamente, durante o desenvolvimento eu inicio o profiler e verifico o que algumas partes do código geram em termos de consultas.

Se eu detectar algo totalmente complicado (ORM pode gerar consultas horríveis mesmo de instruções LINQ bastante simples, se não cuidadosamente escritas) e/ou pesada (duração, CPU, leituras de página), eu o pego no SSMS e verifico seu plano de execução.

Funciona bem para o meu nível de conhecimento de banco de dados. No entanto, BULK INSERT parece ser uma criatura especial, pois não parece produzir um SHOWPLAN .

Vou tentar ilustrar um exemplo muito simples:

Definição da tabela

CREATE TABLE dbo.ImportingSystemFileLoadInfo
(
    ImportingSystemFileLoadInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ImportingSystemFileLoadInfo PRIMARY KEY CLUSTERED,
    EnvironmentId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo REFERENCES dbo.Environment,
    ImportingSystemId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo_ImportingSystem REFERENCES dbo.ImportingSystem,
    FileName NVARCHAR(64) NOT NULL,
FileImportTime DATETIME2 NOT NULL,
    CONSTRAINT UQ_ImportingSystemImportInfo_EnvXIs_TableName UNIQUE (EnvironmentId, ImportingSystemId, FileName, FileImportTime)
)

Nota: nenhum outro índice está definido na tabela

A inserção em massa (o que eu pego no profiler, apenas um lote)

insert bulk [dbo].[ImportingSystemFileLoadInfo] ([EnvironmentId] Int, [ImportingSystemId] Int, [FileName] NVarChar(64) COLLATE Latin1_General_CI_AS, [FileImportTime] DateTime2(7))

Métricas

• 695 itens inseridos
• CPU = 31
• Leituras = 4271
• Grava = 24
• Duração = 154
• Contagem total de mesas = 11.500

Para o meu aplicativo, tudo bem, embora as leituras pareçam bastante grandes (eu sei muito pouco sobre os componentes internos do SQL Server, então comparo com o tamanho da página de 8K e as pequenas informações de registro que tenho)

Pergunta: como posso investigar se este BULK INSERT pode ser otimizado? Ou não faz sentido, já que é sem dúvida a maneira mais rápida de enviar grandes dados de um aplicativo cliente para o SQL Server?

Um colega meu notou ids incorretos gerados para itens inseridos. Causa mais provável: algum script emite um comando como o seguinte:

dbcc checkident('dbo.table', reseed, 1)

Eu pensei em configurar um gatilho DDL para capturar esses comandos, mas essa lista de eventos DDL não parece incluir nada relacionado a reseeding.

Pergunta: existe uma maneira de capturar esses comandos de maneira semelhante, os gatilhos DDL capturam várias alterações no esquema do banco de dados?

Tenho a seguinte tabela:

CREATE TABLE dbo.Document
(
    DocumentId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentPRIMARY KEY CLUSTERED,
    [Timestamp] datetime2(7) NOT NULL CONSTRAINT DF_Document_Timestamp  DEFAULT (getdate()),
    CreatedBy nvarchar(128) NOT NULL CONSTRAINT DF_Document_CreatedBy  DEFAULT (dbo.getCurrentUser()),
    MonthId int NOT NULL,
    TimeModeId int NOT NULL CONSTRAINT FK_Document_TimeMode REFERENCES usr.TimeMode,
    Key1 bit NOT NULL,
    Key2 int NULL,
    Key3 varchar(max) NULL,   -- sometimes above 8000chars
    Key4 varchar(max) NULL,   -- sometimes above 8000chars
    Key5 varchar(max) NULL,   -- sometimes above 8000chars
    Key6 varchar(max) NULL,   -- sometimes above 8000chars
    Key7 varchar(max) NULL,   -- sometimes above 8000chars
    Key8 int NOT NULL,
    CONSTRAINT FK_Document_BrandType FOREIGN KEY(Key8) REFERENCES dbo.BrandType (Key8),
)

Embora eu tenha insistido em encontrar um identificador natural melhor, tive que lidar com a seguinte tupla única natural:

MonthId, TimeModeId, Key1, ... , Key8

Isso é muito grande para um índice UNIQUE (máximo de 900 bytes no SQL Server 2014 ou menos), então eu tive que criar algo. Minha ideia é calcular um hash para essas colunas, então eu tinha PERSISTED COMPUTEDcolunas como acima:

FiltersHash  AS (hashbytes('SHA2_256',(
        (((((((((((((((
            (CONVERT(varchar(10),MonthId)+'|') 
            + CONVERT(varchar(4),TimeModeId))
            +'|')+CONVERT(varchar(4),Key1))
            +'|')+isnull(CONVERT(varchar(max),Key2),''))
            +'|')+isnull(CONVERT(varchar(max),Key3),''))
            +'|')+isnull(CONVERT(varchar(max),Key4),''))
            +'|')+isnull(CONVERT(varchar(max),Key5),''))
            +'|')+isnull(CONVERT(varchar(max),Key6),''))
            +'|')+isnull(CONVERT(varchar(max),Key7),''))
            +'|')+isnull(CONVERT(varchar(4),Key8),''))
        ) PERSISTED,
CONSTRAINT UQ_Document_FiltersHash UNIQUE NONCLUSTERED (FiltersHash),

Ele se mostrou útil porque, por meio de um cenário complicado, o aplicativo tentou duplicar um documento.

Pergunta: esta solução é boa ou existem soluções mais simples ou mais eficientes para o problema de unicidade de grande largura?

Nota: No meu aplicativo, posso ignorar com segurança as colisões (se isso acontecer, as consequências são bastante pequenas). Obrigado Aaron Bertrandpor apontar.

Dentro de um aplicativo da Web em que estou trabalhando, todas as operações de banco de dados são abstraídas usando alguns repositórios genéricos definidos sobre o Entity Framework ORM.

No entanto, para ter um design simples para os repositórios genéricos, todas as tabelas envolvidas devem definir um inteiro único ( Int32em C#, intem SQL). Até agora, esse sempre foi o PK da mesa e também o IDENTITY.

As chaves estrangeiras são muito usadas e fazem referência a essas colunas inteiras. Eles são necessários para a consistência e para a geração de propriedades de navegação pelo ORM.

A camada de aplicação normalmente faz as seguintes operações:

• carga de dados inicial da tabela (*) -SELECT * FROM table
• Atualizar -UPDATE table SET Col1 = Val1 WHERE Id = IdVal
• Excluir -DELETE FROM table WHERE Id = IdVal
• Inserir -INSERT INTO table (cols) VALUES (...)

Operações menos frequentes:

• Inserção em massa - BULK INSERT ... into tableseguida (*) por todo o carregamento de dados (para recuperar identificadores gerados)
• Exclusão em massa - esta é uma operação de exclusão normal, mas "volumosa" da perspectiva do ORM:DELETE FROM table where OtherThanIdCol = SomeValue
• Atualização em massa - esta é uma operação de atualização normal, mas "volumosa" da perspectiva do ORM:UPDATE table SET SomeCol = SomeVal WHERE OtherThanIdCol = OtherValue

*todas as tabelas pequenas são armazenadas em cache no nível do aplicativo e quase todas SELECTsnão alcançarão o banco de dados. Um padrão típico é a carga inicial e muitos INSERTs, UPDATEs e DELETEs.

Com base no uso atual do aplicativo, há uma chance muito pequena de atingir 100 milhões de registros em qualquer uma das tabelas.

Pergunta: Do ponto de vista de um DBA, existem problemas significativos que posso encontrar por ter essa limitação de design de tabela?

[EDITAR]

Depois de ler as respostas (obrigado pelo ótimo feedback) e os artigos referenciados, sinto que tenho que adicionar mais detalhes:

1. Especificidades do aplicativo atual - não mencionei sobre o aplicativo da web atual, porque quero entender se o modelo pode ser reutilizado para outros aplicativos também. No entanto, meu caso particular é um aplicativo que extrai muitos metadados de um DWH. Os dados de origem são bastante confusos (desnormalizados de maneira estranha, com algumas inconsistências, sem identificador natural em muitos casos etc.) e meu aplicativo está gerando entidades separadas claras. Além disso, muitos dos identificadores gerados ( IDENTITY) são exibidos, para que o usuário possa utilizá-los como chaves de negócios. Isso, além de uma refatoração de código massiva, exclui o uso de GUIDs .

2. "eles não devem ser a única maneira de identificar exclusivamente uma linha" (Aaron Bertrand♦) - esse é um conselho muito bom. Todas as minhas tabelas também definem uma UNIQUE CONSTRAINT para garantir que duplicatas de negócios não sejam permitidas.

3. Design orientado a aplicativos de front-end versus design orientado a banco de dados - a escolha do design é causada por esses fatores

   1. Limitações do Entity Framework - PKs de várias colunas são permitidos, mas seus valores não podem ser atualizados

   2. Limitações personalizadas - ter uma única chave inteira simplifica muito as estruturas de dados e o código não SQL. Ex.: todas as listas de valores possuem uma chave inteira e valores exibidos. Mais importante, garante que qualquer tabela marcada para armazenamento em cache poderá ser colocada em um Unique int key -> valuemapa.

4. Consultas de seleção complexas - isso quase nunca acontecerá porque todos os dados de tabelas pequenas (< 20-30 K registros) são armazenados em cache no nível do aplicativo. Isso torna a vida um pouco mais difícil ao escrever o código do aplicativo (mais difícil de escrever LINQ), mas o banco de dados é atingido muito melhor:

   1. Visualizações de lista - não gerarão SELECTconsultas no carregamento (tudo é armazenado em cache) ou consultas que se parecem com isso:

      SELECT allcolumns FROM BigTable WHERE filter1 IN (val1, val2) AND filter2 IN (val11, val12)
      

      Todos os outros valores necessários são buscados por meio de pesquisas de cache (O(1)), portanto, nenhuma consulta complexa será gerada.

   2. Editar visualizações - gerará SELECTinstruções como esta:

      SELECT allcolumns FROM BigTable WHERE PKId = value1
      

(todos os filtros e valores são ints)

Pediram-me para criar um sistema de auditoria simples para um aplicativo da Web. Devido a alguns constrangimentos optei por uma forma "suja e rápida". Isso foi possível devido à pequena atividade naquela aplicação específica (várias centenas de inserções por dia).

No entanto, agora tenho que implementar em outro aplicativo da Web que gere mais atividade, então considero refatorá-lo.

Notas:

• todas as consultas são geradas pelo ORM (estrutura de entidade) usada e parecem bastante terríveis.

• todos os testes são realizados com uma tabela preenchida com alguns registros fictícios de 2K, distribuídos uniformemente entre alguns usuários, tempo etc.

Definição de dados

CREATE TABLE dbo.AppEvent
(
    AppEventId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_AppEvent PRIMARY KEY CLUSTERED,
    InsertTimestamp DATETIME2 NOT NULL CONSTRAINT DF_AppEvent DEFAULT(GETDATE()),
    UserId INT NOT NULL CONSTRAINT FK_AppEvent_UserId REFERENCES dbo.AppUser,
    EventTypeId INT NOT NULL CONSTRAINT FK_AppEvent_EventType REFERENCES dbo.EventType,
    RegionId INT NULL CONSTRAINT FK_AppEvent_Region REFERENCES dbo.Region,
    CountryId INT NULL CONSTRAINT FK_AppEvent_Country REFERENCES dbo.Country,
    InsertDay AS (CAST(InsertTimestamp as DATE)),
    InsertMonth AS (CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, InsertTimestamp), 0) AS DATE)),
    InsertYear AS (CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, InsertTimestamp), 0) AS DATE)),
    Description NVARCHAR(2000) NULL,
    ProjectId INT NULL CONSTRAINT FK_AppEvent_Project REFERENCES dbo.Project,
    ReminderActionId INT NULL CONSTRAINT FK_AppEvent_ReminderAction REFERENCES dbo.ReminderAction
)
GO

Esta é a única tabela relevante. Todas as referências FK vão para chaves primárias agrupadas e a maioria das tabelas contém menos de 100 registros.

Registro real

O aplicativo tenta agrupar informações de log, evitando inserir muito próximo no tempo (mesmo usuário, mesmo tipo de evento).

Assim, ele precisa fazer um SELECT:

exec sp_executesql N'SELECT TOP (1) 
    [Project1].[InsertTimestamp] AS [InsertTimestamp]
    FROM ( SELECT 
        [Extent1].[AppEventId] AS [AppEventId], 
        [Extent1].[InsertTimestamp] AS [InsertTimestamp]
        FROM [dbo].[AppEvent] AS [Extent1]
        WHERE ([Extent1].[UserId] = @p__linq__0) AND ([Extent1].[EventTypeId] = @p__linq__1) AND (([Extent1].[ReminderActionId] = @p__linq__2) OR (([Extent1].[ReminderActionId] IS NULL) AND (@p__linq__2 IS NULL)))
    )  AS [Project1]
    ORDER BY [Project1].[AppEventId] DESC',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 int',@p__linq__0=1,@p__linq__1=4,@p__linq__2=27

Isso gera cerca de:CPU = 16, Reads = 34, Writes = 0, Duration = 0

Olhando para o plano de execução, pensei que um índice poderia melhorar as coisas:

CREATE INDEX IDX_AppEvent_User_EventType ON dbo.AppEvent (UserId, EventTypeId, ReminderActionId) INCLUDE (AppEventId, InsertTimestamp)

Isto dáCPU = 0, Reads = 20, Writes = 0, Duration = 0

As instruções INSERT reais são assim:

exec sp_executesql N'INSERT [dbo].[AppEvent]([InsertTimestamp], [UserId], [EventTypeId], [RegionId], [CountryId], [Description], [ProjectId], [ReminderActionId])
VALUES (@0, @1, @2, @3, @4, @5, @6, NULL)
SELECT [AppEventId], [InsertDay], [InsertMonth], [InsertYear]
FROM [dbo].[AppEvent]
WHERE @@ROWCOUNT > 0 AND [AppEventId] = scope_identity()',N'@0 datetime2(7),@1 int,@2 int,@3 int,@4 int,@5 nvarchar(2000),@6 int',
@0='2017-01-30 14:54:02.6469319',@1=1,@2=7,@3=5,@4=305,@5=N'Custom message',@6=1533

SELECTA instrução é causada pelo ORM que precisa conhecer o identificador recém-criado (terei que procurar se posso me livrar do extra SELECTque não é necessário).

Isso leva cerca deCPU = 16, Reads = 32, Writes = 0, Duration = 9

O plano de execução gera o seguinte:

e isto

Relatório

O relatório de auditoria é bastante simples e raramente é executado (várias vezes por dia no máximo). As consultas típicas são assim:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K2] AS [InsertDay], 
    [GroupBy1].[K1] AS [CountryId], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[CountryId] AS [K1], 
        [Extent1].[InsertDay] AS [K2], 
        COUNT(1) AS [A1]
        FROM [dbo].[AppEvent] AS [Extent1]
        WHERE ([Extent1].[EventTypeId] IN (1, 6, 7, 9)) AND ([Extent1].[CountryId] IS NOT NULL)
        GROUP BY [Extent1].[CountryId], [Extent1].[InsertDay]
    )  AS [GroupBy1]

`CPU = 16, Reads = 25, Writes = 0, Duration = 11`

SELECT 
    1 AS [C1], 
    [GroupBy1].[K2] AS [InsertMonth], 
    [GroupBy1].[K1] AS [CountryId], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[CountryId] AS [K1], 
        [Extent1].[InsertMonth] AS [K2], 
        COUNT(1) AS [A1]
        FROM [dbo].[AppEvent] AS [Extent1]
        WHERE ([Extent1].[EventTypeId] IN (1, 6, 7)) AND ([Extent1].[CountryId] IS NOT NULL)
        GROUP BY [Extent1].[CountryId], [Extent1].[InsertMonth]
    )  AS [GroupBy1]

`CPU = 16, Reads = 25, Writes = 0, Duration = 12`

Pergunta: considerando uma geração de no máximo 100K eventos por dia, devo pensar em reescrever todo o mecanismo de auditoria (da perspectiva do banco de dados)?

Na camada de aplicação posso fazer diversas melhorias como: garantir que a auditoria não seja realizada na mesma transação que as alterações operacionais e utilizar outra thread para realizar as consultas.

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

Estou trabalhando em um aplicativo que possui vários módulos herdados que dependem fortemente do procedimento armazenado (sem ORM, portanto, todas as buscas e persistência de dados são feitas por meio de procedimentos armazenados).

A segurança dos módulos legados depende SUSER_NAME()de obter o usuário atual e aplicar regras de segurança.

Estou migrando para usar um ORM (Entity Framework) e o conector SQL usará um usuário genérico para se conectar ao banco de dados (SQL Server), então tenho que fornecer o nome de usuário atual para muitos procedimentos.

Para evitar alterações no código .NET, pensei em "injetar" de alguma forma o usuário atual no contexto quando uma nova conexão é feita:

CREATE TABLE dbo.ConnectionContextInfo 
(
    ConnectionContextInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ConnectionContextInfo PRIMARY KEY,
    Created DATETIME2 NOT NULL CONSTRAINT DF_ConnectionContextInfo DEFAULT(GETDATE()),
    SPID INT NOT NULL,
    AttributeName VARCHAR(32) NOT NULL, 
    AttributeValue VARCHAR(250) NULL,
    CONSTRAINT UQ_ConnectionContextInfo_Info UNIQUE(SPID, AttributeName)
)
GO

Quando uma conexão é aberta (ou reutilizada, como um pool de conexão é usado), o seguinte comando é usado:

exec sp_executesql N'
    DELETE FROM dbo.ConnectionContextInfo WHERE SPID = @@SPID AND AttributeName = @UsernameAttribute;
    INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName, AttributeValue) VALUES (@@SPID, @UsernameAttribute, @Username);
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
go

(0 CPU, ~15 leituras, <6 ms)

Uma função escalar permite obter facilmente o usuário atual:

alter FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
    DECLARE @ret VARCHAR(250) = (SELECT AttributeValue FROM ConnectionContextInfo where SPID = @@SPID AND AttributeName = 'Username')
    -- fallback to session current, if no data is found on current SPID (i.e. call outside of the actual application)
    RETURN ISNULL(@ret, SUSER_NAME())
END
GO

Existem ressalvas (robustez, desempenho, etc.) nessa abordagem do ponto de vista da camada de dados?

Obrigado.

Algumas das tarefas da minha equipe estão relacionadas à otimização de procedimentos armazenados. Sou principalmente um desenvolvedor .NET, mas pensei em escrever um procedimento genérico para comparação de dados enquanto os procedimentos estão sendo alterados. As principais metas são:

• garantir que as alterações não interrompam as funcionalidades existentes (as mesmas entradas devem fornecer a mesma saída)
• pode ser executado de vez em quando para facilitar a detecção de bugs introduzidos por otimizações
• também deve fornecer informações básicas de criação de perfil, para que a versão "otimizada" seja realmente mais rápida
• deve ser executado a partir do SQL Server

O código:

-- 
-- Description: compares the results returned by two stored procedures. Comparison is performed using a 'loopback' linked server and using openquery, so the final query
--              must obey openquery limitations. It returns all rows that are within the first result set and not within the second and viceversa. If all result sets are 
--              empty, results are equivalent (order does not matter)
--
-- PARAMS:
--      @Procedure1FullName: procedure 1 full name (i.e. database.schema.proc_name)
--      @Params1Str: procedure 1 params as string (e.g. @param1 = value1, @param2 = 'value2)'
--      @Procedure2FullName: procedure 2 full name
--      @Params2Str: procedure 2 params as string
--      @ResultSetStr: result set column specification (it is required for usage of procedure in SQL 2012+)
--      @LoopBackServerName: loopback (same server) linked server name - required to use openquery on the same server (and database)
--      @Debug: outputs debug info
--
-- =============================================
ALTER PROCEDURE [dbo].[uspCompareProcedureResults]
(
    @Procedure1FullName VARCHAR(255),
    @Params1Str VARCHAR(MAX),
    @Procedure2FullName VARCHAR(255),
    @Params2Str VARCHAR(MAX),
    @ResultSetStr VARCHAR(MAX),
    @LoopBackServerName VARCHAR(255) = 'loopback',
    @ForceShowDetails BIT = 0,
    @Debug BIT = 0
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = ''
    DECLARE @InputStr NVARCHAR(MAX)

    -- escaping string parameters
    SET @Params1Str = REPLACE(@Params1Str, '''', '''''')
    SET @Params2Str = REPLACE(@Params2Str, '''', '''''')

    SET @InputStr = @Procedure1FullName + '(' + @Params1Str + ')'

    SET @SQL = '
        DECLARE @StartTime datetime;
        DECLARE @Diff1 BIGINT;
        DECLARE @Diff2 BIGINT;

        -- executing and measuring time for the first procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R1
        FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure1FullName + ' ' + @Params1Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
        SET @Diff1 = DATEDIFF(ms, @StartTime, GETDATE());

        -- executing and measuring time for the second procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R2
        FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure2FullName + ' ' + @Params2Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
        SET @Diff2 = DATEDIFF(ms, @StartTime, GETDATE());

        -- changing all float columns to decimal to ensure correct comparison
        DECLARE @InnerSQL NVARCHAR(MAX) = N''''

        select @InnerSQL += ''alter table #R1 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R1[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        SET @InnerSQL = N'''';
        select @InnerSQL += ''alter table #R2 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R2[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        -- creating temporary tables to hold result sets differences
        SELECT ''R1 \ R2'' AS [R1 \ R2], * INTO #R12 
        FROM #R1 
        WHERE 1 = 0

        SELECT ''R2 \ R1'' AS [R2 \ R1], * INTO #R21
        FROM #R1 
        WHERE 1 = 0

        -- inserting data
        INSERT INTO #R12 
        SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R1 
        EXCEPT
        SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R2;

        INSERT INTO #R21
        SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R2 
        EXCEPT
        SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R1;

        -- difference flag
        DECLARE @IsDiff BIT = 0
        IF EXISTS (SELECT 1 FROM #R12) OR EXISTS (SELECT 1 FROM #R21)
            SET @IsDiff = 1

        SELECT ''' + @InputStr + ''' AS ''' + LEFT(@InputStr, 128) + ''', @IsDiff AS ''Diff results'', ''R1'' AS [R1], @Diff1 AS ''Duration1 [ms]'', @Diff2 AS ''Duration2 [ms]'';

        -- showing details if a difference exists or details must be output
        if (@IsDiff  = 1 OR ' + CAST(@ForceShowDetails AS VARCHAR) + ' = 1)
        BEGIN
            SELECT ''Results for first procedure'' AS ''Results for first procedure'', * FROM #R1;
            SELECT ''Results for second procedure'' AS ''Results from the second procedure'', * FROM #R2;
            SELECT * FROM #R12
            SELECT * FROM #R21
        END
    '

    if (@Debug = 1)
    BEGIN
        PRINT '@SQL = ' + @SQL
        PRINT 'SQL len = ' + CAST(LEN(@SQL) AS VARCHAR(MAX))
    END

    EXEC (@SQL)
END

Exemplo de chamada:

declare @paramsStr VARCHAR(max) = '@year=2014,@month=6'
declare @resultSetStr VARCHAR(MAX) = 'kpi_id INT, kpi_value NUMERIC(18, 2)'
exec uspCompareProcedureResults 
    @Procedure1FullName = '[loopback].[DB].[usr].[get_data]', @Params1Str = @paramsStr, 
    @Procedure2FullName = '[loopback].[DB].[usr].[get_data_next_gen]', @Params2Str = @paramsStr, 
    @ResultSetStr = @resultSetStr, @ForceShowDetails = 0, @Debug = 1
GO 

Limitações/notas/problemas conhecidos:

• Requer um servidor vinculado para apontar para a mesma instância (usado por openquery)
• O procedimento deve retornar apenas um conjunto de resultados
• Todos os floats são convertidos em decimais (números de ponto fixo) para evitar pequenas diferenças de float
• O procedimento pode ser adaptado para executar também no SQL Server 2008 (basta remover WITH RESULTS SETS)

O procedimento faz seu trabalho, mas estou me perguntando: existem opções mais fáceis/melhores de realizar a comparação .