Início / dba / Perguntas / 207422
Accepted
Forrest
Asked: 2018-05-22 14:21:59 +0800 CST

Por que o NOLOCK faz uma varredura com atribuição de variável mais lenta?

  • 772

Estou lutando contra o NOLOCK no meu ambiente atual. Um argumento que ouvi é que a sobrecarga do bloqueio diminui a velocidade de uma consulta. Então, eu criei um teste para ver o quanto essa sobrecarga pode ser.

Descobri que o NOLOCK realmente diminui a velocidade da minha varredura.

No começo eu estava encantado, mas agora estou apenas confuso. Meu teste é inválido de alguma forma? O NOLOCK não deveria permitir uma varredura um pouco mais rápida? O que está acontecendo aqui?

Aqui está meu roteiro:

USE TestDB
GO

--Create a five-million row table
DROP TABLE IF EXISTS dbo.JustAnotherTable
GO

CREATE TABLE dbo.JustAnotherTable (
ID INT IDENTITY PRIMARY KEY,
notID CHAR(5) NOT NULL )

INSERT dbo.JustAnotherTable
SELECT TOP 5000000 'datas'
FROM sys.all_objects a1
CROSS JOIN sys.all_objects a2
CROSS JOIN sys.all_objects a3

/********************************************/
-----Testing. Run each multiple times--------
/********************************************/
--How fast is a plain select? (I get about 587ms)
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID  --trash variable prevents any slowdown from returning data to SSMS
FROM dbo.JustAnotherTable
ORDER BY ID
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

----------------------------------------------
--Now how fast is it with NOLOCK? About 640ms for me
DECLARE @trash CHAR(5), @dt DATETIME = SYSDATETIME()

SELECT @trash = notID
FROM dbo.JustAnotherTable (NOLOCK)
ORDER BY ID --would be an allocation order scan without this, breaking the comparison
OPTION (MAXDOP 1)

SELECT DATEDIFF(MILLISECOND,@dt,SYSDATETIME())

O que eu tentei que não funcionou:

  • Executando em servidores diferentes (mesmos resultados, os servidores eram 2016-SP1 e 2016-SP2, ambos silenciosos)
  • Executando em dbfiddle.uk em versões diferentes (ruidoso, mas provavelmente os mesmos resultados)
  • SET ISOLATION LEVEL em vez de dicas (mesmos resultados)
  • Desativando o escalonamento de bloqueio na tabela (mesmos resultados)
  • Examinando o tempo de execução real da verificação no plano de consulta real (mesmos resultados)
  • Dica de recompilação (mesmos resultados)
  • Grupo de arquivos somente leitura (mesmos resultados)

A exploração mais promissora vem da remoção da variável trash e do uso de uma consulta sem resultados. Inicialmente, isso mostrou o NOLOCK um pouco mais rápido, mas quando mostrei a demo ao meu chefe, o NOLOCK voltou a ser mais lento.

O que há no NOLOCK que retarda uma varredura com atribuição de variável?

sql-server sql-server-2016

1 respostas

  1. Best Answer

    NOTA: este pode não ser o tipo de resposta que você está procurando. Mas talvez seja útil para outros respondentes em potencial, fornecendo pistas sobre onde começar a procurar

    Quando executo essas consultas no rastreamento ETW (usando PerfView), recebo os seguintes resultados:

    Plain  - 608 ms  
NOLOCK - 659 ms

    Portanto, a diferença é de 51ms . Isso é bastante morto com sua diferença (~ 50ms). Meus números são um pouco maiores no geral por causa da sobrecarga de amostragem do criador de perfil.

    Encontrando a diferença

    Aqui está uma comparação lado a lado mostrando que a diferença de 51ms está no FetchNextRowmétodo em sqlmin.dll:

    Buscar próxima linha

    A seleção simples está à esquerda em 332 ms, enquanto a versão nolock está à direita em 383 ( 51 ms a mais). Você também pode ver que os dois caminhos de código diferem desta maneira:

    • AviãoSELECT

      • sqlmin!RowsetNewSS::FetchNextRowchamadas
        • sqlmin!IndexDataSetSession::GetNextRowValuesInternal

    • UsandoNOLOCK

      • sqlmin!RowsetNewSS::FetchNextRowchamadas
        • sqlmin!DatasetSession::GetNextRowValuesNoLockque chama ou
          • sqlmin!IndexDataSetSession::GetNextRowValuesInternalou
          • kernel32!TlsGetValue

    Isso mostra que há alguma ramificação no FetchNextRowmétodo com base no nível de isolamento/dica nolock.

    Por que o NOLOCKramo demora mais?

    A ramificação nolock na verdade gasta menos tempo chamando GetNextRowValuesInternal(25ms a menos). Mas o código diretamente GetNextRowValuesNoLock(sem incluir os métodos que ele chama de coluna "Exc") é executado por 63ms - que é a maior parte da diferença (63 - 25 = aumento líquido de 38ms no tempo de CPU).

    Então, quais são os outros 13ms (total de 51ms - 38ms contabilizados até agora) de sobrecarga em FetchNextRow?

    Despacho de interface

    Eu pensei que isso era mais uma curiosidade do que qualquer coisa, mas a versão nolock parece incorrer em alguma sobrecarga de despacho de interface chamando o método da API do Windows kernel32!TlsGetValuevia kernel32!TlsGetValueStub- um total de 17ms. A seleção simples parece não passar pela interface, então nunca atinge o stub, e gasta apenas 6ms TlsGetValue(uma diferença de 11ms ). Você pode ver isso acima na primeira captura de tela.

    Eu provavelmente deveria executar esse rastreamento novamente com mais iterações da consulta, acho que existem algumas pequenas coisas, como interrupções de hardware, que não foram detectadas pela taxa de amostragem de 1 ms do PerfView

    Fora desse método, notei outra pequena diferença que faz com que a versão nolock fique mais lenta:

    Liberando Bloqueios

    A ramificação nolock parece executar o sqlmin!RowsetNewSS::ReleaseRowsmétodo de forma mais agressiva, que você pode ver nesta captura de tela:

    Liberando bloqueios

    A seleção simples está na parte superior, a 12ms, enquanto a versão nolock está na parte inferior a 26ms (14ms a mais ). Você também pode ver na coluna "Quando" que o código foi executado com mais frequência durante o exemplo. Este pode ser um detalhe de implementação do nolock, mas parece introduzir um pouco de sobrecarga para pequenas amostras.

    Existem muitas outras pequenas diferenças, mas esses são os grandes pedaços.

    • 14

relate perguntas