ooutwire's questions

Versão do SQL Server:

SELECIONE @@VERSÃO;

/*
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Developer Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pack 1)
*/

Questão:

Então, eu estava brincando com a memória máxima e acidentalmente defini a memória máxima para 128 MB e agora não consigo abrir uma conexão com o SQL Server. Quando tentei abrir uma sessão e corrigir o erro, recebi este maravilhoso pop-up.

Para me salvar, decidi adicionar o parâmetro -f ao SQL Server e devolver o serviço.

Veja mais sobre isso aqui:

Opções de configuração do servidor de memória do servidor

e aqui:

Opções de inicialização do serviço do mecanismo de banco de dados

Depois que o serviço foi reiniciado, tentei mais uma vez me conectar. Recebi a seguinte mensagem:

Isso era esperado de acordo com as informações que eu tinha sobre o sinalizador -f. Em seguida, consegui abrir uma conexão usando a Conexão de administração dedicada (DAC). Consegui então executar sp_configure e alterar a configuração de memória máxima. Aqui está o que vi em sp_configure antes de fazer uma alteração. Parece que o valor corrente é novamente infinito (ou próximo o suficiente).

Executei esta atualização para aumentar a memória máxima para 3 GB:

USE mestre;
VAI
sp_configure
      'memória máxima do servidor (MB)'
    , 3072;
VAI
RECONFIGURAR;
VAI

E então removi o parâmetro -f e reiniciei o SQL Server. Nesse ponto, o servidor tornou-se acessível no modo multiusuário e eu tinha um valor máximo de memória igual a 3 GB. Esta não é a solução mais elegante que já encontrei; mas, infelizmente, funcionou; então resolvi compartilhar aqui.

Alguém tem uma solução mais rápida e limpa?

Um amigo meu me disse hoje que, em vez de pular o SQL Server, eu poderia simplesmente desanexar e reanexar um banco de dados e essa ação limparia as páginas e os planos do banco de dados fornecidos do cache. Eu discordei e forneço minhas evidências abaixo. Se você discorda de mim ou tem uma refutação melhor, por favor, forneça-a.

Estou usando o AdventureWorks2012 nesta versão do SQL Server:

SELECIONE @@VERSÃO;
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Developer Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pack 1)

Tendo carregado o banco de dados, executo a seguinte consulta:

Em primeiro lugar, execute o script de engorda AW de Jonathan K encontrado aqui:

AW Engordar

----------------------
-- Passo 1: Coisas de Bpool?
----------------------
USE [AdventureWorks2012];
VAI

SELECIONE
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [tamanho do buffer (MB)]
   , COUNT(*) AS [buffer_count]
A PARTIR DE
     sys.allocation_units AS um
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
ONDE
     b.database_id = DB_ID()
     E p.object_id > 100
GRUPO POR
     p.object_id
   , p.index_id
ORDENAR POR
     buffer_count DESC;

O resultado é mostrado aqui:

Desanexe e anexe novamente o banco de dados e, em seguida, execute novamente a consulta.

----------------------
-- Etapa 2: desanexar/anexar
----------------------
-- Destacar
USE [mestre]
VAI
EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2012'
VAI

-- Anexar
USE [mestre];
VAI

CRIAR BANCO DE DADOS [AdventureWorks2012] ON
(
    FILENAME = N'C:\sql server\files\AdventureWorks2012_Data.mdf'
)
    ,
(
    FILENAME = N'C:\sql server\files\AdventureWorks2012_Log.ldf'
)
 PARA ANEXO;
VAI

O que está no bpool agora?

----------------------
-- Passo 3: Coisas de Bpool?
----------------------
USE [AdventureWorks2012];
VAI

SELECIONE
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [tamanho do buffer (MB)]
   , COUNT(*) AS [buffer_count]
A PARTIR DE
     sys.allocation_units AS um
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
ONDE
     b.database_id = DB_ID()
     E p.object_id > 100
GRUPO POR
     p.object_id
   , p.index_id
ORDENAR POR
     buffer_count DESC;

E o resultado:

Todas as leituras são lógicas neste ponto?

--------------------------------
-- Etapa 4: somente leituras lógicas?
--------------------------------
USE [AdventureWorks2012];
VAI

DEFINIR ESTATÍSTICA IO ON;   
    SELECIONE * DE DatabaseLog;
    VAI
DEFINIR ESTATÍSTICA IO OFF;  

/*
(1597 linhas afetadas)
Tabela 'DatabaseLog'. Contagem de varredura 1, leituras lógicas 782, leituras físicas 0, leituras antecipadas 768, leituras lógicas lob 94, leituras físicas lob 4, leituras antecipadas lob 24.
*/  

E podemos ver que o buffer pool não foi totalmente destruído pelo desanexar/anexar. Parece que meu amigo estava errado. Alguém discorda ou tem um argumento melhor?

Outra opção é off-line e, em seguida, on-line do banco de dados. Vamos tentar isso.

--------------------------------
-- Passo 5: Offline/Online?
--------------------------------
ALTER DATABASE [AdventureWorks2012] DEFINIR OFFLINE;
VAI
ALTER DATABASE [AdventureWorks2012] DEFINIDO ONLINE;
VAI

----------------------
-- Passo 6: Coisas de Bpool?
----------------------
USE [AdventureWorks2012];
VAI

SELECIONE
     OBJECT_NAME(p.object_id) AS [ObjectName]
   , p.object_id
   , p.index_id
   , COUNT(*) / 128 AS [tamanho do buffer (MB)]
   , COUNT(*) AS [buffer_count]
A PARTIR DE
     sys.allocation_units AS um
     INNER JOIN sys.dm_os_buffer_descriptors AS b
           ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p
           ON a.container_id = p.hobt_id
ONDE
     b.database_id = DB_ID()
     E p.object_id > 100
GRUPO POR
     p.object_id
   , p.index_id
ORDENAR POR
     buffer_count DESC;

Parece que a operação offline/online funcionou muito melhor.

Fiz uma aposta com meu antigo chefe. Aposto com ela que o SQL Server, ao alocar uma nova extensão, sempre aloca do buffer pool e nunca verifica se existe algum lugar no disco onde a alocação possa ser armazenada. Essencialmente, ela contesta que o SQL Server deve verificar o espaço disponível no LUN antes de alocar uma página. Isso parece errado, já que eu poderia colocar meu armazenamento na lua, o que causaria uma latência séria. Eu sinto que ela realmente deseja que o SQL Server sempre traga uma página do disco primeiro e depois execute a (s) tarefa (s) DML.

Aqui está a minha "prova" de que ela está errada. Se você discordar da minha "prova", então, por favor, responda com uma melhor!

Vamos criar um banco de dados e uma tabela triviais. O modelo de recuperação do banco de dados será definido como SIMPLE e AUTO_CREATE_STATISTICS será desativado para minimizar o inchaço do registro de log.

Antes de começarmos, deixe-me divulgar a versão do SQL Server que estou usando.

SELECIONE @@VERSÃO;
-------------------------------------------------- -----------------------------------
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Developer Edition (64 bits) no Windows NT 6.1 (Build 7601: Service Pack 1)

Agora, o código...

USE mestre;
VAI

IF DATABASEPROPERTYEX(N'PageAllocDemo' , N'Version') > 0
      COMEÇAR
            ALTER DATABASE PageAllocDemo SET SINGLE_USER COM ROLLBACK IMEDIATO;
            DROP DATABASE PageAllocDemo;
      FIM;
VAI

CREATE DATABASE PageAllocDemo
VAI

USE PageAllocDemo;
VAI
SET NOCOUNT ON;
VAI

-- Defina o banco de dados como SIMPLE e desative o crapola de geração de log
ALTER DATABASE PageAllocDemo SET RECUPERAÇÃO SIMPLES;
VAI
ALTER DATABASE PageAllocDemo SET AUTO_CREATE_STATISTICS OFF;
VAI

CRIAR TABELA dbo.X
(
      c1 IDENTIDADE INT (1,1)
) EM [PRIMÁRIO];
VAI

Agora, vamos verificar quantas páginas foram alocadas? Suspeito de zero, pois criamos apenas uma "tabela lógica", no nosso caso um heap vazio.

-- Quantas páginas estão alocadas em nossa tabela?
DBCC IND (PageAllocDemo,X,-1);
VAI

Agora, limpe o registro.

-- Limpa o registro
PONTO DE VERIFICAÇÃO;
VAI

O que está atualmente no log?

-- O que está no log agora?
SELECT * FROM fn_dblog(NULL,NULL);
VAI

/*

---------------------------------------
-- Operação -------------- Contexto ---
---------------------------------------
LOP_BEGIN_CKPT LCX_NULL
LOP_XACT_CKPT LCX_BOOT_PAGE_CKPT
LOP_END_CKPT LCX_NULL

*/

Isso é esperado, pois estamos no modelo de recuperação SIMPLES. Vamos agora criar uma transação explícita que irá inserir um e apenas um registro em nossa tabela; mas, antes disso, vamos abrir o Process Monitor e filtrar nosso arquivo MDF e LDF, bem como o PID para o processo do SQL Server.

Inicie a transação:

INICIAR TRAN

    INSERIR NOS VALORES PADRÃO dbo.X;
    VAI

O Process Monitor mostra duas gravações no arquivo de log de transações.

Vamos verificar os registros de log.

-- O que está no log agora?
    SELECT * FROM fn_dblog(NULL,NULL);

    /*
    Omiti todos os registros de log para PFS, GAM, SGAM, etc.
    -------------------------------------------------- -------------
    -- Operação -------------- Contexto ------- ID da transação ---
    -------------------------------------------------- -------------
    LOP_BEGIN_XACT LCX_NULL 0000:0000030e
    LOP_BEGIN_XACT LCX_NULL 0000:0000030f
    LOP_FORMAT_PAGE LCX_HEAP 0000:0000030f
    LOP_COMMIT_XACT LCX_NULL 0000:0000030f
    LOP_INSERT_ROWS LCX_HEAP 0000:0000030e
    LOP_COMMIT_XACT LCX_NULL 0000:0000030e

*/

Eu omito o mapa de bits e as alocações de PFS e podemos ver que uma página é alocada e uma linha é inserida como seria de esperar.

Quantas páginas estão alocadas em nosso heap?

-- Quantas páginas estão alocadas em nossa tabela?
    DBCC IND (PageAllocDemo,X,-1);
    VAI

    /*

    Uma página IAM e uma página de dados e nada mais
    ----------------------------------
    PáginaFID PáginaPID IAMFID IAMPID      
    ------- ----------- ------ ------
    1 264 NULO NULO        
    1 231 1 264         

    */

Isso é como previsto. Temos uma página IAM e uma página de dados. Agora, nossa penúltima ação é confirmar a transação. Espero que ocorra uma descarga de bloco de log 512B neste ponto.

COMEÇAR TRAN;

Terminemos a "prova" com uma operação de ponto de verificação. Até agora, nada foi confirmado no arquivo de dados apenas no arquivo de log.

PONTO DE VERIFICAÇÃO;
VAI

Legal, as páginas de dados foram liberadas para o disco conforme o esperado.

Minha conclusão, a partir da evidência do Process Monitor, é que o SQL Server aloca na memória, adiciona o registro na memória e confirma a página no disco sem verificar nada no nível de armazenamento.

Alguém se opõe a esta hipótese? Se sim, por quê?

Então, imagine que tenho uma página que pretendo dividir.

 
USE mestre;
VAI

IF DATABASEPROPERTYEX (N'Páginas', N'Versão') > 0
COMEÇAR
    Páginas ALTER DATABASE SET SINGLE_USER COM ROLLBACK IMEDIATO;
    Páginas DROP DATABASE;
FIM
VAI

CREATE DATABASE Páginas;
VAI

IF EXISTS(SELECT object_id FROM sys.objects WHERE name = 'PageSplit')
COMEÇAR
    DROP TABLE PageSplit ;
FIM


Páginas de USO;
VAI

CREATE TABLE PageSplit
(
      c1 IDENTIDADE INT
    , c2 VARCHAR(2000) DEFAULT REPLICATE('b', 1000)
);
VAI

CRIAR ÍNDICE CLUSTERED ÚNICO CIX_c1
ON dbo.PageSplit
(
    [c1] ASC
);
VAI

INSERIR NOS VALORES PADRÃO DO PageSplit;
GO 7

DBCC IND(Páginas,PageSplit,-1);
VAI
DBCC TRACEON(3604);
VAI
DBCC PAGE (Páginas,1,231,3);
VAI
-- m_freeCnt = 977

Neste ponto, preciso apenas atualizar uma linha da seguinte forma e obtenho uma divisão: 3 linhas para uma página, 4 para a outra:

ATUALIZAR Divisão de página
SET c2 = REPLICAR('b', 2000)
ONDE c1 = 1 ;
VAI

Então, minha pergunta é a seguinte: quando acontece a atualização: antes ou depois da divisão da página? Presumo que ocorra após a divisão.

Anexei a saída do SQL Profiler e DBCC IND. Apenas uma observação lateral, a linha atualizada permaneceu na página original.

Apliquei o conselho do SQL Kiwi. Veja os resultados aqui:

As alterações do modelo de recuperação do SQL Server são registradas em algum lugar... WMI, logs de erro... etc?

Por exemplo, se eu mudar de FULLpara SIMPLE, isso é gravado em algum lugar?

Continuo a ler em muitos fóruns e em muitos blogs que uma página é composta da seguinte forma: Tamanho da página: 16 x 512B = 8192B Cabeçalho da página: = 96B Maximum In_Row Row: = 8060B

Isso deixa (8192 - 96 - 8060)B = 36B.

Ok, isso é lógico e correto. A pergunta que tenho é a seguinte: por que tantas pessoas dizem que os 36B restantes são reservados para a matriz de slots?

Obviamente, a matriz de slots fornece 2B por linha na página; portanto, pode ser tão pequeno quanto 2B e tão grande quanto 1472B:

2B: 1 linha * 2B = 2B

1472B: 8096B = n*9B (tamanho mínimo da linha com sobrecarga...pense em uma única coluna TINYINT) + n*2B (custo da matriz de slots por linha) => 8096 = 11n => n = 8096 / 11 = 736.

736*2B = 1472B.

Isso me leva a 20 devido à tag de versão 14B.

USE master ;
GO

CREATE DATABASE test ;
GO

USE test ;
GO

ALTER DATABASE test
    SET ALLOW_SNAPSHOT_ISOLATION ON ;
GO

ALTER DATABASE test
    SET READ_COMMITTED_SNAPSHOT ON ;
GO

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i CHAR(8000) DEFAULT(REPLICATE('a',8000))
    , j CHAR(53)   DEFAULT(REPLICATE('a',53))
) ;

INSERT INTO tbl 
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

Outro exemplo. Se você for de 49 para 50, obterá o VARCHAR(MAX) indo para LOB_DATA.

DROP TABLE tbl ;
GO

CREATE TABLE tbl
(
      i VARCHAR(MAX) DEFAULT(REPLICATE('a',8000))
    , j CHAR(49)   DEFAULT(REPLICATE('a',49))
) ;

sp_tableoption N'tbl', 'large value types out of row', 'OFF' ;
GO

INSERT INTO tbl 
    DEFAULT VALUES ;
GO

DBCC IND (test,tbl,-1) ;
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(test,1,272,3) ;
GO

Parece que esse problema permanece, mesmo no SQL Server 2012. @SQLKiwi aponta para esta postagem de Kimberly Tripp - http://www.sqlskills.com/blogs/kimberly/a-simple-start-table-creation-best-practices / .

-- Exemplo 1: com índice agrupado

CREATE TABLE tbl5 
(
    i VARCHAR(900) NOT NULL 
) ;
GO
CREATE CLUSTERED INDEX CIX_tbl5
    ON tbl5 (i ASC) ;
GO

INSERT      INTO tbl5
            ( i )
VALUES
            ( REPLICATE('a' , 900) )
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) )
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) )  -- 900 x 8  + (13 x 8) = 7200 + 104 = 7304
    ,       ( REPLICATE('i' , 761) ) ;-- 7304 + 761 + 13  
GO                                    -- = 8078 + 96(Page Header) + 18 (Slot)
                                      -- = 8192
                                      -- m_freeCnt = 0

DBCC IND(tempdb,tbl5,-1) ; -- everything is IN_ROW_DATA
GO
DBCC TRACEON(3604) ;
GO
DBCC PAGE(tempdb,1,177,3) ;
GO

-- Exemplo 2: Com heap. Quando a coluna 'i' é 392 m_freeCnt = 387. Quando 'i' = 393, obtenho uma nova página. Por que não posso adicionar mais bytes a 'i'?

CREATE TABLE tbl6
(
    i VARCHAR(900) NOT NULL 
) ;
GO
INSERT      INTO tbl6
            ( i )
VALUES
            ( REPLICATE('a' , 900) ) 
    ,       ( REPLICATE('b' , 900) ) 
    ,       ( REPLICATE('c' , 900) ) 
    ,       ( REPLICATE('d' , 900) ) 
    ,       ( REPLICATE('e' , 900) ) 
    ,       ( REPLICATE('f' , 900) ) 
    ,       ( REPLICATE('g' , 900) ) 
    ,       ( REPLICATE('h' , 900) ) 
    ,       ( REPLICATE('i' , 393) ) ;
GO

DBCC IND(tempdb,tbl6,-1); 
GO
DBCC TRACEON(3604);
GO
DBCC PAGE(tempdb,1,181,3);
GO

USE tempdb ;
VAI

DROP TABLE tbl ;
VAI

CRIAR TABELA tb
(
    i SQL_VARIANT NÃO NULO
);
VAI

INSERIR EM tbl (i)
    VALORES (1) ;
VAI

SELECT i FROM tbl ;
VAI

DBCC IND ('tempdb','tbl',-1);
VAI

DBCC TRACEON (3604); -- Despejo de página irá para o console
VAI

PÁGINA DBCC ('tempdb',1,157,3);
VAI

• Tamanho do registro = 17B
• 30000400 01000001 00110038 01010000 00
• MarcaA = 0x30 = 1B
• EtiquetaB = 0x00 = 1B
• Deslocamento de bitmap nulo = 0x0004 = 2B
• Contagem de colunas = 0x0001 = 2B
• Bitmap nulo = 0x00 = 1B
• Contagem de colunas de comprimento variável = 0x0001 = 2B
• Matriz de deslocamento de coluna de comprimento variável = 0x0011 = 2B
• Esta é a codificação sql_variant para números inteiros... Acho que = 0x0138 = 2B
• Nossa coluna inteira = 0x00000001 = 4B

    SELECIONE
      SQL_VARIANT_PROPERTY(i, 'BaseType') AS BaseType -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i, 'Precisão') AS [Precisão] -- INT
    , SQL_VARIANT_PROPERTY(i, 'Escala') AS Escala -- INT
    , SQL_VARIANT_PROPERTY(i, 'TotalBytes') AS TotalBytes -- INT
    , SQL_VARIANT_PROPERTY(i, 'Collation') AS [Collation] -- SYSNAME NVARCHAR(128)
    , SQL_VARIANT_PROPERTY(i, 'MaxLength') AS MaxLength -- INT
A PARTIR DE
      tb ;
VAI

Minhas perguntas:

1. 0x3801... o que é isso
2. i = 328792402 Hã? De onde veio isso?
3. SQL_VARIANT_PROPERTY() diz que tenho um número inteiro. Por que não escolhe tinyint?
4. Alguém sabe onde SQL_VARIANT_PROPERTY() pode ser encontrado. Devo usar o DAC para obtê-lo?

No SQL Server, por que um tinyint é armazenado com 9B na linha. Por alguma razão, parece haver um byte adicional no final da máscara de bitmap NULL.

    USE tempdb ;
    VAI

    CRIAR TABELA tb
    (
        i TINYINT NÃO NULO
    );
    VAI

    INSERIR EM tbl (i)
        VALORES (1) ;
    VAI

    DBCC IND ('tempdb','tbl',-1);
    VAI

    DBCC TRACEON (3604); -- Despejo de página irá para o console
    VAI

    PÁGINA DBCC ('tempdb',1,168,3);
    VAI

Resultados (inverti os bytes devido a DBCC PAGE mostrando o byte menos significativo primeiro):

Record Size = 9B
10000500 01010000 00
TagA = 0x10 = 1B
TagB = 0x00 = 1B
Null Bitmap Offset = 0x0005 = 2B
Our integer column = 0x01 = 1B
Column Count = 0x0001 = 2B
NULL Bitmap = 0x0000 = 2B (what!?)