James's questions

No último sábado à noite, nosso site ficou fora do ar por cerca de 10 minutos. A análise dos logs mostrou os seguintes erros no SQL Server principal (em um par espelhado), durante a interrupção e não fora da interrupção:

• 06/06/2015 23:14:41,spid[vários],Desconhecido,Tempo esgotado durante a espera da trava: class 'ACCESS_METHODS_DATASET_PARENT' id [vários] tipo 4 Tarefa [vários]: [vários] tempo de espera 300 sinalizadores 0x1a possuindo tarefa [ vários]. Continuando a espera.
• 06/06/2015 23:06:54,spid19s,Desconhecido,Tempo esgotado durante a espera pelo tipo de trava de buffer 3 para a página (5:159076157) ID do banco de dados 6.
• 06/06/2015 23:06:54,spid19s,Desconhecido,Erro: 845 Gravidade: 17 Estado: 1.
• 06/06/2015 23:06:54,spid19s,Desconhecido, Ocorreu um tempo limite durante a espera pela trava do buffer - digite 3 bp 0000000518FA1200 página 5:159076157 stat 0xc0000b ID do banco de dados: 6 ID da unidade de alocação: 72057793340899328 tarefa 0x00000048: 0x0000004 tempo de espera 300 sinalizadores 0x100000001a possuindo tarefa 0x0000000004472BC8. Não continuar esperando.

Houve mais de uma centena do primeiro erro, principalmente antes, mas também depois dos outros, que ocorreram apenas uma vez. Esses erros ocorreram cerca de 2 horas depois de fazer um failover, aplicar atualizações do sistema operacional e retornar ao servidor original. Estamos executando nesses servidores há cerca de 2 anos e nunca vimos esse problema. O software que chama os servidores foi atualizado mais recentemente na tarde de quinta-feira (cerca de 55 horas antes da interrupção).

Estou encontrando muito pouca informação aqui ou no google sobre esse timeout. A coisa mais próxima que encontrei é a segunda resposta a esta pergunta: https://stackoverflow.com/questions/3149310/time-out-occurred-while-waiting-for-buffer-latch-type-2-error-in -sql-server , que fala sobre o erro de trava tipo 4 relacionado ao tempdb e causado por um bug em 2008, mas esse bug foi resolvido em 2009, antes do lançamento de 2008 R2. A versão exata relata como:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)    
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

Os servidores usam SSDs Intel Enterprise espelhados para tudo (matrizes RAID 10 duplas para os dados) e nenhuma das unidades está relatando problemas. O volume tempdb tem 200 GB livres. Existem 20 arquivos tempdb (é um servidor BIG). O tráfego SQL caiu significativamente em relação ao pico de um ano atrás devido a otimizações no esquema, procedimentos armazenados e o software que chama o SQL, então provavelmente não é um problema relacionado ao carregamento.

O tipo 4 está definitivamente relacionado ao tempdb, como muitas postagens parecem indicar (o banco de dados 6 mencionado na mensagem de erro é nosso banco de dados principal, não o tempdb)? O que posso fazer para evitar que esse problema volte a acontecer?

Eu sei que o SQL Server 2012 e 2014 oferecem grupos de disponibilidade AlwaysOn que fornecem essa funcionalidade, mas ainda estou preso no SQL Server 2008 R2 por um tempo.

Recentemente, encontrei este whitepaper da AWS: http://media.amazonwebservices.com/AWS_RDBMS_MS_SQLServer.pdf

Fiquei confuso com este gráfico na página 11: o que parece indicar que o banco de dados espelho pode ser usado para leitura (verifique na coluna do recurso 'Cópia legível'), assim como pode ao usar Log Shipping, porque tem sido minha experiência que o banco de dados espelho não pode ser usado de forma alguma - seu único propósito é para failover.

A resposta a esta pergunta: O espelhamento de banco de dados é limitado a um banco de dados original parece confirmar minhas suspeitas de que o banco de dados espelho NÃO é legível, embora apenas de passagem.

Este white paper está errado ou os bancos de dados espelhados do SQL Server 2008 R2 são realmente legíveis?

Se estiverem, o que é preciso fazer para fazer isso, pois a tentativa de conexão no SSMS resulta em uma janela conectada ao banco de dados mestre e a execução USE [database]dá o seguinte erro:

Msg 954, Level 14, State 1, Line 1
The database "database" cannot be opened. It is acting as a mirror database.

ATUALIZAÇÃO: Entendo que existem maneiras de contornar esse problema e obter algo derivado do espelho e com atraso de tempo que seja legível, e suponho que alguém possa argumentar que um instantâneo é uma "Cópia legível", mas isso é muito diferente do tipo de cópia legível que você obteria por meio de envio de log ou replicação de transação, já que ambos seriam atualizados automaticamente, mesmo que ligeiramente desatualizados (talvez os instantâneos também possam estar - estou preso no padrão Edition , então não estou tão familiarizado com o conjunto de recursos Enterprise Edition ). Além disso, um espelho síncrono verdadeiramente legível (como usar Grupos de Disponibilidade AlwaysOnem 2012+) forneceria uma versão legível perfeitamente sincronizada - o que é MUITO mais útil porque o espelho pode ser usado para distribuir a carga de consulta de leitura e evitar todos os problemas com o retorno de dados desatualizados. Meu objetivo principal (conforme declarado na pergunta acima) era realmente obter uma resposta definitiva sobre a legibilidade do próprio espelho (não um instantâneo ou uma réplica dele). Embora a resposta fornecida esteja de acordo com minha própria experiência, os únicos links para fontes definitivas fornecidas são para possíveis maneiras de obter resultados semelhantes - nenhum deles afirma definitivamente que os espelhos não são legíveis. Aceitarei a primeira resposta que fornecer esse link.

Eu tenho um problema onde estou vendo algumas esperas de bloqueio muito estranhas, com o processo no topo da árvore de espera de bloqueio no estado 'dormindo' 'AWAITING COMMAND'. Posso ver o nome da máquina e o aplicativo e tenho certeza de que é uma transação do lado do cliente bloqueada no lado do cliente depois de bloquear alguns registros que está causando o problema, mas não consegui descobrir por meio do exame de código o que poderia estar fazendo. Já descartei o GC, pois a mesma aplicação está respondendo a outras requisições (temos uma requisição para despejar o status de todas as requisições pendentes, cada uma com um ID único, e que retornaram bem enquanto o bloqueio permaneceu). No momento em que somos notificados sobre esse problema, o backup da solicitação é de vários milhares por máquina cliente SQL,

Gostaria de adicionar algumas informações às informações de conexão SQL para rastrear qual solicitação está no topo, não apenas qual máquina e aplicativo (e acho que isso também pode ser útil para diagnosticar problemas futuros). Eu poderia acrescentar algo ao nome do aplicativo na string de conexão, mas acho que isso provavelmente prejudicaria o pool de conexões.

Eu li esta postagem: Posso definir o valor de App_Name () DEPOIS do login? e este: http://www.sqlservercentral.com/articles/T-SQL/2765/ que parecem indicar que eu poderia usar SET CONTEXT_INFOpara obter dados sys.dm_exec_requests.context_infoe então eu poderia usar esses dados para identificar o solicitação exata de onde vem a transação inválida.

Não estamos usando SET CONTEXT_INFOpara mais nada na aplicação (e os servidores SQL são dedicados a esta aplicação).

Existe algum motivo pelo qual eu não gostaria de fazer a identificação de solicitação dessa maneira ou alguma maneira melhor de rastrear solicitações até a camada SQL?

O problema real envolve muito mais dados e junções, mas criei uma pequena amostra para demonstrar o problema:

-- create example table
DROP TABLE dbo.EventRecords
GO
CREATE TABLE dbo.EventRecords
    (
    EventDate datetime NOT NULL,
    EventCount int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.EventRecords ADD CONSTRAINT
    PK_EventRecords PRIMARY KEY CLUSTERED 
    (
    EventDate,
    EventCount
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
-- put in some random data for example
DECLARE @Counter INT=0
WHILE (@Counter<1000)
BEGIN
    DECLARE @SemiRandomCount1 INT=@Counter*589043%23
    DECLARE @SemiRandomCount2 INT=@Counter*85907%7
    IF @SemiRandomCount1>0 AND @Counter%7<>0    -- leave some dates empty
    BEGIN
        INSERT INTO dbo.EventRecords(EventDate,EventCount)
        VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount1)
        PRINT CAST(@SemiRandomCount2 AS VARCHAR(MAX))
        IF @SemiRandomCount2>0 AND @Counter%2=0 -- some dates have multiple entries
            INSERT INTO dbo.EventRecords(EventDate,EventCount)
            VALUES (DATEADD(day,@Counter,'2013-01-01'),@SemiRandomCount2)
    END
    SET @Counter=@Counter+1
END
--SELECT * FROM dbo.EventRecords

Portanto, algumas datas têm várias entradas, outras não. Preciso obter resultados para relatórios que contenham todas as datas em um intervalo especificado, com as contagens totais para essa data (zero se não houver contagens para essa data). Depois de muito pesquisar no Google e experimentar, descobri uma maneira muito inteligente de gerar sequências em tempo real e construí essa função a partir dela. Essas sequências podem ser usadas para criar uma tabela de sequência de datas em tempo real, que pode ser usada para ingressar na tabela EventRecords e agrupar por data sem lacunas:

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE name='GetSequence')
   EXECUTE sp_executesql N'CREATE FUNCTION GetSequence() RETURNS @Table TABLE (Value SMALLINT NOT NULL) AS BEGIN     RETURN END'
GO

ALTER FUNCTION [dbo].[GetSequence](@StartInclusive INT, @EndExclusive INT)
RETURNS @Sequence TABLE
(
    Value BIGINT NOT NULL
)
AS
BEGIN
    INSERT @Sequence
    SELECT Value=@StartInclusive+n-1
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY o1.n) 
        FROM (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o1
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o2
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o3
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o4
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o5
        CROSS JOIN (SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.objects WITH (NOLOCK)) o6
        ) D (n)
    WHERE n<=@EndExclusive-@StartInclusive
    RETURN
END
GO

Aqui estão exemplos de consultas:

DECLARE @StartDate DATE='2013-01-01'
DECLARE @EndDate DATE='2015-01-01'
-- query with holes: not what I need
SELECT [EventDate], [TotalEventCount]=ISNULL(SUM(EventCount),0) 
FROM dbo.EventRecords 
GROUP BY [EventDate]
-- query with date holes filled in: this is what I need
SELECT 
    [EventDate]=DATEADD(day,s.Value,@StartDate), 
    [TotalEventCount]=ISNULL(SUM(EventCount),0)
FROM [dbo].[GetSequence](0,DATEDIFF(day,@StartDate,@EndDate)) s
LEFT JOIN dbo.EventRecords c
    ON DATEDIFF(day,@StartDate, EventDate)=s.Value
GROUP BY s.Value

Então, minha pergunta é: existe uma maneira melhor (mais simples ou mais rápida) de obter sequências ou uma maneira melhor de resolver esse problema no SQL?