Versão curta

O SQL Server está usando 34 GB de RAM. Mas ao consultar o Relatório de Consumo de Memória , o tamanho do buffer pool e o tamanho da consulta ad-hoc, ele soma apenas cerca de 2 GB. O que os outros 32 GB de RAM estão fazendo?

Preemptivo: "Você deve limitar a quantidade de RAM que o SQL Server pode usar." Vamos dizer que é limitado a x. Isso simplesmente muda minha pergunta para: "O que o outro xGB de RAM está fazendo?"

Versão longa

Eu tenho uma instância do SQL Server que está consumindo 32 GB de RAM:

Isso não é 32 GB de memória virtual ; na verdade está consumindo 32 GB de memória física (nos chips de RAM) - conhecido como "conjunto de trabalho" .

E não é como se fosse compartilhado com algum outro processo. Essencialmente, tudo isso é privado para o SQL Server:

• Conjunto de trabalho privado : 33.896.700 bytes

O que ele está fazendo com toda essa RAM?!

Uso de memória do bufferpool por banco de dados

Portanto, consultamos o uso de memória por banco de dados - pois o pool de buffers armazena em cache as páginas dos bancos de dados:

--Memory usage server wide
;WITH src AS
(
    SELECT
        database_id,
        COUNT_BIG(*) AS db_buffer_pages
    FROM sys.dm_os_buffer_descriptors
    --WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
)
SELECT
    CASE [database_id] WHEN 32767 THEN 'Resource DB' ELSE DB_NAME([database_id]) END AS [Database Name],
    db_buffer_pages AS BufferPages,
    db_buffer_pages /128.0 AS BufferMB
FROM src
ORDER BY db_buffer_pages DESC
OPTION(RECOMPILE, MAXDOP 1);

Para um total geral de 4,5 MB dos 32 GB.

O TempDB usa mais (1,4 MB), e o resto desce a partir daí:

5 MB de 32 GB - não conta muito

Sim, isso pode parecer baixo - mas provavelmente porque eu liguei DBCC DROPCLEANBUFFERSprimeiro.

Cache do plano de consulta

Em seguida, consultamos o Cache do Plano de Consulta . Todas essas instruções T-SQL precisam ser compiladas em um plano enorme, e esses planos são armazenados em cache na RAM.

--Server-wide memory usage of plan cache
SELECT
    [cacheobjtype], ObjType,
    COUNT(1) AS Plans,
    SUM(UseCounts) AS UseCounts,
    SUM(CAST(size_in_bytes AS real)) / 1024.0 / 1024 AS [SizeMB]
FROM sys.dm_exec_cached_plans
--where [cacheobjtype] = 'Compiled Plan' and [objtype] in ('Adhoc', 'Prepared')
GROUP BY CacheObjType, ObjType
ORDER BY SizeMB DESC
OPTION(RECOMPILE, MAXDOP 1)

Agora podemos ver quanta memória é usada para armazenar vários planos de consulta:

Para um total geral de 250 KB - muito aquém dos 32 GB ausentes.

Nota: Sim, isso pode parecer baixo - mas provavelmente porque eu liguei DBCC FREEPROCCACHEprimeiro.

Relatório de consumo de memória

As consultas acima me mostram a RAM usada por:

• o pool de buffers (para armazenar em cache nas páginas do banco de dados de memória do disco)
• o cache do plano de consulta

E isso é realmente tudo o que existe. Mas o SQL Server fornece um relatório de consumo de memória :

Este relatório fornece dados detalhados sobre o consumo de memória dos componentes na instância

Narrador : "Não"

O relatório é um pouco difícil de ler:

Mas no final a divisão é:

• MEMORYCLERK_SOSNODE : 131.832 KB
• MEMORYCLERK_SOSMEMMANAGER : 71.464 KB
• USERSTORE_DBMETADATA : 67.432 KB
• USERSTORE_SCHEMAMGR : 55.784 KB
• MEMORYCLERK_SQLSTORENG : 54.280 KB
• MEMORYCLERK_SQLBUFFERPOOL : 30.576 KB
• Outros : 145.056 KB

Isso dá um total geral de: 556.424 KB → 544 MB

Mesmo se arredondarmos para 1 GB: ainda está muito longe de 32 GB.

Então, para onde vai a memória?

Sim, posso limitar o SQL Server a 25 GB de RAM. Mas isso só mudaria minha pergunta para:

O que é o SQL Server está usando 25 GB de RAM; para onde vai a memória?

Porque isso soa muito como um vazamento de memória para mim.

• Servidor : SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)

Tempo de funcionamento do servidor

Pode consultar o tempo de atividade do servidor (criação de tempdb):

--Use creation date of tempdb as server start time

SELECT SERVERPROPERTY('SERVERNAME') AS ServerName, create_date AS ServerStartedDate FROM sys.databases WHERE NAME='tempdb';

• Data de início do servidor :2021-12-21 15:46:26.730

Montagens CLR

SELECT * FROM sys.assemblies

Servidores vinculados

select provider, provider_string from sys.servers

Eu tenho uma consulta em um servidor que o otimizador estima que terá um custo de 0,01. Na realidade acaba por correr muito mal.

• ele acaba executando uma verificação de índice clusterizado

Nota : Você pode encontrar o ddl, sql, tabelas, etc. aqui no Stackoverflow . Mas essa informação, embora interessante, não é importante aqui - o que é uma questão não relacionada. E essa pergunta nem precisa de DDL.

Se eu forçar o uso de uma busca de índice de cobertura, ele estima que o uso desse índice terá um custo de subárvore de 0,04.

• varredura de índice clusterizado: 0,01
• cobrindo a varredura do índice: 0,04

Portanto, não é de surpreender que o servidor opte por usar o plano que:

• na verdade, causa 147.000 leituras lógicas do índice clusterizado
• em vez das 16 leituras muito mais rápidas de um índice de cobertura

Servidor A:

| Plan                                       | Cost      | I/O Cost    | CPU Cost  |
|--------------------------------------------|-----------|-------------|-----------|
| clustered index scan (optimizer preferred) | 0.0106035 | 116.574     | 5.01949   | Actually run extraordinarily terrible (147k logical reads, 27 seconds)
| covering index seek (force hint)           | 0.048894  |   0.0305324 | 0.0183616 | actually runs very fast (16 logical reads, instant)

Isto é com estatísticas atualizadas COM FULLSCAN nada menos.

Tente em outro servidor

Então eu tento em outro servidor. Recebo estimativas da mesma consulta, com uma cópia recente do banco de dados de produção, também com estatísticas atualizadas (COM FULLSCAN).

• Este outro servidor também é SQL Server 2014
• mas ele percebe corretamente que as varreduras de índice clusterizado são ruins
• e naturalmente prefere a busca do índice de cobertura (porque o custo é 5 ordens de magnitude menor!)

Servidor B :

| Plan                                      | Cost        | I/O Cost   | CPU Cost  |
|-------------------------------------------|-------------|------------|-----------|
| Clustered index scan (force hint)         | 115.661     |   110.889  | 4.77115   | Runs extraordinarily terrible as server A (147k logical reads, 27 seconds)
| Covering index seek (optimizer preferred) |   0.0032831 |   0.003125 | 0.0001581 | Runs fast (16 logical reads, near instant)

O que não consigo descobrir é porque para esses dois servidores, com cópias quase idênticas do banco de dados, ambos com estatísticas atualizadas, ambos SQL Server 2014:

• pode-se executar a consulta tão corretamente
• o outro cai morto

Eu sei que parece um caso clássico de estatísticas desatualizadas. Ou planos de execução em cache ou sniffing de parâmetros. Mas essas consultas de teste estão sendo emitidas com OPTION(RECOMPILE), por exemplo:

SELECT MIN(RowNumber) FROM Transactions
WITH (index=[IX_Transactions_TransactionDate]) WHERE TransactionDate >= '20191002 04:00:00.000' OPTION(RECOMPILE)

Se você olhar de perto, parece que a estimativa do "operador" está errada

A varredura de índice clusterizado é uma coisa ruim. E um dos servidores sabe disso. É uma operação muito cara, e a operação de varredura deve me dizer isso.

Se eu forçar a verificação de índice clusterizado e observar as operações de verificação estimadas em ambos os servidores, algo salta à vista:

| Cost                | Server A    | Server B   |
|---------------------|-------------|------------|
| I/O Cost            | 116.573     | 110.889    |
| CPU Cost            |   5.01945   |   4.77155  |
| Total Operator Cost |   0.0106035 | 115.661    |
                        mistakenly  | avoids it
                          uses it   |

O custo do operador no servidor A é muito baixo.

• o custo de E/S é razoável
• o custo da CPU é razoável
• mas em conjunto, o custo geral do Operador é 4 ordens de magnitude muito baixo.

Isso explica por que está escolhendo erroneamente o plano de execução ruim; simplesmente tem um custo de operador ruim . O servidor B descobriu isso corretamente e evita a verificação de índice clusterizado.

O operador não é = cpu + io?

Em quase todos os nós do plano de execução sobre os quais você passará o mouse e em todas as capturas de tela dos planos de execução no dba, stackoverflow e em todos os blogs, você verá isso sem falhas:

operatorCost >= max(cpuCost, ioCost)

E, na verdade, geralmente é :

operatorCost = cpuCost + ioCost

Então o que está acontecendo aqui?

O que pode explicar o servidor decidir que os custos de 115 + 5 são quase nada e, em vez disso, decide algo 1/10000 desse custo?

Eu sei que o SQL Server tem opções para ajustar o peso interno aplicado às operações de CPU e E/S:

DBCC    TRACEON (3604);     -- Show DBCC output
DBCC    SETCPUWEIGHT(1E0);  -- Default CPU weight
DBCC    SETIOWEIGHT(0.6E0); -- I/O multiplier = 0.6
DBCC    SHOWWEIGHTS;        -- Show the settings

E quando você fizer isso, o custo do operador pode acabar abaixo do custo de CPU + E/S:

Mas ninguém tem brincado com eles. É possível que o SQL Server tenha algum ajuste automático de peso baseado no ambiente, ou baseado em alguma comunicação com o subsistema de disco?

Se o servidor fosse uma máquina virtual, usando um disco SCSI virtual, conectado por um link de fibra a uma Storage Area Network (SAN), ele poderia decidir que os custos de CPU e E/S podem ser ignorados?

Exceto que não pode ser alguma coisa de ambiente permanente neste servidor, porque todas as outras consultas que encontrei se comportam corretamente:

 I/O:       0.0112613
 CPU:      +0.0001
           =0.0113613 (theoretical)
 Operator:  0.0113613 (actual)

O que pode explicar o servidor não tomar:

I/O Cost + Cpu Cost = Operator Cost

corretamente nesta instância ?

SQL Server 2014 SP2.

Eu tenho tentado diagnosticar lentidão em um aplicativo. Para isso, registrei os eventos estendidos do SQL Server .

• Para esta pergunta, estou olhando para um procedimento armazenado específico.
• Mas há um conjunto básico de uma dúzia de procedimentos armazenados que igualmente podem ser usados ​​como uma investigação completa
• e sempre que executo manualmente um dos procedimentos armazenados, ele sempre é executado rapidamente
• e se um usuário tentar novamente: ele será executado rapidamente.

Os tempos de execução do procedimento armazenado variam muito. Muitas das execuções deste procedimento armazenado retornam em < 1s:

E para esse balde "rápido" , é muito menos que 1s. Na verdade, é em torno de 90 ms:

Mas há uma longa cauda de usuários que precisam esperar 2s, 3s, 4s segundos. Alguns têm que esperar 12s, 13s, 14s. Depois, há as almas realmente pobres que têm que esperar 22s, 23s, 24s.

E após 30s, o aplicativo cliente desiste, aborta a consulta e o usuário teve que esperar 30 segundos .

Correlação para encontrar a causa

Então tentei correlacionar:

• duração vs leituras lógicas
• duração vs leituras físicas
• duração vs tempo de CPU

E nenhum parece dar qualquer correlação; nenhum parece ser a causa

• duração vs leituras lógicas : seja um pouco ou muitas leituras lógicas, a duração ainda flutua muito :

  

• duração vs leituras físicas : mesmo que a consulta não tenha sido atendida pelo cache e muitas leituras físicas sejam necessárias, isso não afeta a duração:

  

• duração vs tempo de CPU : se a consulta levou 0s de tempo de CPU ou 2,5s completos de tempo de CPU, as durações têm a mesma variabilidade:

  

Bônus : notei que a duração v leituras físicas e duração v tempo de CPU são muito semelhantes. Isso é comprovado se eu tentar correlacionar o tempo de CPU com leituras físicas:

Acontece que muito uso da CPU vem de E/S. Quem sabia!

Então, se não há nada no ato de executar a consulta que possa explicar as diferenças no tempo de execução, isso significa que é algo não relacionado à CPU ou disco rígido?

Se a CPU ou o disco rígido fossem o gargalo; não seria o gargalo?

Se levantarmos a hipótese de que foi a CPU que foi o gargalo; que a CPU está com pouca energia para este servidor:

• então as execuções usando mais tempo de CPU não levariam mais tempo?
• uma vez que eles têm que completar com os outros usando a CPU sobrecarregada?

Da mesma forma para os discos rígidos. Se levantarmos a hipótese de que o disco rígido era um gargalo; que os discos rígidos não têm taxa de transferência aleatória suficiente para este servidor:

• então as execuções usando mais leituras físicas não levariam mais tempo?
• uma vez que eles têm que completar com outros usando a E/S sobrecarregada do disco rígido?

O procedimento armazenado em si não executa nem requer nenhuma gravação.

• Geralmente retorna 0 linhas (90%).
• Ocasionalmente, retornará 1 linha (7%).
• Raramente retornará 2 linhas (1,4%).
• E nos piores casos, ele retornou mais de 2 linhas (uma vez retornando 12 linhas)

Portanto, não é como se estivesse retornando um volume insano de dados.

Uso da CPU do servidor

A média de uso do processador do servidor é de cerca de 1,8%, com um pico ocasional de até 18% - portanto, não parece que a carga da CPU seja um problema:

Portanto, a CPU do servidor não parece sobrecarregada.

Mas o servidor é virtual...

Algo fora do universo?

A única coisa que posso imaginar é algo que existe fora do universo do servidor.

• se não for lógico lê
• e não são leituras físicas
• e não é uso de CPU
• e não é carga da CPU

E não é como se fossem os parâmetros do procedimento armazenado (porque emitindo a mesma consulta manualmente e não leva 27 segundos - leva ~ 0 segundos).

O que mais poderia explicar o servidor às vezes levando 30 segundos, em vez de 0 segundos, para executar o mesmo procedimento armazenado compilado.

• postos de controle?

É um servidor virtual

• o host sobrecarregado?
• outra VM no mesmo host?

Passando pelos eventos estendidos do servidor; não há mais nada acontecendo quando uma consulta de repente leva 20 segundos. Ele roda bem, então decide não rodar bem:

• 2 segundos
• 1 segundo
• 30 segundos
• 3 segundos
• 2 segundos

E não há outros itens particularmente extenuantes que eu possa encontrar. Não é durante o backup do log de transações a cada 2 horas.

O que mais poderia ser?

Existe algo que eu possa dizer além de: "o servidor" ?

Edit : Correlacione por hora do dia

Percebi que correlacionei as durações a tudo:

• leituras lógicas
• leituras físicas
• utilização do CPU

Mas a única coisa que eu não correlacionava era com a hora do dia . Talvez o backup do log de transações a cada 2 horas seja um problema.

Ou talvez as lentidão ocorram nos mandris durante os pontos de verificação?

Não:

Intel Xeon Gold Quad-core 6142.

Editar - As pessoas estão hipotetizando o plano de execução da consulta

As pessoas estão supondo que os planos de execução de consultas devem ser diferentes entre "rápido" e "lento". Eles não são.

E podemos ver isso imediatamente a partir da inspeção.

Sabemos que a duração mais longa da pergunta não se deve a um plano de execução "ruim":

• um que levou mais leituras lógicas
• um que consumiu mais CPU de mais junções e pesquisas de chave

Because if an increase in reads, or increase in CPU, was a cause of increased query duration, then we would have already seen that above. There is no correlation.

But lets try to correlate duration against the CPU-reads area product metric:

There becomes even less of a correlation - which is a paradox.

Edit: Updated the scatter diagrams to workaround a bug in Excel scatter plots with large numbers of values.

Next Steps

My next steps will be to get someone to have to server generate events for blocked queries - after 5 seconds:

EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE

It won't explain if queries are blocked for 4 seconds. But perhaps anything that's blocking a query for 5 seconds also blocks some for 4 seconds.

The slowplans

Here's the slowplan of the two stored procedures being executed:

• `EXECUTE FindFrob @CustomerID = 7383, @StartDate = '20190725 04:00:00.000', @EndDate = '20190726 04:00:00.000'
• `EXECUTE FindFrob @CustomerID = 7383, @StartDate = '20190725 04:00:00.000', @EndDate = '20190726 04:00:00.000'

The same stored procedure, with the same parameters, run back to back:

| Duration (us) | CPU time (us) | Logical reads | Physical reads | 
|---------------|---------------|---------------|----------------|
|    13,984,446 |        47,000 |         5,110 |            771 |
|     4,603,566 |        47,000 |         5,126 |            740 |

Call 1:

|--Nested Loops(Left Semi Join, OUTER REFERENCES:([Contoso2].[dbo].[Frobs].[FrobGUID]) OPTIMIZED)
    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]))
    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[RowNumber]) OPTIMIZED)
    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[TransactionPatronInfo].[IX_TransactionPatronInfo_CustomerID_TransactionGUID] AS [tpi]), SEEK:([tpi].[CustomerID]=[@CustomerID]) ORDERED FORWARD)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[Transactions].[IX_Transactions_TransactionGUIDTransactionDate]), SEEK:([Contoso2].[dbo].[Transactions].[TransactionGUID]=[Contoso2].[dbo
    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions2_MoneyAppearsOncePerTransaction]), SEEK:([Contoso2].[dbo].[FrobTransactions].[TransactionGUID]=[Contos
    |    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions_RowNumber]), SEEK:([Contoso2].[dbo].[FrobTransactions].[RowNumber]=[Contoso2].[dbo].[Fin
    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[Frobs].[PK_Frobs_FrobGUID]), SEEK:([Contoso2].[dbo].[Frobs].[FrobGUID]=[Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]),  WHERE:([Contos
    |--Filter(WHERE:([Expr1009]>(1)))
     |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1012],0)))
          |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
           |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactins_OnFrobGUID]), SEEK:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]=[Contoso2].[dbo].[Frobs].[LC

Call 2

|--Nested Loops(Left Semi Join, OUTER REFERENCES:([Contoso2].[dbo].[Frobs].[FrobGUID]) OPTIMIZED)
    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]))
    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Contoso2].[dbo].[FrobTransactions].[RowNumber]) OPTIMIZED)
    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([tpi].[TransactionGUID]) OPTIMIZED)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[TransactionPatronInfo].[IX_TransactionPatronInfo_CustomerID_TransactionGUID] AS [tpi]), SEEK:([tpi].[CustomerID]=[@CustomerID]) ORDERED FORWARD)
    |    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[Transactions].[IX_Transactions_TransactionGUIDTransactionDate]), SEEK:([Contoso2].[dbo].[Transactions].[TransactionGUID]=[Contoso2].[dbo
    |    |    |    |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions2_MoneyAppearsOncePerTransaction]), SEEK:([Contoso2].[dbo].[FrobTransactions].[TransactionGUID]=[Contos
    |    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactions_RowNumber]), SEEK:([Contoso2].[dbo].[FrobTransactions].[RowNumber]=[Contoso2].[dbo].[Fin
    |    |--Clustered Index Seek(OBJECT:([Contoso2].[dbo].[Frobs].[PK_Frobs_FrobGUID]), SEEK:([Contoso2].[dbo].[Frobs].[FrobGUID]=[Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]),  WHERE:([Contos
    |--Filter(WHERE:([Expr1009]>(1)))
     |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(int,[Expr1012],0)))
          |--Stream Aggregate(DEFINE:([Expr1012]=Count(*)))
           |--Index Seek(OBJECT:([Contoso2].[dbo].[FrobTransactions].[IX_FrobTransactins_OnFrobGUID]), SEEK:([Contoso2].[dbo].[FrobTransactions].[OnFrobGUID]=[Contoso2].[dbo].[Frobs].[LC

It makes sense for the plans to be identical; it's executing the same stored procedure, with the same parameters.

Dada alguma tabela com uma chave primária, por exemplo:

CREATE TABLE Customers (
   CustomerID int NOT NULL PRIMARY KEY,
   FirstName nvarchar(50),
   LastName nvarchar(50),
   Address nvarchar(200),
   Email nvarchar(260)
   --...
)

temos uma chave primária única em CustomerID.

Tradicionalmente, posso precisar de alguns índices de cobertura adicionais; por exemplo, para encontrar rapidamente um usuário usando CustomerIDou Email:

CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
   CustomerID,
   Email
)

E esses são os tipos de índices que criei por décadas.

Não é necessário ser único, mas na verdade é

O próprio índice existe para evitar uma varredura de tabela; é um índice de cobertura para auxiliar no desempenho (o índice não existe como uma restrição para impor exclusividade).

Hoje lembrei-me de uma pequena informação - o SQL Server pode usar o fato de que:

• uma coluna tem uma restrição de chave estrangeira
• uma coluna tem um índice único
• uma restrição é confiável

para ajudá-lo a otimizar sua execução de consulta. Na verdade, do Guia de design do índice do SQL Server :

Se os dados forem exclusivos e você desejar a exclusividade aplicada, a criação de um índice exclusivo em vez de um índice não exclusivo na mesma combinação de colunas fornece informações adicionais para o otimizador de consulta que pode produzir planos de execução mais eficientes . A criação de um índice exclusivo (de preferência criando uma restrição UNIQUE) é recomendada nesse caso.

Dado que meu índice de várias colunas contém a chave primária, esse índice composto será de fato exclusivo. Não é uma restrição que eu particularmente precise que o SQL Server aplique durante cada inserção ou atualização; mas o fato é que esse índice não agrupado é exclusivo.

Existe alguma vantagem em marcar esse índice único de fato como realmente único?

CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Clientes
(
   Identificação do Cliente,
   E-mail
)

Parece-me que o SQL Server pode ser inteligente o suficiente para perceber que meu índice já é exclusivo pelo fato de conter a chave primária.

• Mas talvez ele não saiba disso, e há uma vantagem para o otimizador se eu declarar o índice como único de qualquer maneira.
• Exceto, talvez, que agora possa levar a lentidão durante inserções e atualizações, onde deve executar verificações de exclusividade - onde antes nunca precisava antes.
• A menos que saiba que o índice já é exclusivo , porque contém a chave primária.

Não consigo encontrar nenhuma orientação da Microsoft sobre o que fazer quando um índice composto contém a chave primária.

Os benefícios dos índices exclusivos incluem o seguinte:

• A integridade dos dados das colunas definidas é garantida.
• Informações adicionais úteis para o otimizador de consulta são fornecidas.

Devo marcar um índice composto como único se ele já contiver a chave primária? Ou o SQL Server pode descobrir isso sozinho?

Tivemos problemas, durante alta simultaneidade, de consultas retornando resultados sem sentido - resultados que violam a lógica das consultas sendo emitidas. Demorou um pouco para reproduzir o problema. Consegui reduzir o problema reproduzível a alguns punhados de T-SQL.

Observação : a parte do sistema ativo com o problema é composta por 5 tabelas, 4 gatilhos, 2 procedimentos armazenados e 2 exibições. Simplifiquei o sistema real em algo muito mais gerenciável para uma pergunta postada. Coisas foram reduzidas, colunas removidas, procedimentos armazenados em linha, exibições transformadas em expressões de tabela comuns, valores de colunas alterados. Isso tudo é um longo caminho para dizer que, embora o que se segue reproduza um erro, pode ser mais difícil de entender. Você terá que se abster de se perguntar por que algo está estruturado do jeito que está. Estou aqui tentando descobrir por que a condição de erro ocorre de forma reproduzível neste modelo de brinquedo.

/*
The idea in this system is that people are able to take days off. 
We create a table to hold these *"allocations"*, 
and declare sample data that only **1** production operator 
is allowed to take time off:
*/
IF OBJECT_ID('Allocations') IS NOT NULL DROP TABLE Allocations
CREATE TABLE [dbo].[Allocations](
    JobName varchar(50) PRIMARY KEY NOT NULL,
    Available int NOT NULL
)
--Sample allocation; there is 1 avaialable slot for this job
INSERT INTO Allocations(JobName, Available)
VALUES ('Production Operator', 1);

/*
Then we open up the system to the world, and everyone puts in for time. 
We store these requests for time off as *"transactions"*. 
Two production operators requested time off. 
We create sample data, and note that one of the users 
created their transaction first (by earlier CreatedDate):
*/
IF OBJECT_ID('Transactions') IS NOT NULL DROP TABLE Transactions;
CREATE TABLE [dbo].[Transactions](
    TransactionID int NOT NULL PRIMARY KEY CLUSTERED,
    JobName varchar(50) NOT NULL,
    ApprovalStatus varchar(50) NOT NULL,
    CreatedDate datetime NOT NULL
)
--Two sample transactions
INSERT INTO Transactions (TransactionID, JobName, ApprovalStatus, CreatedDate)
VALUES (52625, 'Production Operator', 'Booked', '20140125 12:00:40.820');
INSERT INTO Transactions (TransactionID, JobName, ApprovalStatus, CreatedDate)
VALUES (60981, 'Production Operator', 'WaitingList', '20150125 12:19:44.717');

/*
The allocation, and two sample transactions are now in the database:
*/
--Show the sample data
SELECT * FROM Allocations
SELECT * FROM Transactions

As transações são inseridas como arquivos WaitingList. Em seguida, temos uma tarefa periódica que é executada, procurando slots vazios e coloca qualquer pessoa na WaitingList em um status Booked.

Em uma janela SSMS separada, temos o procedimento armazenado recorrente simulado:

/*
    Simulate recurring task that looks for empty slots, 
    and bumps someone on the waiting list into that slot.
*/
SET NOCOUNT ON;

--Reset the faulty row so we can continue testing
UPDATE Transactions SET ApprovalStatus = 'WaitingList'
WHERE TransactionID = 60981

--DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

DECLARE @attempts int
SET @attempts = 0;

WHILE (@attempts < 1000000)
BEGIN
    SET @attempts = @attempts+1;

    /*
        The concept is that if someone is already "Booked", then they occupy an available slot.
        We compare the configured amount of allocations (e.g. 1) to how many slots are used.
        If there are any slots leftover, then find the **earliest** created transaction that 
        is currently on the WaitingList, and set them to Booked.
    */

    PRINT '=== Looking for someone to bump ==='
    WITH AvailableAllocations AS (
        SELECT 
            a.JobName,
            a.Available AS Allocations, 
            ISNULL(Booked.BookedCount, 0) AS BookedCount, 
            a.Available-ISNULL(Booked.BookedCount, 0) AS Available
        FROM Allocations a
            FULL OUTER JOIN (
                SELECT t.JobName, COUNT(*) AS BookedCount
                FROM Transactions t
                WHERE t.ApprovalStatus IN ('Booked') 
                GROUP BY t.JobName
            ) Booked
            ON a.JobName = Booked.JobName
        WHERE a.Available > 0
    )
    UPDATE Transactions SET ApprovalStatus = 'Booked'
    WHERE TransactionID = (
        SELECT TOP 1 t.TransactionID
        FROM AvailableAllocations aa
            INNER JOIN Transactions t
            ON aa.JobName = t.JobName
            AND t.ApprovalStatus = 'WaitingList'
        WHERE aa.Available > 0
        ORDER BY t.CreatedDate 
    )


    IF EXISTS(SELECT * FROM Transactions WHERE TransactionID = 60981 AND ApprovalStatus = 'Booked')
    begin
        --DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS
        RAISERROR('The later tranasction, that should never be booked, managed to get booked!', 16, 1)
        BREAK;
    END
END

E, finalmente, execute isso em uma terceira janela de conexão do SSMS. Isso simula um problema de simultaneidade em que a transação anterior deixa de ocupar um slot e fica na lista de espera:

/*
    Toggle the earlier transaction back to "WaitingList".
    This means there are two possibilies:
       a) the transaction is "Booked", meaning no slots are available. 
          Therefore nobody should get bumped into "Booked"
       b) the transaction is "WaitingList", 
          meaning 1 slot is open and both tranasctions are "WaitingList"
          The earliest transaction should then get "Booked" into the slot.

    There is no time when there is an open slot where the 
    first transaction shouldn't be the one to get it - he got there first.
*/
SET NOCOUNT ON;

--Reset the faulty row so we can continue testing
UPDATE Transactions SET ApprovalStatus = 'WaitingList'
WHERE TransactionID = 60981

DECLARE @attempts int
SET @attempts = 0;

WHILE (@attempts < 100000)
BEGIN
    SET @attempts = @attempts+1

    /*Flip the earlier transaction from Booked back to WaitingList
        Because it's now on the waiting list -> there is a free slot.
        Because there is a free slot -> a transaction can be booked.
        Because this is the earlier transaction -> it should always be chosen to be booked
    */
    --DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

    PRINT '=== Putting the earlier created transaction on the waiting list ==='

    UPDATE Transactions
    SET ApprovalStatus = 'WaitingList'
    WHERE TransactionID = 52625

    --DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS

    IF EXISTS(SELECT * FROM Transactions WHERE TransactionID = 60981 AND ApprovalStatus = 'Booked')
    begin
        RAISERROR('The later tranasction, that should never be booked, managed to get booked!', 16, 1)
        BREAK;
    END
END

Conceitualmente, o procedimento de colisão continua procurando por slots vazios. Se encontrar uma, pega a primeira transação que está no WaitingListe a marca como Booked.

Quando testado sem simultaneidade, a lógica funciona. Temos duas transações:

• 12h00: Lista de Espera
• 12h20: Lista de Espera

Há 1 alocação e 0 transações reservadas, então marcamos a transação anterior como reservada:

• 12:00: Reservado
• 12h20: Lista de Espera

Na próxima vez que a tarefa for executada, haverá 1 slot sendo ocupado - portanto, não há nada para atualizar.

Se atualizarmos a primeira transação e colocá-la no WaitingList:

UPDATE Transactions SET ApprovalStatus='WaitingList'
WHERE TransactionID = 60981

Então estamos de volta onde começamos:

• 12h00: Lista de Espera
• 12h20: Lista de Espera

Observação : você deve estar se perguntando por que estou colocando uma transação de volta na lista de espera. Isso é uma vítima do modelo de brinquedo simplificado. No sistema real, as transações podem ser PendingApproval, que também ocupam um slot. Uma transação PendingApproval é colocada na lista de espera quando é aprovada. Não importa. Não se preocupe com isso.

Mas quando eu introduzo a simultaneidade, por ter uma segunda janela constantemente colocando a primeira transação de volta na lista de espera após ser reservada, a transação posterior conseguiu obter a reserva:

• 12h00: Lista de Espera
• 12h20: Reservado

Os scripts de teste de brinquedo detectam isso e param de iterar:

Msg 50000, Level 16, State 1, Line 41
The later tranasction, that should never be booked, managed to get booked!

Por quê?

A questão é: por que nesse modelo de brinquedo essa condição de salvamento está sendo acionada?

Existem dois estados possíveis para o status de aprovação da primeira transação:

• Reservado : caso em que o slot está ocupado e a transação posterior não pode tê-lo
• WaitingList : nesse caso, há um slot vazio e duas transações que o desejam. Mas como sempre temos selecta transação mais antigaORDER BY CreatedDate (ou seja, ) a primeira transação deve obtê-la.

Eu pensei que talvez por causa de outros índices

Aprendi que após iniciar um UPDATE e alterar os dados , é possível ler os valores antigos. Nas condições iniciais:

• Índice agrupado :Booked
• Índice não agrupado :Booked

Em seguida, faço uma atualização e, embora o nó folha do índice clusterizado tenha sido modificado, todos os índices não clusterizados ainda contêm o valor original e ainda estão disponíveis para leitura:

• Índice agrupado (Bloqueio exclusivo):Booked WaitingList
• Índice não clusterizado : (desbloqueado)Booked

Mas isso não explica o problema observado. Sim, a transação não está mais reservada , o que significa que agora há um slot vazio. Mas essa mudança ainda não foi confirmada, ainda é realizada exclusivamente. Se o procedimento de colisão fosse executado, seria:

• bloco: se a opção de banco de dados de isolamento de instantâneo estiver desativada
• leia o valor antigo (por exemplo Booked): se o isolamento de instantâneo estiver ativado

De qualquer maneira, o trabalho de colisão não saberia que há um slot vazio.

Então eu não tenho ideia

Estamos lutando há dias para descobrir como esses resultados absurdos podem acontecer.

Você pode não entender o sistema original, mas há um conjunto de scripts reprodutíveis de brinquedo. Eles salvam quando o caso inválido é detectado. Por que está sendo detectado? Por que isso está acontecendo?

Pergunta bônus

Como a NASDAQ resolve isso? Como funciona o cavirtex? Como funciona o mtgox?

tl;dr

Há três blocos de script. Coloque-os em 3 guias separadas do SSMS e execute-os. O 2º e 3º scripts gerarão um erro. Ajude-me a descobrir por que o erro aparece.

Estou tentando resolver um problema com o SQL Server 2008 R2 e estou me agarrando às palhas.

Dado que os dados estão alojados em nós de folha da árvore B e também existem em índices, alguém poderia ler vários valores durante uma única UPDATEinstrução?

Imagine que um processo atualize uma linha:

--Change status from Pending -> Waiting
BEGIN TRANSACTION
   UPDATE Transactions SET Status = 'Waiting'
   WHERE TransactionID = 12345
COMMIT TRANSACTION

É possível que durante esse BEGIN TRANS; UPDATE; COMMITprocesso outro processo possa ler valores novos e antigos?

Eu me pergunto isso porque a atualização não altera um valor em apenas um lugar. Esse valor existirá em vários lugares:

• nós folha do índice clusterizado
• IX_Transações_1
• IX_Transações_2
• IX_Transações_3
• IX_Transações_4
• IX_Transações_5
• ...
• IX_Transações_n

Se a atualização começar, ela deverá atualizar os valores em todos esses locais. O que acontece se outro processo usar um plano de execução de índice para encontrar o valor:

• IX_Clustered : Aguardando Pendente
• IX_Transactions_1 : Pendente Aguardando
• IX_Transactions_2 : Pendente Aguardando
• IX_Transactions_3 : Pendente
• IX_Transactions_4 : Pendente
• IX_Transactions_5 : Pendente
• ...
• IX_Transactions_n : Pendente

Nesse instante, se for emitida uma consulta que examine o valor no índice clusterizado, ela encontrará Waiting .

Mas se uma consulta usar IX_Transactions_4, ela encontrará um valor de Pending.

Desdocumentando o mecanismo de bloqueio

A ordem de bloqueio internamente não é documentada, mas presumo que o SQL Server tenha um bloqueio compartilhado nos índices clusterizados e não clusterizados:

• IX_Clustered : Compartilhado
• IX_Transactions_1 : Compartilhado
• IX_Transactions_2 : Compartilhado
• IX_Transactions_3 : Compartilhado
• IX_Transactions_4 : Compartilhado
• IX_Transactions_5 : Compartilhado

Em seguida, atualiza esses bloqueios para bloqueios de atualização:

• IX_Clustered : atualização compartilhada
• IX_Transactions_1 : atualização compartilhada
• IX_Transactions_2 : atualização compartilhada
• IX_Transactions_3 : atualização compartilhada
• IX_Transactions_4 : atualização compartilhada
• IX_Transactions_5 : atualização compartilhada

neste ponto, outro processo ainda pode ler os valores desses 5 índices; porque os selects são compatíveis com os bloqueios de atualização.

Em seguida, a atualização continua. Se tirarmos uma foto em um instante no tempo:

• IX_Clustered : atualização compartilhada exclusiva -> valor alterado
• IX_Transactions_1 : atualização compartilhada exclusiva -> valor alterado
• IX_Transactions_2 : atualização compartilhada exclusiva
• IX_Transactions_3 : atualização compartilhada
• IX_Transactions_4 : atualização compartilhada
• IX_Transactions_5 : atualização compartilhada

Outro processo não pode mais ler valores de itens com um bloqueio exclusivo , mas ainda pode ler valores de itens que ainda possuem o bloqueio de atualização (os bloqueios compartilhados são compatíveis com os bloqueios de atualização )

A menos é claro que não é isso que acontece

Isso só funciona se o SQL promover apenas para exclusivo conforme necessário. Se, em vez disso, for:

• IX_Clustered : S U IX X -> valor alterado
• IX_Transactions_1 : S U IX X -> valor alterado
• IX_Transactions_2 : S U IX X
• IX_Transactions_3 : S U IX
• IX_Transactions_4 : S U IX
• IX_Transactions_5 : S U IX

E não consigo mais digitar; minha vontade de continuar é esmagada. Como eu disse, estou me agarrando em palhas.