CORREÇÃO: Problemas de desempenho ocorrem quando aumenta a atividade de bloqueio do banco de dados do SQL Server

Sintomas

Suponha que você execute uma instância do Microsoft SQL Server 2014, SQL Server 2012, SQL Server 2008 ou SQL Server 2008 R2 em um computador com vários processadores. Quando o número de bloqueios (tipo de recurso = banco de dados) para um determinado banco de dados excede um determinado limite, os seguintes problemas de desempenho:

• Valores elevados ocorram para contagem de spinlock LOCK_HASH .
  
  Observação: Consulte a seção "Mais informações" para obter informações sobre como monitorar esse spinlock.

• Consultas ou operações que requerem bloqueios de banco de dados demoram muito tempo para ser concluída. Por exemplo, você pode observar os atrasos de desempenho a seguir:

  • Logons do SQL Server

  • Consultas de servidor vinculado

  • sp_reset_connection

  • Transações

Observação: Para localizar a lista de bloqueios (tipo de recurso = banco de dados) em um determinado banco de dados, consulte a seção "Mais informações". O valor de limiar varia de acordo com o ambiente.

Resolução

Informações sobre o hotfix
Um hotfix compatível foi disponibilizado pela Microsoft. No entanto, esse hotfix destina-se a corrigir somente o problema descrito neste artigo. Aplique este hotfix somente aos sistemas que apresentarem esse problema específico.

Se o hotfix estiver disponível para download, há uma seção "Download de Hotfix disponível" na parte superior deste artigo da Base de Conhecimento. Se essa seção não for exibida, envie uma solicitação ao suporte e atendimento ao cliente Microsoft para obter o hotfix.

Observação: Se ocorrerem problemas adicionais ou qualquer solução de problemas é necessária, você terá que criar uma solicitação de serviço separada. Os custos normais de suporte serão aplicadas a perguntas de suporte adicionais e problemas que não se qualificam para esse hotfix específico. Para obter uma lista completa dos números de telefone do Atendimento Microsoft e suporte ou para criar uma solicitação de serviço separada, visite o seguinte site da Microsoft:

http://support.microsoft.com/contactus/?ws=supportObservação: "Download de Hotfix disponível" exibe os idiomas para os quais o hotfix está disponível. Caso você não veja seu idioma, é porque um hotfix não está disponível para esse idioma.

Status

A Microsoft confirma que este é um problema em seus produtos listados na seção "Aplica-se a".

Mais informações

Quando um aplicativo faz uma conexão para SQL Server, ele primeiro estabelece um contexto de banco de dados. Por padrão, a conexão tentará obter um bloqueio de banco de dados no modo SH. O bloqueio do Banco de dados SH será lançado quando a conexão é interrompida ou contexto de banco de dados for alterado durante o ciclo de vida da conexão. Se você tiver várias conexões ativas que usam o mesmo contexto de banco de dados, você pode ter muitos bloqueios do tipo de recurso de banco de dados para o banco de dados específico.

No computador que tenha 16 ou mais CPUs, somente objetos da tabela usam um esquema de bloqueio particionado. No entanto, os bloqueios de banco de dados não são particionados. Portanto, quanto maior o número de banco de dados bloqueia, mais tempo levará para SQL Server ao obter um bloqueio no banco de dados. A maioria dos aplicativos não tem quaisquer problemas causados por esse design. Mas, assim como o número exceder um determinado limite, tempo e trabalho adicional é necessário para obter o bloqueio. Embora o custo seja apenas micro segundos para cada bloqueio adicional, o tempo total pode aumentar rapidamente, como bloqueio hash buckets são protegidos por meio de um spinlock. Isso faz com que os ciclos de CPU adicionais e aguarda para trabalhadores adicionais ao obter o bloqueio.

Esse hotfix introduz o particionamento de bloqueio de banco de dados quando o sinalizador de rastreamento T1236 está habilitado na inicialização. Particionar o bloqueio do banco de dados mantém a profundidade da lista de bloqueio gerenciável em cada partição local. Isso otimiza consideravelmente o caminho de acesso é usado para obter um bloqueio de banco de dados .

Para monitorar o spinlock LOCK_HASH , você pode usar a consulta a seguir.SET NOCOUNT ONCREATE TABLE #spinlock_stats([CaptureTime] datetime,[name] nvarchar(512),[collisions] bigint,
[spins] bigint,[spins_per_collision] real,[sleep_time] bigint,[backoffs] int)
DECLARE @counter int = 1
WHILE @counter < 100
BEGIN
INSERT INTO #spinlock_stats SELECT GETDATE() as "CaptureTime" , * FROM sys.dm_os_spinlock_stats WHERE [name] = 'LOCK_HASH'
WAITFOR DELAY '00:00:05'
SET @counter +=1
END
SELECT * FROM #spinlock_stats ORDER BY [CaptureTime]
DROP TABLE #spinlock_stats
Para obter mais informações sobre o diagnóstico e resolução de contenção de spinlock no SQL Server, consulte o seguinte documento:

Diagnosticar e resolver Spinlock contenção no SQL ServerObservação: Embora este documento foi escrito para o SQL Server 2008 R2, as informações são ainda se aplicam ao SQL Server 2012.

Referências

Para obter mais informações sobre sinalizadores de rastreamento no SQL Server 2012, acesse o seguinte site do TechNet:

Informações sobre sinalizadores de rastreamento no SQL Server 2012
Para obter mais informações sobre como encontrar o número de bloqueios de banco de dados do usuário por banco de dados, use a seguinte consulta para calcular esse valor:select Resource_database_id, resource_type, request_mode, request_status, count (*) 'LockCount' from sys.dm_tran_locks
group by Resource_database_id, resource_type, request_mode, request_status