Resumo/Resumo;

Ao analisar a primeira página do DCM em um banco de dados (que documenta quais extensões foram modificadas, para que o processo de backup DIFF não precise verificar todo o banco de dados em busca de alterações, mas possa pular para as extensões para verificar as páginas que foram alteradas), onde está o local da próxima página do DCM documentada?

Eu teria pensado que isso seria documentado no m_nextPageatributo do cabeçalho da página. Mas ao verificar, isso não parece ser o caso.

Encontrando as páginas do DCM

Eu estava instruindo nosso aprendiz sobre o que os backups diferenciais contêm e como o DBMS documenta as alterações nas páginas individuais e depois na página consolidada do DCM.

Referência: Guia de arquitetura de páginas e extensões (Microsoft Learn | SQL)

Comecei então a brincar com um banco de dados muito grande e executei os seguintes comandos:

DBCC TRACEON (3604);
DBCC PAGE ( 6, 1, 0, 3 );       -- File Header 

Isso produziu a seguinte saída:

PAGE: (1:0)


BUFFER:


BUF @0x000002CC7AC80780

bpage = 0x000002CC2D11E000          bhash = 0x0000000000000000          bpageno = (1:0)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48700                       bstat = 0x9
blog = 0x15a                        bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D11E000

m_pageId = (1:0)                    m_headerVersion = 1                 m_type = 15
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x208
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 0
m_slotCnt = 1                       m_freeCnt = 7019                    m_freeData = 3321
m_reservedCnt = 0                   m_lsn = (2501578:318516:1)          m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = 1668847350
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED 

Depois de ler o resultado, notei a informação perto do final e presumi (corretamente) que a página do DCM estaria localizada na página número 6 (bem, 7 na verdade, mas...).

Vamos dar uma olhada:

DBCC PAGE ( 6, 1, 6, 0 );       -- 1. DCM Page

Sim, estas parecem ser as primeiras páginas do DCM:

PAGE: (1:6)


BUFFER:


BUF @0x000002CC7AC80300

bpage = 0x000002CC2D112000          bhash = 0x0000000000000000          bpageno = (1:6)
bdbid = 6                           breferences = 1                     bcputicks = 0
bsampleCount = 0                    bUse1 = 48800                       bstat = 0x9
blog = 0x7a7a7a7a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002CC2D112000

m_pageId = (1:6)                    m_headerVersion = 1                 m_type = 16
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99    m_indexId (AllocUnitId.idInd) = 0   Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0           Metadata: IndexId = 0               Metadata: ObjectId = 99
m_prevPage = (0:0)                  m_nextPage = (0:0)                  pminlen = 90
m_slotCnt = 2                       m_freeCnt = 6                       m_freeData = 8182
m_reservedCnt = 0                   m_lsn = (2501605:3132423:42)        m_xactReserved = 0
m_xdesId = (0:0)                    m_ghostRecCnt = 0                   m_tornBits = -431908416
DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED           

DIFF_MAP: Header @0x000000B60C7FA064 Slot 0, Offset 96

status = 0x0                        

DIFF_MAP: Extent Alloc Status @0x000000B60C7FA0C2

(1:0)        - (1:24)       =     CHANGED                                
(1:32)       - (1:40)       = NOT CHANGED                                
(1:48)       -              =     CHANGED                                
(1:56)       - (1:80)       = NOT CHANGED                                
(1:88)       -              =     CHANGED                                
(1:96)       - (1:104)      = NOT CHANGED                                
(1:112)      - (1:128)      =     CHANGED                                
(1:136)      - (1:152)      = NOT CHANGED                                
(1:160)      - (1:168)      =     CHANGED                                
(1:176)      -              = NOT CHANGED                                
(1:184)      - (1:192)      =     CHANGED                                
(1:200)      - (1:208)      = NOT CHANGED   
...

No entanto, encontrar a próxima página do DCM foi uma tentativa e erro. Adicionei 4'096'000 páginas às 6 que eu já tinha (o que está errado, deveria ser apenas 512'000 páginas. Obrigado Martin Smith por apontar isso), mas recebi um número de página um pouco maior do que a próxima página do DCM. A próxima página do DCM pode ser encontrada nas informações do cabeçalho.

GAM (1:4089856) = ALLOCATED         SGAM (1:4089857) = NOT ALLOCATED    PFS (1:4092528) = 0x0   0_PCT_FULL
DIFF (1:4089862) = NOT CHANGED      ML (1:4089863) = NOT MIN_LOGGED    

A próxima página do DCM não está documentada nas m_nextPageinformações do cabeçalho, então presumo que ela deva estar na última página bitda primeira página do DCM.

Questões

1. Minha suposição está correta de que o SQL Server detecta a próxima página do DCM como o último bit da primeira página do DCM? Ou o SQL Server tem outros meios de encontrar a segunda, terceira, quarta, etc. páginas do DCM?

2. Não seria mais eficiente documentar a próxima página do DCM nas m_nextPageinformações de cabeçalho da primeira página do DCM?

Introdução

Recentemente, durante uma atualização de um aplicativo, recebi a seguinte mensagem de erro:

Msg 5074 Level 16 State 1 Line 1
The statistics 'BreakingStuff' is dependent on column 'TaskText'.
Msg 4922 Level 16 State 9 Line 1
ALTER TABLE ALTER COLUMN TaskText failed because one or more objects access this column.

^{Exemplo de mensagem de erro baseada em análise}

Esta foi uma estatística criada manualmente em uma coluna de uma tabela. Na verdade, havia várias estatísticas criadas manualmente referenciando a coluna em questão.

Gambiarra

Depois de descartar as estatísticas da tabela, executar a atualização e recriar as estatísticas, tudo estava funcionando bem.

Análise

Comecei com uma simulação trivial em db<>fiddle para ver se conseguia reproduzir o problema. Depois de alguns ajustes (trocadilhos), descobri que a mensagem de erro só é acionada se eu reduzir o tamanho da coluna.

por exemplo varchar(50)-->varchar (40)

Aqui está o db<>fiddle .

Os únicos avisos que encontrei no Microsoft Learn foram:

1. Modificar o tipo de dados de uma coluna que já contém dados pode resultar na perda permanente de dados quando os dados existentes são convertidos para o novo tipo. Além disso, o código e os aplicativos que dependem da coluna modificada podem falhar. Isso inclui consultas, visualizações, procedimentos armazenados, funções definidas pelo usuário e aplicativos cliente. Observe que essas falhas ocorrerão em cascata. Por exemplo, um procedimento armazenado que chama uma função definida pelo usuário que depende da coluna modificada pode falhar. Considere cuidadosamente quaisquer alterações que você deseja fazer em uma coluna antes de fazê-la.

   ^{Referência: Modificar colunas (Microsoft Learn | SQL)}

Eu estava ciente de que reduzir o tamanho da coluna possivelmente resultaria em perda de dados, mas não foi o caso, pois os dados eram todos menores que varchar(40).

Questões

1. Por que reduzir o tamanho da coluna resulta em erro devido às estatísticas criadas manualmente?
2. Por que aumentar o tamanho de uma coluna não resulta em erro nas estatísticas criadas manualmente?

Eu tenho as seguintes tabelas em meu banco de dados.

Tabela de Sobrenomes

CREATE TABLE [dbo].[LastNames](
    [LastNameID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_LastNames_LastName] ON [dbo].[LastNames]
(
    [LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Tabela de nomes

CREATE TABLE [dbo].[FirstNames](
    [FirstNameID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_FirstNames_FirstName] ON [dbo].[FirstNames]
(
    [FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Tabela PessoasAnon

CREATE TABLE [dbo].[PersonsAnon](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [LastNameID] [int] NOT NULL,
    [FirstNameID] [int] NOT NULL,
    [Info1] [bit] NULL,
    [Info2] [char](1) NULL,
    [Info3] [nchar](50) NULL,
    [AdressID] [int] NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [CIX_PersonsAnon_PersonID_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [PersonID] ASC,
    [LastNameID] ASC,
    [FirstNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_PersonsAnon_LastNameID_FirstNameID] ON [dbo].[PersonsAnon]
(
    [LastNameID] ASC,
    [FirstNameID] ASC
)
INCLUDE([PersonID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Geração de dados

Para aqueles que possuem um banco de dados AdventureWorks2014 disponível, usei as seguintes instruções para preencher as tabelas:

INSERT INTO dbo.LastNames (LastName) SELECT DISTINCT app.LastName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.FirstNames (FirstName) SELECT DISTINCT app.FirstName FROM AdventureWorks2016.Person.Person AS app

INSERT INTO dbo.PersonsAnon (LastNameID, FirstNameID)
SELECT ln.LastNameID, fn.FirstNameID FROM LastNames ln CROSS APPLY FirstNames fn 

A declaração

A seguinte declaração foi usada para ver que tipo de plano de execução seria criado:

SELECT pa.PersonID,
       fn.FirstName,
       ln.LastName,
       pa.Info2
FROM   PersonsAnon AS pa
       JOIN LastNames AS ln
            ON  pa.LastNameID = ln.LastNameID
       JOIN FirstNames AS fn
            ON  pa.FirstNameID = fn.FirstNameID
WHERE  ln.LastName LIKE 'Pet%'
       AND fn.FirstName LIKE 'John%'
ORDER BY
       ln.LastName,
       fn.FirstName;

O Plano de Execução de Consulta

Colar o plano

Questões

Por que o JOIN é realizado entre Sobrenomes e Nomes mesmo que não haja predicado JOIN? O que está fazendo com que o QO JUNTE as duas tabelas?

Situação Gráfica

Aqui está o que parece:

E os detalhes mostram:

Artigos já consultados

• Por que isso diz que não há predicado de junção?
• O que exatamente significa “No Join Predicate” no SQL Server?
• Devo ficar alarmado com este aviso NO JOIN PREDICATE?
• Por que isso diz que não há predicado de junção?

Recentemente, consultei nossa ferramenta interna de inventário de banco de dados para obter uma lista de servidores, instâncias e bancos de dados e adicionei o status correspondente a cada servidor, instância e banco de dados.

Diagrama de Relacionamento

Server ˂-- 1 : n --˃ Instance ˂-- 1 : n --˃ Database
   ˄                    ˄                      ˄
   |                    |                      |
   |                  1 : 1                    |
   |                    |                      |
   |                    ˅                      |
   +-- 1 : 1 --˃     Status        ˂-- 1 : 1 --+

Leia como:
...um servidor pode ter múltiplas instâncias
...uma instância pode ter vários bancos de dados
...um servidor, uma instância e um banco de dados podem ter um status

Configurar

Tabela de status

CREATE TABLE [Status]
(
  StatusID int,
  StatusName char(20),
  );

Dados de status

INSERT INTO [Status] (StatusID, StatusName)
VALUES
(1,'Productive'),
(2,'Prod ACC'),
(3,'Prod APP'),
(4,'Test'),
(5,'Test ACC'),
(6,'Test APP'),
(7,'OFFLINE'),
(8,'Reserved'),
(9,'Decommisioned');

Tabela de servidores

CREATE TABLE [Server]
(
  ServerID int,
  ServerName char(20),
  ServerStatusID int
  );

Dados do servidor

INSERT INTO [Server] (ServerID, ServerName, ServerStatusID)
VALUES
(1,'FirstServer',1),
(2,'SecondServer',2),
(3,'ThirdServer',5),
(4,'FourthServer',8),
(5,'FifthServer',8);

Tabela de Instâncias

CREATE TABLE [Instance]
(
  InstanceID int,
  ServerID int,
  InstanceName char(30),
  InstanceStatusID int
  );

Dados da instância

INSERT INTO [Instance] 
  (InstanceID, ServerID, InstanceName, InstanceStatusID)
VALUES
(1,1,'GENERAL',1),
(2,1,'TAXES',1),
(3,2,'GENERAL',9),
(4,2,'SOCIAL',2),
(5,3,'GENERAL',5),
(6,3,'FBI',8),
(7,5,'COMINGSOON',8);

Tabela de banco de dados

CREATE TABLE [Database]
(
  DatabaseID int,
  InstanceID int,
  DatabaseName char(30),
  DatabaseStatusID int
  );

Dados do banco de dados

INSERT INTO [Database]
(DatabaseID, InstanceID, DatabaseName, DatabaseStatusID)
VALUES
(1,1,'master',1),
(2,1,'model',1),
(3,1,'msdb',1),
(4,1,'UserDB1',1),
(5,2,'master',1),
(6,2,'model',1),
(7,2,'msdb',1),
(8,2,'TaxesDB',1),
(9,4,'master',2),
(10,4,'model',2),
(11,4,'msdb',2),
(12,4,'HealthCareDB',2),
(13,5,'master',5),
(14,5,'model',5),
(15,5,'msdb',5),
(16,5,'GeneralUserDB',5),
(17,6,'master',8),
(18,6,'model',8),
(19,6,'msdb',8),
(20,6,'CriminalDB',8);

Instrução SELECT sem tabela de status envolvida

A instrução SELECT inicial envolvia simplesmente juntar as três tabelas: servidor, instância, banco de dados e era a seguinte:

-- Simple SELECT to get all information on Servers, Instances and Databases
-- The status of the server, instance or database is not returned
SELECT 
  ServerName, 
  InstanceName,
  DatabaseName 
  FROM [Server] as srv
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID;

Resultados de 1. Declaração

POR FAVOR, OBSERVE ISSO...

• existe um servidor sem instância e banco de dados
• há uma instância sem banco de dados

Instrução SELECT envolvendo tabela de status

Na próxima instrução decido adicionar o status a cada elemento (servidor, instância, banco de dados) e JOINeditar cada tabela com a Statustabela da seguinte forma:

-- Advanced SELECT to get all information on Servers, Instances and Databases 
-- including their status
SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  insst.StatusName,
  DatabaseName,
  dbsst.StatusName
  FROM [Server] as srv
    JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    JOIN [Status] as insst
      ON ins.InstanceStatusID = insst.StatusID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID
    JOIN [Status] as dbsst
      ON dbs.DatabaseStatusID = dbsst.StatusID
  ;

Resultados de 2. Declaração

Para minha surpresa o servidor sem instância e banco de dados e o servidor com instância mas sem banco de dados não estavam mais listados:

Descobertas / Solução

Depois de verificar várias opções com uma abordagem de tentativa e erro, descobri que JOINon the Statustable teve que ser alterado para a LEFT JOINpara permitir que a instrução exibisse o servidor sem uma instância ou um banco de dados e exibisse a instância sem um banco de dados :

-- Advanced SELECT to get all information on Servers, Instances and Databases 
-- including their status
SELECT 
  ServerName, 
  srvst.StatusName,
  InstanceName,
  insst.StatusName,
  DatabaseName,
  dbsst.StatusName
  FROM [Server] as srv
    LEFT JOIN [Status] as srvst
      ON srv.ServerStatusID = srvst.StatusID
    LEFT JOIN [Instance] as ins
      ON srv.ServerID = ins.ServerID
    LEFT JOIN [Status] as insst
      ON ins.InstanceStatusID = insst.StatusID
    LEFT JOIN [Database] as dbs
      ON ins.InstanceID = dbs.InstanceID
    LEFT JOIN [Status] as dbsst
      ON dbs.DatabaseStatusID = dbsst.StatusID;

Resultados de 3. Declaração

Material de referência

Aqui está um link para o db<>fiddle para reproduzir minhas descobertas.

Pergunta

Por que o SQL Server exige um LEFT JOINna Statustabela para itens filhos que não existem e para que a consulta exiba esses itens?

Situação

Ao consultar um banco de dados com uma instrução SELECT com um conjunto definido de colunas, os resultados são recebidos em cerca de 21 segundos.

Se houver um asterisco adicional ( , *) no final da lista de conjunto de colunas definido, a consulta retornará os resultados em 2 segundos.

Planos de Execução de Consultas

Os planos de execução diferem significativamente.

Você pode encontrar o plano de execução de consulta real bom e o plano de execução de consulta real ruim com os links do PasteThePlan.

Instrução contendo , * na lista de colunas (no final)

            SELECT    -- DISTINCT -- 27.04.2020
                'SchuelerKlasse' AS EcoQuery,
                VX_PERSON.PER_MAN_ID, VX_PERSON.PER_ID, VX_PERSON.PER_NAME, VX_PERSON.PER_VORNAME, VX_PERSON.PER_LB_PER_ID, 
                VX_PERSON.PER_GESCHLECHT, VX_PERSON.PER_GEBURTSDATUM, VX_PERSON.PER_TELP, VX_PERSON.PER_MAILP, VX_PERSON.PER_NATP, VX_PERSON.PER_VERSICHERTENNUMMER, VX_PERSON.PER_LAND,
                VX_ADRESSE.ADR_STRASSE, VX_ADRESSE.ADR_PLZ, VX_ADRESSE.ADR_ORT,
                VX_KLASSE.KL_CODE, VX_KLASSE.KL_BEZEICHNUNG,
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS, 
                VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDETYP, VX_KLASSEABSCHNITTSCHUELER.KAS_ABSCHNITTSNR,
                VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSE, VX_KLASSE_ZEITRAUM.KLZ_ZR_NR,
                VX_ZEITRAUM.ZR_BEGINN, VX_ZEITRAUM.ZR_ENDE
                ,'' AS FA_CODE
                ,'' AS FA_BEZ_STP, '' AS FA_BEZ_STP_LANG
                , '' AS EcoOrig_FA_CODE, '' AS EcoOrig_FA_BEZ_STP, '' AS EcoOrig_FA_BEZ_STP_LANG
                , VX_ANGEBOT.ANG_BEGINN
            
 ,* 

            FROM 
                ECOLST.VX_KLASSE_ZEITRAUM, 
                ECOLST.VX_PERSON, 
                ECOLST.VX_KLASSE, 
                ECOLST.VX_KLASSEABSCHNITTSCHUELER, 
                ECOLST.VX_ZEITRAUM, 
                ECOLST.VX_ADRESSE 
                , ECOSYS.T_KLASSE
                , ECOLST.VX_ANGEBOT

            WHERE  
                    VX_KLASSE_ZEITRAUM.klz_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSE_ZEITRAUM.klz_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_ang_id = VX_KLASSE.kl_ang_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_zr_id = VX_ZEITRAUM.zr_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_per_id = VX_PERSON.per_id 
                AND VX_KLASSEABSCHNITTSCHUELER.kas_kl_id = VX_KLASSE.kl_id 
                AND VX_KLASSEABSCHNITTSCHUELER.KAS_ANMELDE_STATUS LIKE 'De%'  -- LIKE 'Definitiv%'
                AND VX_PERSON.per_id = VX_ADRESSE.adr_per_id 
                AND VX_PERSON.per_man_id = VX_KLASSE.kl_man_id
                AND VX_KLASSE.KL_ANG_ID = VX_ANGEBOT.ANG_ID
                AND VX_KLASSE.KL_MAN_ID = 15 
                AND VX_KLASSE.KL_ID = T_KLASSE.KL_ID
                AND T_KLASSE.KL_STATUS_ID = 491   -- d.h. TS_CODE.CODE_UP_BEZEICHNUNG = 'AKTIV'
            

                AND VX_KLASSE.KL_KLASSENTYP_ID IN (742,743,1235,1926,2075,2076,2078,2079,2080,2081,2086,2103,2118,2119,2122,2152,2252,2308,2416)
        

                AND VX_PERSON.PER_NP = 1   -- Natürliche Person
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_VORNAME))) > 0        -- TRIM() kann erst ab SQL Server 2017 verwendet werden
                AND LEN(LTRIM(RTRIM(VX_PERSON.PER_NAME))) > 0           -- TRIM() kann erst ab SQL Server 2017 verwendet werden
        
 AND VX_ZEITRAUM.zr_beginn <= CONVERT(DATETIME, '20.05.2023', 104) 
 AND VX_ZEITRAUM.zr_ende   >= CONVERT(DATETIME, '14.02.2023', 104) 
 AND VX_PERSON.per_man_id IN ( 15 ) 

                --AND VX_Person.PER_ID IN  (233777,233779)
        

Questões

A recomendação geral é não usar *ao definir a lista de colunas, mas, no meu caso, adicionar o , *à lista de colunas no final acelera significativamente a consulta. (de 21s até 2s)

Não há recomendações de índices ausentes nos planos de execução reais.

Presumo que tenha a ver com colunas específicas retornadas ao usar o , *in the statement , que possivelmente estão incluídas em índices considerados úteis pelo otimizador de consulta, mas não tenho certeza de como identificar essas colunas.

1. Quais índices eu teria que criar para persuadir o SQL Server a executar a instrução de desempenho ruim que não contém nenhuma , *lista de colunas, para usar um plano semelhante à instrução de desempenho, * que contém o adicional na lista de colunas?

2. Teria que analisar todos os índices usados ​​no bom plano de execução e criar índices reduzidos (omitindo certas colunas) para que o otimizador de consulta considerasse usar um plano de boa execução semelhante para a instrução sem o adicional , *?

Soluções experimentadas de acordo com as sugestões

1. OPTION (MIN_GRANT_PERCENT = 10, MAX_GRANT_PERCENT = 15)

   A aplicação da solução acima forneceu apenas um aumento temporário no desempenho por cerca de 1 hora. Depois disso, a consulta voltou ao plano de execução ruim. Eu não tenho ideia do porquê...

2. Nível de compatibilidade do banco de dados

   Alterar o nível de compatibilidade do banco de dados para 110 (SQL Server 2012) com o seguinte comando resultou em um aumento constante de desempenho para a consulta mencionada sem a adição de ,*na lista de colunas.

   USE [master]
   GO
   ALTER DATABASE [ECOWEBBSP] SET COMPATIBILITY_LEVEL = 110
   GO
   

   O plano de execução de consulta com nível de compatibilidade em 110 mostra que o otimizador de consulta escolheu uma abordagem totalmente diferente ao recuperar os dados e não teve nenhum problema em atribuir a quantidade correta de memória (110 MB).

Questão a seguir

Definir o nível de compatibilidade para 110 é minha única opção?

Comentários Adicionais

A Ominous Function fi_kla_is_abschlussklassemencionada na resposta de Erik é acionada pela coluna VX_KLASSE_ZEITRAUM.KLZ_IS_ABSCHLUSSKLASSEna ECOLST.VX_KLASSE_ZEITRAUMexibição. A tabela subjacente chama a função de valor escalar ao recuperar dados. A própria função retorna 0 ou 1, dependendo se o aluno está em uma turma de graduação (1) ou não (0).

No entanto, a função não parece ter tanto impacto na duração da consulta ao executar com o nível de compatibilidade definido como 110 (SQL Server 2012). Consulte o plano de execução da consulta com nível de compatibilidade em 110 para obter detalhes.

Atualmente estou consultando o sys.dm_db_log_info()DMV para recuperar os VLFs de um banco de dados para determinar quando posso reduzir, reorganizar e reduzir a quantidade de fragmentados (10 MB VLFs) no arquivo TLOG.

A razão para isso é que você não pode reduzir o arquivo TLOG se uma transação estiver no final do arquivo TLOG e resultar em um VLF ativo. Situação semelhante, se uma transação ativa residir no meio do arquivo TLOG, você não poderá reduzir esse VLF.

Extrato atual

Atualmente tenho esta declaração para recuperar o MAX(vlf_begin_offset)registro, o MIN(vlf_begin_offset)registro e qualquer registro com um ativo vlf_active = 1:

SELECT ddli.vlf_begin_offset,
       ddli.vlf_sequence_number,
       ddli.vlf_active,
       ddli.vlf_status,
       ddli.vlf_first_lsn
FROM   sys.dm_db_log_info(DB_ID()) AS ddli
WHERE  ddli.vlf_begin_offset = (
           SELECT MIN(ddli2.vlf_begin_offset)
           FROM   sys.dm_db_log_info(DB_ID()) AS ddli2
       )
       OR  ddli.vlf_active = 1
       OR  ddli.vlf_begin_offset = (
               SELECT MAX(ddli3.vlf_begin_offset)
               FROM   sys.dm_db_log_info(DB_ID()) AS ddli3
           )
ORDER BY
       ddli.vlf_begin_offset ASC

Quando todos os registros são retornados, o conjunto de resultados fica assim:

+------------------+---------------------+------------+------------+------------------------+
| vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
+------------------+---------------------+------------+------------+------------------------+
|             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
|           262144 |              253979 |          0 |          0 | 00000000:00000000:0000 |
|           516096 |              253980 |          0 |          0 | 00000000:00000000:0000 |
|           770048 |              253977 |          0 |          0 | 00000000:00000000:0000 |
|          1048576 |              253981 |          0 |          0 | 00000000:00000000:0000 |
|         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
|         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
|         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |
|         67108864 |              253971 |          0 |          0 | 00000000:00000000:0000 |
|         75497472 |              253972 |          0 |          0 | 00000000:00000000:0000 |
|         83886080 |              253973 |          0 |          0 | 00000000:00000000:0000 |
|         92274688 |              253974 |          0 |          0 | 00000000:00000000:0000 |
|        100663296 |              253975 |          0 |          0 | 00000000:00000000:0000 |
|        109051904 |              253976 |          0 |          0 | 00000000:00000000:0000 |
|        117440512 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        236978176 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        356515840 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        476053504 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        595591168 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        715128832 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        834666496 |                   0 |          0 |          0 | 00000000:00000000:0000 |
|        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
+------------------+---------------------+------------+------------+------------------------+

Com meu script atual, recebo:

+------------------+---------------------+------------+------------+------------------------+
| vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
+------------------+---------------------+------------+------------+------------------------+
|             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
|         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
|        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
+------------------+---------------------+------------+------------+------------------------+

informação adicional

O vlf_active = 1pode aparecer em vários locais. Pode haver mais de uma ilha com vlf_active = 1.

Perguntas

Seria bom ter um registro antes de qualquer vlf ativo ( vlf_active = 1) e um registro depois.

• Como posso conseguir isso?

    +------------------+---------------------+------------+------------+------------------------+
    | vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
    +------------------+---------------------+------------+------------+------------------------+
    |             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
    |         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
    |         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
    |         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |    
    |        954204160 |                   0 |          0 |          0 | 00000000:00000000:0000 |
    +------------------+---------------------+------------+------------+------------------------+
  

• O que seria necessário para ter um registro resumido no meio se houver centenas de registros? por exemplo, algo assim:

    +------------------+---------------------+------------+------------+------------------------+
    | vlf_begin_offset | vlf_sequence_number | vlf_active | vlf_status |     vlf_first_lsn      |
    +------------------+---------------------+------------+------------+------------------------+
    |             8192 |              253978 |          0 |          0 | 00000000:00000000:0000 |
    |  (4 res clipped) |                     |            |            |                        |
    |         17563648 |              253982 |          0 |          0 | 00000000:00000000:0000 |
    |         34078720 |              253983 |          1 |          2 | 0003e01f:00000010:0001 |
    |         50593792 |              253970 |          0 |          0 | 00000000:00000000:0000 |    
    | (13 res clipped) |                     |            |            | 00000000:00000000:0000 |    
    |        954204160 |                   0 |          0 |          0 |                        |
    +------------------+---------------------+------------+------------+------------------------+
  

Eu encontrei um problema ao configurar um SQL Server de várias instâncias ao atribuir um endereço IP específico à primeira instância.

Configuração de IP Dedicado

As etapas que executo para configurar uma única instância para um endereço IP dedicado são:

1. Abra o SQL Server Configuration Manager (SSCM)

2. Navegue para baixo na árvore para:

   SQL Server Configuration Manager (local)
   +-- SQL Server Network Configuration
       +-- Protocols for <INSTANCENAME>
   

3. No painel direito, clico duas vezes em TCP/IP que abre as Propriedades de TCP/IP na guia Protocolo

4. Em seguida, asseguro-me de que Enabled esteja definido como yes e a opção Listen All esteja definida como No .

5. Eu troco as guias para os endereços IP e asseguro que

   • Enabled está definido como Não para todos os endereços IPv4 e IPv6, exceto para o endereço IP que desejo atribuir a esta instância. Para este endereço IP, defino a configuração como Sim .
   • As portas dinâmicas TCP estão vazias para todos os endereços IP.
   • Para o endereço IP que desejo atribuir a esta instância, atribuo a porta TCP 1433 .

6. Em seguida, fecho todas as janelas e o SSCM exibe a mensagem padrão:

   ---------------------------
   Warning
   ---------------------------
   Any changes made will be saved; however, they will not take 
   effect until the service is stopped and restarted.
   ---------------------------
   OK   
   ---------------------------
   

7. Eu reinicio a instância específica e geralmente permite conexões com minha instância por meio do endereço IP dedicado.

8. Eu testo se a instância está escutando no endereço IP e na porta fornecidos com:

   c:\> netstat -abon
   

   TCP    10.58.212.112:1433     0.0.0.0:0              LISTENING         2172
   [sqlservr.exe]
   

^{Os resultados acima são de um servidor em funcionamento}

Até agora tudo bem. Já fiz isso pela enésima vez e nunca tive problemas.

Caso

Dois dias atrás, o serviço do SQL Server para uma instância recém-configurada em um servidor de teste não iniciaria após a configuração do endereço 10.58.194.5 para a instância. Eu tentei as seguintes etapas para corrigir a instância de teste "quebrada" :

1. Reparar ferramentas compartilhadas
2. Reparar instância
3. Desinstalar e reinstalar o SQL Server
4. Execute um ProcMon para descobrir onde as coisas foram quebradas.

Mas nada ajudou até agora.

A saída relevante do arquivo ERRORLOG é a seguinte:

2021-12-17 08:00:03.76 spid17s     Error: 26024, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     Server failed to listen on 10.58.194.5 <ipv4> 1433. Error: 0x2741. 
                                   To proceed, notify your system administrator.
2021-12-17 08:00:03.76 spid17s     Error: 17182, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     TDSSNIClient initialization failed with error 0x2741, status code 0xa. 
                                   Reason: Unable to initialize the TCP/IP listener. 
2021-12-17 08:00:03.76 spid17s     Error: 17182, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     TDSSNIClient initialization failed with error 0x2741, status code 0x1. 
                                   Reason: Initialization failed with an infrastructure error. Check for previous errors. 
2021-12-17 08:00:03.76 spid17s     Error: 17826, Severity: 18, State: 3.
2021-12-17 08:00:03.76 spid17s     Could not start the network library because of an internal error in the network library. 
                                   To determine the cause, review the errors immediately preceding this one in the error log.
2021-12-17 08:00:03.76 spid17s     Error: 17120, Severity: 16, State: 1.
2021-12-17 08:00:03.76 spid17s     SQL Server could not spawn FRunCommunicationsManager thread. 
                                   Check the SQL Server error log and the operating system error log for information about possible related problems.

Esta é a primeira vez que não consigo configurar/definir uma instância do SQL Server para um endereço IP específico.

Entradas de registro de eventos

Os seguintes erros são registrados no log de eventos do aplicativo do Windows ao tentar iniciar o serviço (o mais recente primeiro):

Level | Date / Time         | Source               | Event ID
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17120
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17826
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17182
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 17182
Error | 17.12.2021 11:00:45 | MSSQL$<INSTANCENAME> | 26024

Essas identificações de eventos se assemelham às informações do ERRORLOG.

A entrada adicional no log de eventos do sistema é:

Level | Date / Time         | Source                  | Event ID
Error | 17.12.2021 11:00:45 | Service Control Manager | 7024 

Os detalhes sendo:

The service "SQL Server (<INSTANCENAME>)" has been stopped with the following error: 
The requested address is invalid in this context.

Os detalhes do XML são:

- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
- <System>
  <Provider Name="Service Control Manager" Guid="{555908d1-a6d7-4695-8e1e-26931d2012f4}" EventSourceName="Service Control Manager" /> 
  <EventID Qualifiers="49152">7024</EventID> 
  <Version>0</Version> 
  <Level>2</Level> 
  <Task>0</Task> 
  <Opcode>0</Opcode> 
  <Keywords>0x8080000000000000</Keywords> 
  <TimeCreated SystemTime="2021-12-17T10:00:45.876740800Z" /> 
  <EventRecordID>15418</EventRecordID> 
  <Correlation /> 
  <Execution ProcessID="736" ThreadID="372" /> 
  <Channel>System</Channel> 
  <Computer>SERVER.DOMAIN.TLD</Computer> 
  <Security /> 
  </System>
- <EventData>
  <Data Name="param1">SQL Server (<INSTANCENAME>)</Data> 
  <Data Name="param2">%%10049</Data> 
  <Binary>4D005300530051004C00240041004C004C00470045004D00450049004E000000</Binary> 
  </EventData>
  </Event>

Configuração da placa de interface de rede

Há apenas uma NIC configurada no servidor de teste . E é virtual. O nome é Ethernet0 e o tipo é Adaptador Ethernet para vmxnet3 .

A rede está configurada da seguinte forma:

IP address:        10.58.194.4
Subnet mask:       255.255.255.192
Standard gateway:  10.58.194.2

Os endereços IP adicionais que estamos usando para as instâncias do SQL Server são:

10.58.194.5 / 255.255.255.192
10.58.194.6 / 255.255.255.192
10.58.194.7 / 255.255.255.192
10.58.194.28 / 255.255.255.192
10.58.194.29 / 255.255.255.192

A única NIC está configurada para aceitar conexões para os seis endereços IP.

Pergunta

Alguém tem alguma ideia de como posso resolver este problema?

Informações Adicionais via Comentários

Se eu definir a opção Listen All como Yes na configuração TCP/IP, a instância será iniciada. Mas não é isso que procuro. Não consigo me conectar à instância usando o alias ou o endereço IP. O processo sqlserver.exe não está mais escutando na porta 1433.

Depois de contemplar minha observação de que o processo sqlserver.exe não está mais ouvindo na porta 1433 , parece que o servidor não consegue vincular o endereço IP à porta 1433 para minha primeira instância.

Depois de configurar Listen All para yes e o endereço IP para ter port 1433, então sqlserver.exenão escuta em nenhum endereço IP ( 0.0.0.0) para port 1433. É como se a configuração da caixa de entrada para o número da porta estivesse corrompida. O SQL Server ERRORLOG exibe:

2021-12-17 16:27:36.35 spid17s     Error: 26058, Severity: 16, State: 1.   
2021-12-17 16:27:36.35 spid17s     A TCP provider is enabled, but there are no TCP listening ports configured. 
                                   The server cannot accept TCP connections.`

Notas Laterais

Consegui configurar e configurar um servidor produtivo , que recebi no mesmo dia, para usar um IP específico sem problemas. A única diferença foi talvez que os seis endereços IP que recebi para o servidor produtivo , estavam em ordem sequencial assim:

10.xx.xxx.111 / 255.255.255.0 -- server
10.xx.xxx.112 / 255.255.255.0 -- instance_1
10.xx.xxx.113 / 255.255.255.0 -- instance_2
10.xx.xxx.114 / 255.255.255.0 --...
10.xx.xxx.115 / 255.255.255.0
10.xx.xxx.116 / 255.255.255.0

Visão geral

Eu tenho um banco de dados de fotos que cataloga informações de fotos tiradas com câmeras. O banco de dados contém uma tabela picturescom 256 colunas que contêm informações sobre a foto que foi tirada.

Uma coluna Commentsé formatada como ntexte contém CR/LFs.
Existem outras 21 colunas que foram configuradas como ntext.

Eu extraio os dados do banco de dados em um arquivo simples usando a Tasks | Export Data...função encontrada no SSMS. Os dados exportados são então transferidos por um parceiro externo para um novo sistema que será usado em um futuro próximo. O arquivo de exportação (CSV) contém cerca de 256 colunas com 21 colunas que podem conter CR/LFs.

Problema

O Commentscampo/coluna contém vários CR/LFs (SQL: CHAR(13), CHAR(10)) que estão impactando a análise dos dados.

Tentei usar o REPLACE(expression, value, value)para procurar o CR/LF e substituí-lo @@e estava pensando em implementar isso durante a exportação usando Export Datano SSMS.

No entanto, a REPLACE()função retorna um

Msg 8116, Level 16, State 1, Line 4 O
tipo de dados do argumento ntext é inválido para o argumento 1 da função de substituição.

...quando executo algo como:

SELECT 'Start *******************', REPLACE(Comment,'
','@@'), ID, '********************End' FROM dbo.pictures
WHERE Comment LIKE '%
%';

Dados de amostra do comentário da coluna

Emitindo a seguinte declaração:

SELECT Comment FROM dbo.Pictures
WHERE Comment like '%
%';

...recuperará o seguinte registro de amostra Comment:

Zwei Fotos von Arenenberg auf einer Seite einer englischen Zeitschrift.
Seite 148 der Zeitung "The Graphic" vom 4. August 1906 = News from Abroad.
"The last stage of all": the retreat for aged actors opened last week near Meaux, in France
1. General view of the home
2. M. Coquelin reciting in the open-air theater

The château of Arenenberg which has been presented by the Empress Eugénie to the canton of Thurgovie
3. View from the chateau [Arenenberg] over Lake Constance
4. The château of Arenenberg
The Empress Eugénie has presented to the Swiss Canton of Thurgovie the historical château of Arenenberg, where Napoleon III. passed several years of his youth. Queen Hortense, on the fall of the first Empire, fled to Switzerland, and in 1817 purchased the castle, which is delightfully situated on the shore of Lake / Constance. The gift includes a priceless collection of paintings, manuscripts, books, old furniture, and tapestries, among the mos important souvenirs being the camp bed of Napoleon III., and the carriage in which he left Sedan after his defeat. When the alterations are complete the château will be opened to the public.

5. The maiden voyage of the new Santos-Dumont flying machine
6. The room in Viborg where the dissolved Duma met
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          .

Sim, há uma linha vazia. O ponto final foi inserido por mim para exibir o comprimento do texto.

Expressões regulares

Exportei os dados e executei várias expressões regulares para omitir o CR/LF dos dados. Como a coluna Commentestá no meio dos dados, tive que tentar várias strings regex:

Strings de pesquisa

([a-zA-Z0-9/,.@():;\s]+)(\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([\w/,.@():;\s']+)(\r\n)([\w/,.@():;"\s]+)
(;")(\r\n)(";)
(;")(\r\n)([\w/,.@():;\s']+)
(\w")(\r\n)([\w/,.@():;\s']+)

Substituir

\1@@\3

Esta solução não foi muito eficaz nem eficiente, pois levou várias execuções e horas para arrumar o arquivo CSV.

Pergunta

Como posso substituir o CR/LF em uma ntextcoluna @@durante uma exportação para um arquivo simples CSV? Existe uma opção diferente da REPLACEque eu poderia usar?

Limitações/Observações

• O Integration Services não foi instalado, portanto, não é uma opção.
• A string ntext pode conter ", 'e “, ”, e ‘, ’claro, o opcional «e ».
• Analisei os dados na ntextcoluna Commentusando a instrução SELECT MAX(DATALENGTH(Comment)) FROM Pictures;e recebi o feedback de que um registro contém 5.562 caracteres.

Eu criei um db<>fiddle com uma definição básica da tabela e os dados de exemplo junto com a REPLACE()declaração com falha.

Pergunta

Como posso inserir CAST(event_data as XML)dados em uma coluna recém-adicionada em uma tabela temporária com a seguinte instrução:

INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins

...sem receber a seguinte mensagem de erro:

Msg 515, Level 16, State 2, Line 36
Cannot insert the value NULL into column 'module_guid', table 'tempdb.dbo.#Unique_Logins______________________________________________________________________________________________________00000000000F'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Passos para reproduzir

Estou tentando ler algum XML de um arquivo XEL importado. Ao fazer isso, tenho que criar uma nova coluna para converter os dados de nvarchar(max)para XML.

1. Vamos ver como podemos importar um arquivo de log de eventos estendido

sp_help fn_xe_file_target_read_file

Informações gerais

Name                        | Owner   | Type            | Created_datetime
----------------------------+---------+-----------------+------------------
fn_xe_file_target_read_file | sys     | inline function | 2014-02-20 20:48:46.370

Colunas da função inline

Column_name  | Type             | Computed | Length | Prec  | Scale | Nullable | TrimTrailingBlanks | FixedLenNullInSource | Collation
-------------+------------------+----------+--------+-------+-------+----------+--------------------+----------------------+------------------------------
module_guid  | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
package_guid | uniqueidentifier | no       | 16     |       |       | no       | (n/a)              | (n/a)                | NULL
object_name  | nvarchar         | no       | 120    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
event_data   | nvarchar         | no       | -1     |       |       | yes      | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_name    | nvarchar         | no       | 520    |       |       | no       | (n/a)              | (n/a)                | SQL_Latin1_General_CP1_CI_AS
file_offset  | bigint           | no       | 8      | 19    | 0     | no       | (n/a)              | (n/a)                | NULL

Parâmetros da função inline

Parameter_name     | Type     | Length | Prec | Scale | Param_order | Collation
-------------------+----------+--------+------+-------+-------------+----------------
@path              | nvarchar | 520    | 260  | NULL  | 1           | Latin1_General_CS_AS
@mdpath            | nvarchar | 520    | 260  | NULL  | 2           | Latin1_General_CS_AS
@initial_file_name | nvarchar | 520    | 260  | NULL  | 3           | Latin1_General_CS_AS
@initial_offset    | bigint   | 8      | 19   | 0     | 4           | NULL

2. Insira os dados do arquivo XEL na tabela temporária #Unique_Logins

select * into #Unique_Logins from sys.fn_xe_file_target_read_file(N'C:\temp\Unique_Logins_0_132175196428210000.xel',NULL,NULL,NULL)

Atenção:
importei um arquivo XEL de 2 GB que resultou em uma tabela temporária de 24 GB !

3. Selecione na tabela temporária para ver quais colunas temos

select TOP 2 * from #Unique_Logins

module_guid                          | package_guid                         | object_name | event_data                                                                                                                                                                                                                                                                                            | file_name                                      | file_offset
-------------------------------------+--------------------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+------------
CE79811F-1A80-40E1-8F5D-7445A3F375E7 | 655FD93F-3364-40D5-B2BA-330F7FFB6491 | login       | <event name="login" package="sqlserver" timestamp="2019-11-06T13:14:02.835Z">...<action name="database_name" package="sqlserver"><value><![CDATA[DatabaseName1]]></value></action>...<action name="server_principal_name" package="sqlserver"><value><![CDATA[DB_USER1]]></value></action>...</event> | C:\temp\Unique_Logins_0_132175196428210000.xel | 6656
CE79811F-1A80-40E1-8F5D-7445A3F375E7 | 655FD93F-3364-40D5-B2BA-330F7FFB6491 | login       | <event name="login" package="sqlserver" timestamp="2019-11-06T13:14:02.894Z">...<action name="database_name" package="sqlserver"><value><![CDATA[DatabaseName2]]></value></action>...<action name="server_principal_name" package="sqlserver"><value><![CDATA[DB_USER2]]></value></action>...</event> | C:\temp\Unique_Logins_0_132175196428210000.xel | 6656

Agora os dados parecem ser XML. Vamos verificar se ele foi importado como XML.

4. Recupere os atributos da tabela temporária

USE TEMPDB --caution; temporary table is in tempdb
GO
SELECT sc.name, 
       st.name, 
       st.max_length, 
       st.collation_name, 
       sc.column_id, 
       sc.collation_name, 
       sc.max_length, 
       sc.is_nullable, 
       sc.is_xml_document
FROM sys.columns AS sc JOIN sys.types AS st 
    ON sc.user_type_id = st.user_type_id WHERE sc.object_id = OBJECT_ID('#Unique_Logins')

name         | name             | max_length | collation_name       | column_id | collation_name       | max_length | is_nullable | is_xml_document
-------------+------------------+------------+----------------------+-----------+----------------------+------------+-------------+----------------
module_guid  | uniqueidentifier | 16         | NULL                 | 1         | NULL                 | 16         | 0           | 0
package_guid | uniqueidentifier | 16         | NULL                 | 2         | NULL                 | 16         | 0           | 0
object_name  | nvarchar         | 8000       | Latin1_General_CS_AS | 3         | Latin1_General_CS_AS | 120        | 0           | 0
event_data   | nvarchar         | 8000       | Latin1_General_CS_AS | 4         | Latin1_General_CS_AS | -1         | 1           | 0
file_name    | nvarchar         | 8000       | Latin1_General_CS_AS | 5         | Latin1_General_CS_AS | 520        | 0           | 0
file_offset  | bigint           | 8          | NULL                 | 6         | NULL                 | 8          | 0           | 0

Os dados XML na coluna event_dataforam importados para uma nvarchar(max)coluna.

5. Crie uma nova coluna event_data_xmlcomo XML e insira os dados da coluna event_datausandoCAST()

ALTER TABLE #Unique_Logins ADD event_data_xml XML
GO
INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(event_data AS XML) FROM #Unique_Logins

Mensagem de erro

Msg 515, Level 16, State 2, Line 36
Cannot insert the value NULL into column 'module_guid', table 'tempdb.dbo.#Unique_Logins______________________________________________________________________________________________________00000000000F'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Não vejo o que module_guidtem a ver com a instrução INSERT/SELECT.

Atualmente, estou investigando um aplicativo que parece gerar 99% de planos de consulta ad hoc em relação ao banco de dados que está consultando. Posso verificar isso executando a seguinte instrução para recuperar um resumo dos objetos no cache do plano de consulta:

_{Desculpe, não foi possível inserir o código no editor SE, daí a captura de tela}

^{Referência: planejar o cache e otimizar para cargas de trabalho ad hoc (SQLSkills.com / K. Tripp) com pequenas modificações}

Os resultados da consulta acima são os seguintes:

CacheType            Total Plans          Total MBs                               Avg Use Count Total MBs - USE Count 1                 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- ------------- --------------------------------------- -------------------------
Adhoc                158997               5749.520042                             2             2936.355979                             126087
Prepared             1028                 97.875000                               695           46.187500                               576
Proc                 90                   69.523437                               39659         21.187500                               21
View                 522                  75.921875                               99            0.453125                                3
Rule                 4                    0.093750                                22            0.000000                                0
Trigger              1                    0.070312                                12            0.000000                                0    

Das 158.997 consultas ad hoc no cache do plano, 126.087 consultas foram executadas apenas uma vez.

Em um exame mais aprofundado das consultas ad hoc, descobri que algumas consultas são geradas várias vezes. Examinei o cache do plano com a seguinte consulta para recuperar planos de execução idênticos:

SELECT SUM(cplan.usecounts) AS [Unique Same Single Plans],
       qtext.text
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
             CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'   -- <-- only Adhoc plans
       AND sdb.name = 'DATABASENAME' -- <-- for a certain database
       AND cplan.usecounts = 1       -- <-- with a usecounts of 1
GROUP BY
       qtext.text having sum(cplan.usecounts) > 1
ORDER BY
       1 DESC --,cplan.objtype, cplan.usecounts   

^{Referência: Não me lembro. Deixe-me saber se foi originalmente seu e eu vou atribuí-lo.}

Isso me fornece uma lista de consultas ad hoc que têm um plano de consulta idêntico a um plano de consulta idêntico existente e a soma de planos de consulta idênticos exclusivos no cache do plano.

Como você pode ver nos GUIDs editados, há muitos planos de consulta ad hoc exclusivos que foram criados várias vezes.

Para provar que estou indo na direção certa, peguei uma declaração de cima que tinha uma contagem única de 3 e usei a declaração como um filtro na minha declaração de resumo do cache do plano para recuperar as declarações e os planos de consulta:

SELECT cplan.usecounts,
       qtext.text,
       qplan.query_plan
FROM   sys.dm_exec_cached_plans AS cplan
       CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
       CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
       JOIN sys.databases AS sdb
            ON  sdb.database_id = qplan.dbid
WHERE  1 = 1
       AND cplan.objtype = 'Adhoc'
       AND sdb.name = 'DATABASENAME'
       AND qtext.text = 
           'SELECT description,id,name,osguid,profil FROM omitted WHERE osguid IN (SELECT osgroupguid FROM omitted WHERE osuserguid=''81C4B8_REMOVED_SOME_9DD2'')'
ORDER BY
       1 DESC

^{Referência: Não me lembro. Deixe-me saber se foi originalmente seu e eu vou atribuí-lo.}

Isso me fornece uma lista de consultas ad hoc exclusivas que foram criadas e armazenadas no cache do plano:

Agora, os números na captura de tela acima mostram que uma consulta já foi reutilizada novamente, porque tem uma contagem de 3. No entanto, todas as consultas são idênticas.

Agora, pelo que li até agora, suponho que:

• consultas adhoc são consultas que foram passadas para o SQL Server Query Optimizer pela primeira vez em sua vida (possivelmente curta)
• instruções que não possuem parâmetros são consideradas exclusivas e resultarão na Adhoccriação de uma entrada no cache do plano
• consultas adhoc podem ser triviais, o que resulta na criação de planos de consulta individuais para cada instrução, mesmo que sejam idênticos

Estou igualmente ciente de que:

• a ativação optimize for ad hoc workloadsresultará em uma ligeira redução no tamanho dos planos de consulta no cache para planos ad hoc que são usados ​​apenas uma vez
• correr ALTER DATABASE [DATABASENAME] SET PARAMETERIZATION FORCEDpode ser uma boa ideia no meu caso, mas isso...
  • existem restrições (veja os artigos de BrentOzar)
  • parametrização no programa seria melhor

Perguntas

Depois de ler todos os artigos e algumas perguntas relacionadas que surgiram enquanto digitava esta pergunta, tenho as duas perguntas a seguir:

1. Em quais casos os planos de consulta Adhoc não parametrizados e não triviais são reutilizados?
2. Por que existem vários planos de consulta em cache para instruções idênticas?

Percebo que minhas perguntas são contraditórias, devido ao fato de que os planos de consulta não parametrizados são considerados exclusivos, mas por que alguns planos de consulta adhoc não parametrizados estão sendo reutilizados novamente?

Em resposta ao comentário de @DenisRubashikin:

Salve planos para consultas 'idênticas' em formato XML e compare os arquivos, acho que pode haver alguma diferença (em opções de conjunto, por exemplo) – Denis Rubashkin 29 minutos atrás

A opção SET são idênticas. As únicas diferenças em todos os planos estão na segunda linha após a <StatementSetOptions>seção em CompileTimee CompileCPU. Colei as duas partes relevantes abaixo:

QueryPlan1.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="472">

QueryPlan2.xml

      <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
      <QueryPlan CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="472">

Nenhuma outra diferença encontrada.

Material de referência usado para selecionar esta pergunta:

• Planeje o cache e a otimização para cargas de trabalho ad hoc (SQLSkills.com)
• Guia de arquitetura de processamento de consultas (Microsoft SQL Docs)
• Opções ALTER DATABASE SET (Transact-SQL) (Microsoft SQL Docs)
• Por que vários planos para uma consulta são ruins (BrentOzar.com)
• A parametrização forçada pode dar errado? (BrentOzar. com)
• Resultado do Blitz: Parametrização Forçada (Brentoar.com)

É possível agrupar por elementos (como em COLUMN LIKE='Value%') em uma PIVOTtabela? Eu tenho uma tabela [DBT].[Status] que contém vários status (de bancos de dados, instâncias, etc.) e não quero dinamizar/consultar todos os valores PROD e TEST como valores únicos, mas agrupá-los.

Por exemplo, em vez de ter colunas para os status Prod, Prod ACC, Prod APP, .. etc. eu teria apenas uma coluna contendo os valores para Name LIKE 'Prod%'e Name LIKE 'Test%'.

O que tenho até agora:

Definição de tabela

CREATE TABLE [DBT].[Status](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
 CONSTRAINT [IX_Status] UNIQUE NONCLUSTERED 
(
    [Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

Valores da tabela

INSERT INTO [DBT].[Status]
(
    -- ID -- this column value is auto-generated
    Name
)
VALUES
('Test ACC'),
('Test APP'),
('Test DBA'),
('Prod ACC'),
('Prod APP'),
('Prod DBA'),
('Prod'),
('Test'),
('Migrated'),
('Offline'),
('Reserved')

A Tabela de Status Dinâmica

SELECT 'Database Status' AS [DB Status], 
[1] AS [Test ACC], [2] AS [Test APP], [3] AS [Test DBA], [4] AS [Prod ACC], [5] AS [Prod APP], [6] AS [Prod DBA], [7] AS [Prod], [8] AS [Test], [9] AS [Migrated], [10] AS [Offline], [11] AS [Reserved] 
FROM 
(
    SELECT ID, Name  FROM [DBT].[Status]
) AS Source
PIVOT
(
    COUNT(Name) FOR ID IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable

Saída até agora

DB Status       Test ACC    Test APP    Test DBA    Prod ACC    Prod APP    Prod DBA    Prod        Test        Migrated    Offline     Reserved
--------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
Database Status 1           1           1           1           1           1           1           1           1           1           1

db<>violino

O dbfiddle até agora.

Pergunta

Em vez de ter várias linhas para os vários valores Test... e Prod...., prefiro agrupá-los, semelhante ao seguinte:

DB Status       | Test | Prod | Migrated | Offline | Reserved   
--------------- | ---- | ---- | -------- | ------- | --------
Database Status |    4 |    4 |        1 |       1 |        1

Não tenho a menor ideia de como resolver minha dúvida. (Para ser honesto, acabei de entender o PIVOT ontem, após extensas tentativas e erros).

Esta pergunta está vagamente relacionada à pergunta Como criar somas/contagens de itens agrupados em várias tabelas que já perguntei. As tabelas [DBT].[Instance] e [DBT].[Database] contêm uma coluna com o [StatusID] que corresponde à tabela que estamos vendo agora.

Questão simples

Como o SQL Server Quantum (4 ms) é sincronizado com o Server OS Quantum (normalmente: 187,5 ms)?

Pergunta simples explicada

Após 184 ms do quantum do SO sendo usado (o que corresponde a 46 quantums SQL completos), o quantum do SO tem 3,5 ms de tempo antes de ter que entregar o agendamento para um processo diferente. O SO SQL inicia um quantum (4 ms) e após 3,5 ms, o quantum do SO decidiu parar o thread atual do SO SQL que ainda tem 0,5 ms antes de produzir o agendamento. O que acontece agora?

Mergulho profundo no OS Quantum

Nas próximas seções, escreverei o que encontrei até agora sobre o quantum do SO e como a duração de um quantum pode ser calculada. A duração de um sistema operacional "quântico" é baseada em "ticks" e a duração do "tick" em si é baseada no "intervalo de clock" que normalmente é 15,625000 ms. Mas deixe-me explicar um pouco...

Carraça

No artigo do blog Know Thy Tick , o autor Jim explica os fundamentos dos intervalos de relógio (também conhecidos como "ticks") e para que servem.

Quando leio algo como “o intervalo do relógio… para a maioria dos multiprocessadores x86 é de cerca de 15 milissegundos”, sou compelido a determinar o valor do meu relógio, ou “tick”, intervalo. Felizmente, o livro em que li esta citação, Windows Internals Fourth Edition, fornece uma referência para me ajudar com minha aflição. ... O autor, Mark Russinovich, do livro acima mencionado graciosamente disponibilizou o utilitário ClockRes em seu site. Executando este utilitário, consegui determinar que o intervalo de clock no meu PC multiprocessador x86 é de 15,625000 ms. Interessante, mas minha mente curiosa quer saber mais.

Quântico

O autor do artigo continua explicando em seu segundo artigoeste...

É claro que a verdadeira razão pela qual o intervalo de ticks é importante é que ele afeta o agendamento de threads . O agendador do Windows dá a cada thread um “quantum” de tempo para executar antes de permitir que outra tarefa, no mesmo nível de prioridade, seja executada. O quantum que o escalonador atribui a um encadeamento é um múltiplo do intervalo de escala . O valor quântico específico escolhido para um segmento específico está um pouco além de onde quero ir com este artigo.

Ok, então eu sei o que é um quantum, mas não quanto tempo um quantum vai durar.

Por enquanto, vamos apenas examinar o valor quântico padrão para um thread de primeiro plano no XPe. Nesse caso, o agendador do Windows atribui um quantum de intervalos de 18 ou 6 ticks. (Sim, para converter quantum em intervalos de ticks, deve-se dividir por 3. ..., mas a razão para o múltiplo é permitir que o escalonador a capacidade de “carregar” um thread para fazer uma operação que faz com que ele seja suspenso.)

Agora sabemos que um intervalo de clock (tick) deve ser em torno de 15,625000 ms e em um sistema operacional Windows Desktop, onde o quantum padrão é 18, isso resultará em 6 ticks ou 93,750000 ms (18 / 3 * 15,625000 ms).

Em um sistema operacional Windows Server, o quantum padrão é diferente. A configuração "Agendamento do processador" está definida como "Serviços em segundo plano"

^{Essa configuração pode ser encontrada em "Configurações do sistema | Avançado (guia) | Desempenho (seção) | Configurações ..." que abrirá "Opções de desempenho | Avançado (guia) | Agendamento do processador"}

As configurações quânticas padrão são, então, de 36 (Background) a 36 (Foreground). O quantum é maior e, portanto, mais longo. Isso é o dobro da quantidade de 93,750000 ms da configuração de primeiro plano quântico de 18 (6 ticks) em um sistema operacional de desktop Windows, que em um sistema operacional de servidor configurado para serviços em segundo plano é de cerca de 187,500000 ms.

Observação / Explicação

Quando você altera a configuração de "Serviços em segundo plano" para "Aplicativos" em um servidor ou área de trabalho, a chave HKLM\SYSTEM \CurrentControlSet\ Control\ PriorityControl\ Win32PrioritySeparation no registro é alterada de 0x18 para 0x02. Qual é o valor quântico padrão para 0x02? Isso pode ser encontrado em um comentário:

O valor 0x02 implica que os campos "Curto vs. Longo" e "Variável vs. Fixo" são o padrão para o SO.

O padrão para esses campos para XPe & XP Pro é: Short & Variable que é o mesmo que ter os seguintes bits adicionais definidos: 0x24.

OR'ing este valor com 0x02 dá 0x26, que você encontrará na tabela no artigo.

^{Referência: Comentário para "Domine seu Quantum" (Blogs do MSDN)}

A tabela explicando as configurações quânticas do mesmo artigo:

Win32PrioritySeparation   Foreground   Background
0x28, 0x29, 0x2A                  18           18
0x18, 0x19, 0x1A                  36           36
0x24                               6            6
0x25, 0x14                        12            6
0x26                              18            6
0x15                              24            6
0x16                              36            6

Breve Resumo do OS Quantum

Com base nas informações acima e citações de artigos, sabemos que um quantum não é um tamanho fixo, mas sim derivado de uma configuração do sistema operacional nas Propriedades do sistema. Um quantum varia de acordo com a Win32PrioritySeparationconfiguração no registro que normalmente corresponde a uma das configurações nas "Propriedades do sistema" (ou "Serviços em segundo plano" ou "Aplicativos").

Um quantum no nível do SO é

• para a configuração "Aplicativos"
  • 18 (que é 6 ticks) para aplicativos em primeiro plano (93,75 ms)
  • 6 (que é 2 ticks) para aplicativos em segundo plano (31,25 ms)
• para a configuração "Serviços em segundo plano"
  • 36 (que é 18 ticks) para aplicativos em primeiro plano (187,5 ms)
  • 36 (que é 18 ticks) para aplicativos em segundo plano (187,5 ms)

Portanto, agora sabemos que um quantum de SO em uma configuração do Windows Server a ser otimizado para serviços em segundo plano é ...

36 / 3 * 15.625000 ms = 187.5 ms

SQL OS Quantum

Esta seção lista o que encontrei no SQL OS quantum ...

Tipo de espera SOS_SCHEDULER_YIELD

Da descrição de Paul Randall no tipo de espera SOS_SCHEDULER_YIELD:

Esse tipo de espera é quando um encadeamento foi capaz de executar seu quantum de encadeamento completo (4 milissegundos em todas as versões do SQL Server, imutável ) e, assim, voluntariamente cedeu o agendador, movendo-se para a parte inferior da Fila Executável em seu agendador.

^{Referência: SOS_SCHEDULER_YIELD (tipos de espera SQLSkills.com)}

Agendadores em DMVs do SQL Server

Em uma explicação sobre DMVs do SQL Server para o DMV sys.dm_os_schedulers.

[...] o Windows usa um mecanismo de agendamento preemptivo e atribui um quantum de tempo de CPU a cada thread, quando um thread consome seu quantum ele é enviado para uma fila e outros threads recebem execução.

Em oposição, o SQL Server usa um mecanismo de agendamento cooperativo quando os threads podem voluntariamente render seu quantum de tempo (você pode ver esse comportamento quando tiver um tipo de espera SOS_SCHEDULER_YIELD). Isso permite que o SQL Server otimize a utilização da CPU, pois quando um thread é sinalizado para execução, mas não está pronto para ser executado, ele pode render seu quantum de tempo em favor de outros threads .

^{Referência: Noções básicas sobre agendadores, trabalhadores e tarefas do SQL Server (MSSQLTips.com)}

Detectar pressão da CPU do SQL Server

Esta é uma seção muito pequena de um artigo sobre a pressão da CPU no SQL Server.

Ocorre quando uma tarefa voluntariamente cede o agendador para que outras tarefas sejam executadas. Durante essa espera, a tarefa aguarda a renovação de seu quantum .

^{Referência: Detectar pressão de CPU do SQL Server (MSSQLTips.com)}

sys.dm_os_schedulers (Documentos da Microsoft)

Acho que a citação a seguir é o trecho mais importante de informações sobre o quantum do SQL OS que pude encontrar:

quantum_length_us bigint  Identified for informational purposes only. 
                          Not supported. Future compatibility is not guaranteed. 
                          Exposes the scheduler quantum used by SQLOS.

^{Referência: sys.dm_os_schedulers (Transact-SQL) (Microsoft | Docs)}

Meu enigma

O Server OS Quantum regula quanto tempo o SQL Server Service é concedido para executar "tarefas". O SQL Server OS Quantum é definido como 4 ms. Se eu dividir os 187,5 ms por 4 ms, fico com 3,5 ms.

E ainda nem começamos a discussão de quando o Intervalo do Relógio está definido para algo diferente do padrão de 15,625000 ms....

Questão simples

Como o SQL Server Quantum (4 ms) é sincronizado com o Server OS Quantum (normalmente: 187,5 ms)?

Pergunta simples explicada

Após 184 ms do quantum do SO sendo usado (o que corresponde a 46 quantums SQL completos), o quantum do SO tem 3,5 ms de tempo antes de ter que entregar o agendamento para um processo diferente. O SO SQL inicia um quantum (4 ms) e após 3,5 ms, o quantum do SO decidiu parar o thread atual do SO SQL que ainda tem 0,5 ms antes de produzir o agendamento. O que acontece agora?

TL;DR

Me deparei com um recurso no SQL Server Management Studio v17.9 que eu não conhecia. Parece um pouco com o EXPLAINencontrado no Oracle, PostgreSQL e MySQL. Onde esse recurso está documentado?

Como ativar o recurso

Eu estava executando a seguinte consulta com as opções Include Actual Execution Plan (Ctrl + M) e Include Live Query Statistics ativadas:

    SELECT sdes.session_id
          ,sdes.[status]
          ,sdes.login_name
          ,sdes.[host_name]
          ,sder.blocking_session_id
          ,sdb.name
          ,sdes.cpu_time
          ,sdes.logical_reads --opcionalmente: + sdes.reads + sdes.writes
          ,sdes.last_request_start_time
          ,sdes.program_name
          ,sdes.session_id
          ,sder.request_id
          ,destino.[texto]
    DE sys.dm_exec_sessions AS sdes
           LEFT JOIN sys.dm_exec_connections AS sdec
                ON sdes.session_id = sdec.session_id
           JOIN sys.databases AS sdb
                ON sdes.database_id = sdb.database_id
           LEFT JOIN sys.dm_exec_requests AS sder
                ON sdes.session_id = sder.session_id
           CROSS APPLY sys.dm_exec_sql_text(sdec.most_recent_sql_handle) AS dest
    ONDE 1=1
    AND sdb.name = ''

Resultado

Isso produziu quatro guias de resultados:

• Resultados
• Mensagens
• Estatísticas de consulta ao vivo
• Plano de execução

Aba Resultados Especiais

Quando mudei para a guia Resultados , notei um conjunto de resultados logo abaixo dos resultados reais que se parece com isso:

Observações

Se eu ativar a opção Incluir Plano de Execução Real (Ctrl + M) ou Incluir Estatísticas de Consulta ao Vivo , a Explicação do SQL Server não será exibida. O "SQL Server Explain" só é exibido se ambas as opções estiverem ativadas.

Perguntas

1. Este recurso pode ser ativado sem ter que ativar a opção Incluir Plano de Execução Real (Ctrl + M) e Incluir Estatísticas de Consulta ao Vivo ?
2. Onde esse pequeno recurso está documentado?

Pré-requisitos

Os seguintes componentes e/ou pacotes de software estão envolvidos no cenário:

Servidor

• Servidor Windows 2016
• SQL Server 2016 SP1

Cliente

• Windows 10 versão 1807 (compilação 17134)
• SQL Server Management Studio v17.9
  • Microsoft SQL Server Management Studio 14.0.17285.0
  • Ferramentas Cliente do Microsoft Analysis Services 14.0.1016.283
  • Componentes de acesso a dados da Microsoft (MDAC) 10.0.17134.1
  • Microsoft MSXML 3.0 6.0
  • Microsoft Internet Explorer 9.11.17134.0
  • Microsoft .NET Framework 4.0.30319.42000
  • Sistema operacional 6.3.17134

SQL Server Management Studio v17.9

• Incluir Plano de Execução Real (Ctrl + M ) ativado
• Incluir estatísticas de consulta ao vivo ativadas

TL;DR

Eu gostaria de adicionar o SQL Server 2014 Analysis Services a uma instância específica do SQL Server e configurá-lo de tal forma que o Analysis Services (SSAS) e o futuro Reporting Services (SSRS) apenas peguem solicitações na instância específica IP, para permitir que o SQL Server Browser Service permaneça no estado parado .

Pré-requisitos

Tenho os seguintes pré-requisitos em relação a um único Windows Server executando várias instâncias do SQL Server 2014:

Informação básica

- 2 x placas de rede
- 12 endereços IP
- 10 x Instâncias de Serviço do SQL Server Server (em execução)
- 10 x instâncias de serviço do SQL Server Agent Server (em execução)
- 1 x SQL Server Browser Service (não está em execução)

Configuração da placa de rede

As NICs do Windows Server são configuradas da seguinte forma:

- 1 x NIC (Rede de Backup)
    - 10.2.0.1
- 1 x NIC (LAN)
    - 10.2.0.2 (Servidor Windows)
    - 10.2.1.1 (SQL Server "Instance01"/CNAME: servername-ins01)
    - 10.2.1.2 (SQL Server "Instance02"/CNAME: servername-ins02)
    - 10.2.1.3 (SQL Server "Instance03"/CNAME: servername-ins03)
    - 10.2.1.4 (SQL Server "Instance04"/CNAME: servername-ins04)
    - 10.2.1.5 (SQL Server "Instance05"/CNAME: servername-ins05)
    - 10.2.1.6 (SQL Server "Instance06"/CNAME: servername-ins06)
    - 10.2.1.7 (SQL Server "Instance07"/CNAME: servername-ins07)
    - 10.2.1.8 (SQL Server "Instance08"/CNAME: servername-ins08)
    - 10.2.1.9 (SQL Server "Instance09"/CNAME: servername-ins09)
    - 10.2.1.10 (SQL Server "Instance10"/CNAME: servername-ins10)

Gerenciador de configuração do SQL Server

O SQL Server Configuration Manager é configurado da seguinte forma para cada "Instance nm " do SQL Server:

- Protocolo para 'Instance01'
    - Memória Compartilhada: Ativada
    - Pipes nomeados: Desativado
    - TCP/IP: Ativado

Propriedades TCP/IP 'Instance01' - Guia Protocolo

- Protocolo
    - Ativado: Sim
    - Manter vivo: 30000
    - Ouvir tudo: Não

Com as configurações acima, cada 'Instance nm ' ​​terá que ser configurado individualmente para responder a chamadas específicas de IP na porta configurada. (Observação: como um benefício adicional, cada instância poderá escutar na porta padrão do SQL Server 1433, o que, por sua vez, simplificará bastante a configuração do firewall, pois você poderá usar a regra de porta sql-server. Apenas dizendo .)

Propriedades TCP/IP 'Instance01' - Guia de Endereços IP

Para a entrada IP n que corresponde ao endereço IP da instância específica, as configurações são as seguintes:

- IPn
    - Ativo: Sim
    - Ativado: Sim
    - Endereço IP: 10.2.1.1
    - Portas TCP Dinâmicas:
    - Porta TCP: 1433
- IPTodos
    - Portas TCP Dinâmicas:
    - Porta TCP:

Qualquer outra entrada IP n não relacionada ao endereço IP específico da instância será configurada da seguinte forma:

- IPn
    - Ativo: Sim
    - Ativado: Não
    - Endereço IP: 10.2.1.xx
    - Portas TCP Dinâmicas:
    - Porta TCP:
 

Resumo rápido

Com as configurações e configurações acima, cada instância do SQL Server será executada em/com um endereço IP dedicado na porta padrão do SQL Server 1433. Cada instância possui seu próprio CNAME, que permite que os usuários se conectem aos SQL Servers:

• servername-ins01
• servername-ins02
• servername-ins03
• servername-ins04
• servername-ins05
• servername-ins06
• ...

...sem precisar especificar a porta ou o nome da instância. Outra vantagem é que o SQL Server Browser Service não precisa estar em execução para distribuir chamadas para instâncias do SQL Server, porque cada chamada é roteada diretamente para o SQL Server (serviço) específico da instância.

Adicionar Analysis Services a uma instância

O fornecedor de software agora gostaria de aumentar a experiência do usuário e fornecer a capacidade de executar algumas consultas analíticas nos dados existentes. O fornecedor do software exige que instalemos o SQL Server Analysis Services. Este é um recurso específico da instância e, durante o processo de instalação, você deve selecionar a instância à qual adicionará o recurso.

Configuração do firewall

Para permitir conexões com o Analysis Services é necessário configurar a seguinte regra no firewall:

10.2.0.2:2382 (Browser Service)

Se eu tivesse usado a instância padrão (MSSQLSERVER), eu poderia ter configurado a seguinte regra no firewall:

10.2.1.1:2383 (Default Instance)

Isso está de acordo com a seguinte documentação:

SQL Server 2005 Analysis Services (SSAS) Server Properties (SQL Server 2005, mas aparentemente ainda válido para as versões atuais)

Conectando-se aos Serviços de Análise Específicos da Instância

Após a finalização da instalação, você se conecta ao Analysis Services específico da instância com a seguinte sintaxe:

servername\Instance01

Serviço de navegador do SQL Server

Depois de configurar o Analysis Service específico da instância do SQL Server, notei que o serviço SQL Server Browser agora estava no estado de execução .

Ops, isso significa que as solicitações para o Analysis Services estão sendo roteadas por meio do endereço IP do Windows Server (10.2.0.2) para a instância específica do SQL Server Analysis Service.

Se eu parar o serviço SQL Server Browser, não poderei mais me conectar ao SQL Server Analysis Services via servername\Instance01 .

Considerações de segurança

De acordo com a documentação acima mencionada:

O serviço SQL Server Browser é compartilhado entre o mecanismo de banco de dados do SQL Server 2005 e o mecanismo do Analysis Services. Para o mecanismo de banco de dados, por motivos de segurança, é recomendável desativar o serviço SQL Server Browser . Isso pode criar um dilema porque você pode precisar dele para o mecanismo do Analysis Services . É uma prática recomendada ativar o serviço se você precisar dele, mas, caso contrário, deixe-o desativado.

Uau.

Descobertas

A instalação do SQL Server Analysis Services iniciou o serviço SQL Server Browser, criando uma consideração de segurança .

Perguntas

1. Posso configurar o SQL Server Analysis Services Instance01com o CNAME servername-ins01, para ser vinculado ao endereço IP 10.2.1.1?

2. Posso configurar o SQL Server Analysis Services da minha instância nomeada de forma que eu possa parar o serviço SQL Server Browser novamente?

Uma página de banco de dados do SQL Server é definida como tendo 8192 bytes de tamanho. Há algumas informações de cabeçalho que dizem ter 96 bytes de tamanho.

E se você já tentou criar uma tabela com mais de 8053 bytes de definições de coluna, você receberá a mensagem de erro:

Creating or altering table 'Generated_Data_GUID' failed because the 
minimum row size would be 8061, including 7 bytes of internal overhead. 
This exceeds the maximum allowable table row size of 8060 bytes.

A seguir está um exemplo de tabela DDL:

CREATE TABLE [dbo].[Generated_Data_GUID](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [SEQGUID] [uniqueidentifier] NOT NULL,
    [Data1] [char](4000) NULL,
    [Data2] [char](4000) NULL,
    [Data3] [char](9) NULL,
    [EntryDate] [datetime2](7) NULL
) ON [PRIMARY]

Com o DDL acima, se eu alterar a definição da coluna para column Data3to be char(10), vou receber a mensagem de erro.

Os tamanhos de byte para cada tipo de coluna são os seguintes:

int               :  4 bytes
uniqueidentifiere : 16 bytes
char(n)           :  n bytes
datetime2(n)      :  6 bytes if n < 3 
                     7 bytes if n = 3 or n = 4
                     8 bytes if n > 4

Se fizermos algumas contas simples, acabamos com o seguinte cálculo:

Page Size         : 8192 bytes
                   -----------
Header            :   96 bytes - 
Internal Overhead :    7 bytes - 
Max Size          : 8053 bytes - 
                   -----------
Missing Data      :   36 bytes
                   ===========

Pergunta

O que esses 36 bytes contêm?

Material de referência

• Guia de arquitetura de páginas e extensões (Microsoft Docs)
• Dentro do Storage Engine: Anatomia de uma página (SQLSkills.com)

Na pergunta Existe alguma função interna (oculta) no MS-SQL para retirar as aspas de nomes de objetos? o autor estava procurando conselhos sobre se havia uma função interna para "UNQUOTE" uma string entre aspas. O autor McNets havia notado que algumas funções internas podiam lidar com valores de parâmetros entre aspas (eg [MyTable]) e sem aspas (eg ) MyTablepassados ​​(eg OBJECT_ID()).

Na resposta ( 1 ) dada por David Browne - Microsoft foi feita a citação que:

...o inverso de QUOTENAME é PARSENAME, que tem a capacidade adicional de navegar por nomes de várias partes.

Eu discordei desta parte da resposta com o seguinte comentário:

Concordo que você pode modificar um argumento passado para PARSENAME()exibir as partes de um identificador de várias partes, seja SERVER, DATABASE, SCHEMAou OBJECTpart. Mas IMHO afirmando que PARSENAMEé o oposto de QUOTENAMEé um pouco exagerado.

Minha pergunta agora é:

A função é PARSENAME()o oposto de QUOTENAME()?

Quando o recurso FILESTREAM é ativado no Microsoft SQL Server 2012, o SQL Server criará um compartilhamento "oculto" no sistema. A participação é definida da seguinte forma:

Sharename          FILESTREAM_SHARE
Path               \\?\GLOBALROOT\Device\RsFx0320\<localmachine>\FILESTREAM_SHARE
Remark             SQL Server FILESTREAM share
Maximum users      unlimited
Users Caching      Manual caching of documents 
Permissions        NT-AUTHORITY\Authenticated Users, FULL

O nome é o nome do compartilhamento que você fornece ao configurar inicialmente FILESTREAM no SQL Server Configuration Manager . Mas para que serve?

Até aqui

Eu li toda a documentação disponível do FILESTREAM começando em:

• FILESTREAM (SQL Server)
• Ativar e configurar FILESTREAM
• Criar um banco de dados habilitado para FILESTREAM
• Criar uma tabela para armazenar dados FILESTREAM
• ... e tudo relacionado
• Compatibilidade do FILESTREAM com outros recursos do SQL Server
• FILESTREAM DDL, Funções, Procedimentos Armazenados e Visualizações
• Acesse dados FILESTREAM com OpenSqlFilestream
• o artigo SQL Server 2008 - Armazenamento FILESTREAM no SQL Server 2008
• e o artigo FILESTREAM (OLTP) --- um guia de referência técnica para projetar soluções OLTP de missão crítica que faz referência a muitos outros materiais

...mas não houve menção ao compartilhamento e o que ele faz ou para que serve. Você insere o nome e o SQL Server criará o compartilhamento sob o capô.

Banco de dados habilitado para FILESTREAM

Quando você cria um banco de dados habilitado para FILESTREAM, o banco de dados faz referência a um grupo de arquivos que faz referência a um diretório (recomendado em uma unidade separada) que não tem absolutamente nada a ver com o compartilhamento, que foi criado inicialmente durante a configuração de FILESTREAM.

Captura de tela Script de banco de dados habilitado para FILESTREAM

Sim, eu percebo que todos os caminhos estão em C:; é apenas um exemplo

O white paper de Paul Randall et al. continua explicando isso...

Os dados FILESTREAM são armazenados no sistema de arquivos em um conjunto de diretórios NTFS chamados contêineres de dados, que correspondem a grupos de arquivos especiais no banco de dados. O acesso transacional aos dados FILESTREAM é controlado pelo SQL Server e um driver de filtro do sistema de arquivos que é instalado como parte da habilitação do FILESTREAM no nível do Windows. O uso de um driver de filtro do sistema de arquivos também permite acesso remoto aos dados FILESTREAM por meio de um caminho UNC. O SQL Server mantém um link de classificação das linhas da tabela para os arquivos FILESTREAM associados a elas. Isso significa que excluir ou renomear qualquer arquivo FILESTREAM diretamente pelo sistema de arquivos resultará em corrupção do banco de dados.

... mais abaixo no documento (página 14) eles continuam com ...

Há um único driver de filtro do sistema de arquivos FILESTREAM para cada volume NTFS que possui um contêiner de dados FILESTREAM e também há um para cada versão do SQL Server que possui um contêiner de dados FILESTREAM no volume. Cada driver de filtro é responsável por gerenciar todos os contêineres de dados FILESTREAM desse volume, para todas as instâncias que usam uma versão específica do SQL Server.

Por exemplo, um volume NTFS que hospeda três contêineres de dados FILESTREAM, um para cada uma das três instâncias do SQL Server 2008, terá apenas um driver de filtro do sistema de arquivos FILESTREAM do SQL Server 2008.

Perguntas

1. É bom saber que o SQL Server tem tudo de bom e amarrado, mas o que esse compartilhamento realmente faz? É o chamado "driver de filtro do sistema de arquivos"?
2. Visto que qualquer usuário autenticado pode acessar o "compartilhamento", quais são as implicações de segurança?
3. O dispositivo RsFx0320 é um predecessor do formato de sistema de arquivos resiliente que foi introduzido com o Windows Server 2012?

Se você puder fornecer respostas às minhas perguntas, seria bom se você pudesse fornecer uma referência de fonte.

IMPORTANTE: O título pode ser enganoso.

Introdução

Adquiri o maravilhoso projeto de atualizar todas as nossas instâncias do SQL Server, o que significa que tenho que garantir que os Service Packs apropriados sejam aplicados a cada instância.

Temos um aplicativo de ferramentas de banco de dados (abreviado como DBT) que armazena todas as informações de nossos bancos de dados MySQL, PostgreSQL, Microsoft SQL Server e Oracle RDBMS em um único local.

Esse aplicativo DBT vincula um determinado banco de dados a um aplicativo, o banco de dados a uma instância e a instância a um servidor e, claro, o banco de dados a uma pessoa responsável.

Um banco de dados terá e pode ter muitas informações adicionais (versão do banco de dados, status, gerente de projeto, gerente de banco de dados, ...) que mantive fora da descrição para simplificar a explicação.

Para iniciar o projeto, eu queria gerar uma lista de SQL Servers exclusivos com uma soma dos bancos de dados e agrupados por todas as outras informações relevantes. A ideia era ter uma visão geral dos SQL Servers com mais bancos de dados e maior complexidade (usuários, aplicativos, instâncias).

TL;DR

Aqui está uma amostra de dados já resumidos e o que eu esperava alcançar

Conjunto de resultados de amostra

SRV_NAME             INST_NAME            DB_NAME              USER_NAME            APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            ANOTHER              HIS_DB               HIM                  TELLTAIL            
SQLSRV_01            ANOTHER              RZO_P4               YOU                  PSB IZQ             
SQLSRV_01            GENERAL              MY_DB2               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB3               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB4               ME                   HAL_2000            
SQLSRV_01            GENERAL              RZO_6_4              ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_1            ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_2            YOU                  RZO_6.4             
SQLSRV_01            GENERAL              YOUR_DB2             YOU                  HAL_2000            
SQLSRV_01            SECURE               DB1                  ME                   HAL_2000            
SQLSRV_01            SECURE               PURCHGRAV            HER                  PURCHGRAV           
SQLSRV_01            SECURE               TELLTAIL             HER                  TELLTAIL            

Resultados esperados após mais agrupamento por/pedido

SRV_NAME             GRP_CNT_INST_NAME    SUM_DB_NAME          GRP_CNT_USER_NAME    GRP_CNT_APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            3                    12                   4                    5

Explicação dos resultados esperados

O SQL Server SQLSRV_01 no exemplo possui três (3) instâncias únicas, doze (12) bancos de dados no total, quatro (4) responsáveis ​​e cinco (5) aplicativos vinculados aos bancos de dados. Este é o resumo dos dados de exemplo acima.

Aplicando isso a todo o banco de dados DBT me forneceria uma visão geral dos sistemas mais complexos.

Material de referência já consultado

• Como obter contagem para diferentes colunas na mesma tabela
• Cláusula OVER (Transact-SQL)
• Usando PIVOT e UNPIVOT
• SOMA em linhas distintas com várias junções próximas
• Soma/contagem/média contínua ao longo do intervalo de datas

A versão longa

Segue os dados e definições de cada tabela envolvida na consulta. E no final, os passos que até agora realizei.

Tabela [DBT].[Servidor]

Dados

 ID | SRV_NAME  | ... 
----+-----------+----
  1 | SQLSRV_01 |     
  2 | SQLSRV_11 |     
  3 | SQLSRV_21 |     

Definição

CREATE TABLE [DBT].[Server](
    [ID] [int] NOT NULL,
    [SRV_NAME] [nchar](20) NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Server] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Server_ALL] ON [DBT].[Server]
(
    [ID] ASC,
    [SRV_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Tabela [DBT].[Instância]

Dados

 ID | INST_NAME   | SRV_ID | ...
----+-------------+--------+----
  1 | GENERAL     |      1 |
  2 | SECURE      |      1 |
  3 | ANOTHER     |      1 |
  4 | GENERAL     |      2 |
  5 | MSSQLSRV    |      3 |
  6 | MSSQLSRV    |      2 |
  7 | PRODUCTION  |      2 |
  8 | TESTING     |      3 |
... |             |        | 

Definição

CREATE TABLE [DBT].[Instance](
    [ID] [int] NOT NULL,
    [INST_NAME] [nchar](20) NOT NULL,
    [SRV_ID] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Instance] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Instance_ALL] ON [DBT].[Instance]
(
    [ID] ASC,
    [INST_NAME] ASC,
    [SRV_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Tabela [DBT].[Aplicativo]

Dados

 ID | APPL_NAME  | ... 
----+------------+-----
  1 | HAL_2000   |     
  2 | RZO_6.4    |     
  3 | PSB IZQ    |     
  4 | TELLTAIL   |     
  5 | PURCHGRAV  |     
... |            |     

Definição

CREATE TABLE [DBT].[Application](
    [ID] [int] NOT NULL,
    [APPL_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Application] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Application_ALL] ON [DBT].[Application]
(
    [ID] ASC,
    [APPL_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Tabela [DBT].[Usuário]

Dados

 ID | USER_NAME  | ... 
----+------------+-----
  1 | ME         |     
  2 | YOU        |     
  3 | HIM        |     
  4 | HER        |     
  5 | THE OTHERS |     
  6 | ALIENS     |     
... |            |     

Definição

CREATE TABLE [DBT].[User](
    [ID] [int] NOT NULL,
    [USER_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[User] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_User_ALL] ON [DBT].[User]
(
    [ID] ASC,
    [USER_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Tabela [DBT].[Banco de dados]

Dados

 ID | DB_NAME    | INST_ID | APPL_ID | USER_ID | ...
----+------------+---------+---------+---------+-----
  1 | MY_DB2     |       1 |       1 |       1 | 
  2 | YOUR_DB2   |       1 |       1 |       2 | 
  3 | RZO_6_4    |       1 |       2 |       1 | 
  4 | DB1        |       2 |       1 |       1 | 
  5 | TELLTAIL   |       2 |       4 |       4 | 
  6 | PURCHGRAV  |       2 |       5 |       4 | 
  7 | HIS_DB     |       3 |       4 |       3 | 
  8 | RZO_P4     |       3 |       3 |       2 | 
  9 | PURCH      |       4 |       5 |       2 | 
 10 | YOUR_DB    |       5 |       4 |       2 | 
 11 | HER_DB     |       6 |       4 |       4 | 
 12 | TEST_PURCH |       6 |       5 |       5 | 
 13 | PROD_PURCH |       7 |       5 |       5 | 
 14 | TELLTAIL   |       7 |       4 |       4 | 
 15 | IZQ_TEST   |       8 |       3 |       3 | 
 16 | IZQ_PROD   |       7 |       2 |       2 | 
 17 | HAL_CA1    |       5 |       1 |       3 | 
 18 | MY_DB3     |       1 |       1 |       1 | 
 19 | MY_DB4     |       1 |       1 |       1 | 
 20 | RZO_6_4_1  |       1 |       2 |       1 | 
 21 | RZO_6_4_2  |       1 |       2 |       2 | 
 22 | HAL_CA1_1  |       5 |       1 |       3 | 
 23 | HAL_CA1_2  |       5 |       1 |       6 | 
... |

Definição

CREATE TABLE [DBT].[Database](
    [ID] [int] NOT NULL,
    [DB_NAME] [nchar](20) NOT NULL,
    [INST_ID] [int] NOT NULL,
    [APPL_ID] [int] NOT NULL,
    [USER_ID] [int] NOT NULL
) ON [PRIMARY]
END
GO
ALTER AUTHORIZATION ON [DBT].[Database] TO  SCHEMA OWNER 
GO
SET ANSI_PADDING ON
GO
CREATE UNIQUE CLUSTERED INDEX [CL_UX_Database_ID_DB_NAME_INST_ID] ON [DBT].[Database]
(
    [ID] ASC,
    [DB_NAME] ASC,
    [INST_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

É tudo por agora.

Selecionando todas as informações

A primeira afirmação foi meu ponto de partida para selecionar as informações básicas.

SELECT s.[SRV_NAME], i.[INST_NAME], d.[DB_NAME], u.[USER_NAME], a.[APPL_NAME]
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]
ORDER BY 1, 2, 3, 4, 5

Isso resulta no retorno dos seguintes registros e é a versão longa dos dados de amostra na introdução:

SRV_NAME             INST_NAME            DB_NAME              USER_NAME            APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            ANOTHER              HIS_DB               HIM                  TELLTAIL            
SQLSRV_01            ANOTHER              RZO_P4               YOU                  PSB IZQ             
SQLSRV_01            GENERAL              MY_DB2               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB3               ME                   HAL_2000            
SQLSRV_01            GENERAL              MY_DB4               ME                   HAL_2000            
SQLSRV_01            GENERAL              RZO_6_4              ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_1            ME                   RZO_6.4             
SQLSRV_01            GENERAL              RZO_6_4_2            YOU                  RZO_6.4             
SQLSRV_01            GENERAL              YOUR_DB2             YOU                  HAL_2000            
SQLSRV_01            SECURE               DB1                  ME                   HAL_2000            
SQLSRV_01            SECURE               PURCHGRAV            HER                  PURCHGRAV           
SQLSRV_01            SECURE               TELLTAIL             HER                  TELLTAIL            
SQLSRV_11            GENERAL              PURCH                YOU                  PURCHGRAV           
SQLSRV_11            MSSQLSRV             HER_DB               HER                  TELLTAIL            
SQLSRV_11            MSSQLSRV             TEST_PURCH           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           IZQ_PROD             YOU                  RZO_6.4             
SQLSRV_11            PRODUCTION           PROD_PURCH           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           TELLTAIL             HER                  TELLTAIL            
SQLSRV_21            MSSQLSRV             HAL_CA1              HIM                  HAL_2000            
SQLSRV_21            MSSQLSRV             HAL_CA1_1            HIM                  HAL_2000            
SQLSRV_21            MSSQLSRV             HAL_CA1_2            ALIENS               HAL_2000            
SQLSRV_21            MSSQLSRV             YOUR_DB              YOU                  TELLTAIL            
SQLSRV_21            TESTING              IZQ_TEST             HIM                  PSB IZQ             

Resumir por contagem (DB_NAME)

Então, pensei que seria uma boa ideia agrupar por SRV_NAME, INST_NAMEe USER_NAMEe APPL_NAMEdepois adicionar COUNT(DB_NAME)à instrução select.

Declaração

SELECT s.[SRV_NAME], i.[INST_NAME], count(d.[DB_NAME]) AS SUMDB, u.[USER_NAME], a.[APPL_NAME]
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]

GROUP BY s.[SRV_NAME], i.[INST_NAME], u.[USER_NAME], a.[APPL_NAME]
ORDER BY 1, 2, 3, 4, 5

Resultados

SRV_NAME             INST_NAME            SUMDB       USER_NAME            APPL_NAME
-------------------- -------------------- ----------- -------------------- --------------------
SQLSRV_01            ANOTHER              1           HIM                  TELLTAIL            
SQLSRV_01            ANOTHER              1           YOU                  PSB IZQ             
SQLSRV_01            GENERAL              1           YOU                  HAL_2000            
SQLSRV_01            GENERAL              1           YOU                  RZO_6.4             
SQLSRV_01            GENERAL              2           ME                   RZO_6.4             
SQLSRV_01            GENERAL              3           ME                   HAL_2000            
SQLSRV_01            SECURE               1           HER                  PURCHGRAV           
SQLSRV_01            SECURE               1           HER                  TELLTAIL            
SQLSRV_01            SECURE               1           ME                   HAL_2000            
SQLSRV_11            GENERAL              1           YOU                  PURCHGRAV           
SQLSRV_11            MSSQLSRV             1           HER                  TELLTAIL            
SQLSRV_11            MSSQLSRV             1           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           1           HER                  TELLTAIL            
SQLSRV_11            PRODUCTION           1           THE OTHERS           PURCHGRAV           
SQLSRV_11            PRODUCTION           1           YOU                  RZO_6.4             
SQLSRV_21            MSSQLSRV             1           ALIENS               HAL_2000            
SQLSRV_21            MSSQLSRV             1           YOU                  TELLTAIL            
SQLSRV_21            MSSQLSRV             2           HIM                  HAL_2000            
SQLSRV_21            TESTING              1           HIM                  PSB IZQ        

Como você pode ver pelos resultados, há mais potencial para resumir (grupo?) por exemplo pelo INST_NAME, USER_NAME, APPL_NAMEpara ter uma visão geral dos sistemas mais complexos.

Agrupar sobre INST_NAME, USER_NAME e APPL_NAME

Então, basicamente, eu gostaria de ter um resumo de cada (sub) item exclusivo com base no servidor, conforme explicado na introdução:

SRV_NAME             GRP_CNT_INST_NAME    SUM_DB_NAME          GRP_CNT_USER_NAME    GRP_CNT_APPL_NAME
-------------------- -------------------- -------------------- -------------------- --------------------
SQLSRV_01            3                    12                   4                    5

Hmmm. Olhando os livros online tenho a opção da cláusula OVER (Transact-SQL) e particionamento nas colunas relevantes. Mas então posso estar interpretando mal a descrição.

Declaração

SELECT s.[SRV_NAME], 
COUNT(i.[INST_NAME]) OVER (PARTITION by i.[INST_NAME]) as GRP_CNT_INST_NAME, 
COUNT(d.[DB_NAME]) AS SUMDB, 
COUNT(u.[USER_NAME]) OVER (PARTITION by u.[USER_NAME]) as GRP_CNT_USER_NAME, 
COUNT(a.[APPL_NAME]) OVER (PARTITION by a.[APPL_NAME]) as GRP_CNT_APPL_NAME
FROM   [DBT].[Server]            AS s
       JOIN [DBT].[Instance]     AS i
            ON  s.ID = i.SRV_ID
       JOIN [DBT].[Database]     AS d
            ON  i.[ID] = d.[INST_ID]
       JOIN [DBT].[Application]  AS a
            ON  d.[APPL_ID] = a.[ID]
       JOIN [DBT].[User]         AS u
            ON  u.ID = d.[USER_ID]

GROUP BY s.[SRV_NAME]--, i.[INST_NAME], u.[USER_NAME], a.[APPL_NAME]
ORDER BY 1, 2, 3, 4, 5

Resultados

SRV_NAME             GRP_CNT_INST_NAME SUMDB       GRP_CNT_USER_NAME GRP_CNT_APPL_NAME
-------------------- ----------------- ----------- ----------------- -----------------
SQLSRV_01            2                 1           3                 5
SQLSRV_01            2                 1           6                 2
SQLSRV_01            3                 1           3                 5
SQLSRV_01            3                 1           4                 4
SQLSRV_01            3                 1           4                 5
SQLSRV_01            5                 1           6                 3
SQLSRV_01            5                 1           6                 5
SQLSRV_01            5                 2           3                 3
SQLSRV_01            5                 3           3                 5
...
...

That doesn't look like what I was expecting to achieve. But then again, I might need a totally different approach.

Question

I'm still trying to find the right way to summarise each sub-item so as to have an overview of the most complex systems. What is a possible solution to my problem?

Quais são as strings de conexão válidas para a caixa de login do SSMS?

De vez em quando terei que me conectar a uma instância do SQL Server com alguma configuração específica ou em uma DMZ onde nenhum nome de host está disponível ou terei que me conectar à Conexão de Admin Dedicada (DAC) via SSMS ou SQLCMD . Como raramente me conecto a um SQL Server por meio de uma porta específica ou com o DAC, tenderei a esquecer quais são as opções exatas de sintaxe para obter uma conexão em funcionamento. Então, terei que pesquisar na Internet novamente pela enésima vez para recuperar as strings de conexão exatas e ficarei regularmente frustrado por não conseguir encontrar as informações necessárias.

Pesquisar

Vou ler todas as perguntas e respostas postadas anteriormente em Serverfault, Stackoverflow e DBA.

Falha no servidor

[1] Porta de conexão DAC SQL Server 2005 SP3
[2] Falha de conexão remota do servidor SQL
[3] Conexão remota do SQL Server 2008 R2

Stackoverflow

[4] Como especificar um número de porta na string de conexão do SQL Server?

DBA

[5] Erro de conexão DAC

Microsoft

Além disso, pesquisarei o site da Microsoft em busca de artigos relevantes:

[6] Como configurar o SQL Server para escutar em uma porta específica
[7] Configurar o Firewall do Windows para permitir acesso ao SQL Server
[8] Configuração da Área de Superfície
[9] Como configurar o SQL Server para escutar em diferentes portas em diferentes endereços IP?
[10] Configurar um servidor para escutar em uma porta TCP específica (SQL Server Configuration Manager)
[11] Conexão de diagnóstico para administradores de banco de dados

Brent Ozar

E Kendra Little tem um artigo para alguns aspectos interessantes do DAC.
[12] A conexão de administrador dedicada: por que você deseja, quando precisa e como saber quem está usando

Em uma nota lateral: às vezes, a conexão DAC também é chamada de conexão ADMIN.

Acho que, no final, nunca há um resumo real de cadeias de conexão simples para inserir em uma caixa de login do SSMS, nem o que adicionar a um SQLCMD.

Esta pergunta e as respostas a seguir pretendem resumir o que descobri até agora e colocar as informações em um único artigo da base de conhecimento.

Pergunta real

Dadas as seguintes configurações para um SQL Server Standard Edition:

Servername.....:    SERVERNAME
IP address.....:    123.1.2.3

1st instance (default instance):
CNAME/Alias....:    SERVERNAME-I01
Name...........:    MSSQLSERVER
Port...........:    1433 (TCP)
IP address.....:    123.1.2.3
DAC Port.......:    1434 (TCP)

2nd instance (default ip & port configuration according to [7]):
CNAME/Alias....:    SERVERNAME-I02
Name...........:    Instance2
Port...........:    dynamic (TCP)
IP address.....:    123.1.2.3
DAC port.......:    dynamic (TCP)

3rd instance (dedicated IP and port):
CNAME/Alias....:    SERVERNAME-I03
Name...........:    Instance3
Port...........:    1433 (TCP)
IP address.....:    123.1.2.4
DAC port.......:    dynamic (TCP)

Browser Service:    1434 (UDP)
Browser service:    ON

...quais são as strings de conexão válidas para cada instância que posso inserir na caixa de login do SSMS

1. ...com o nome do host?
2. ...um pseudônimo (CNAME)?
3. ...com um endereço IP?
4. ...para uma "Conexão de Administrador Dedicada" (DAC também conhecido como ADMIN:)?

Suposições

1. O firewall do servidor está desativado ou configurado de acordo com a documentação da Microsoft mencionada em 6 , 7 ou 8 . Isso também é verdade para qualquer firewall de rede dentro da LAN (incluindo a conexão com um servidor DMZ).
2. As instâncias adicionais, além da instância padrão, foram configuradas manualmente para escutar em diferentes endereços IP de acordo com 9 e neste exemplo foram configuradas para escutar em portas fixas 10 .
3. Em alguns casos, o DAC será ativado de acordo com 11 e 12 .

Estou tentando descobrir várias coisas em torno do PostgreSQL e como os backups devem funcionar em conjunto com o WAL e o Commvault Simpana. Simpana está me dizendo que está tudo bem, mas deixa arquivos espalhados no diretório WAL Archive.

Deixe a viagem começar.

Meio Ambiente

PostgreSQL e versão do sistema operacional

O PostgreSQL 9.3 está sendo executado em um servidor Ubuntu 14.04.3 LTS.

Postgres WAL Config

O arquivo postgres.conf é definido da seguinte forma para arquivamento WAL.

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#wal_level = minimal                    # minimal, archive, or hot_standby
wal_level = archive

[...]

# - Archiving -
archive_mode = on
#archive_mode = off             # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /pgsql-backup/archive/postgres1/%f'
                                # command to use to archive a logfile segment
                                # archive_command = ''           
                                # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f    && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0            # force a logfile segment switch after this
                                # number of seconds; 0 disables

Se a test ...peça for deixada no meio archive_command, quebra o backup do Simpana, por isso o omitimos.

A configuração acima deve resultar na cópia dos arquivos WAL do /pg_xlog/diretório para o /pgsql-backup/archive/postgres1/diretório, quando ...

1. não é mais necessário, devido a um pg_basebackup
2. um arquivo WAL está cheio (padrão 16 MB) e não está mais em uso

Commvault Simpana

O computador cliente foi configurado para que os bancos de dados/instância PostgreSQL e os arquivos WAL no diretório Archive Log sejam copiados. Os arquivos WAL devem ser excluídos quando não forem mais necessários, porque a opção 'Excluir arquivo' do Simpana foi definida para o cliente PostgreSQL.

Comportamento esperado

Como o Simpana está executando o backup com comandos nativos do PostgreSQL, espero que, quando o Simpana concluir um backup completo ou um backup do WAL, os arquivos no /pgsql-backup/archive/postgres1/diretório sejam excluídos.

Comportamento eficaz

Quando eu verificar o /pgsql-backup/archive/postgres1/diretório após o Simpana ter feito um backup, haverá mais um arquivo no diretório com uma 0000000300000037000000nn.mmmmmmmm.backupsintaxe. Isso é uma dica de que o Simpana está fazendo um backup usando comandos nativos do PostgreSQL, pois um 0000000300000037000000nn.mmmmmmmm.backuparquivo só seria criado após um backup PostgreSQL do banco de dados/instância usando pg_basebackup. Esta é apenas a minha conclusão depois de ler a documentação do PostgreSQL 9.3.

Aqui está um exemplo do conteúdo do diretório:

[...]
00000003000000370000007A
00000003000000370000007B.00000028.backup
000000030000003700000091.00000028.backup
000000030000003700000093.00000028.backup
000000030000003700000095.00000028.backup
000000030000003700000097.00000028.backup
000000030000003700000099.00000028.backup
00000003000000370000009B.00000028.backup

Documentação do PostgreSQL

A documentação oficial afirma que

Para fazer uso do backup, você precisará manter todos os arquivos de segmento WAL gerados durante e após o backup do sistema de arquivos. Para ajudá-lo a fazer isso, o processo básico de backup cria um arquivo de histórico de backup que é imediatamente armazenado na área de arquivo do WAL. Esse arquivo recebe o nome do primeiro arquivo de segmento WAL necessário para o backup do sistema de arquivos. Por exemplo, se o arquivo WAL inicial for 0000000100001234000055CD, o arquivo de histórico de backup será nomeado como 0000000100001234000055CD.007C9330.backup. (A segunda parte do nome do arquivo representa uma posição exata dentro do arquivo WAL e normalmente pode ser ignorada.) Depois de arquivar com segurança o backup do sistema de arquivos e os arquivos de segmento WAL usados ​​durante o backup (conforme especificado no histórico de backup Arquivo), todos os segmentos WAL arquivados com nomes numericamente inferiores não são mais necessários para recuperar o backup do sistema de arquivos e podem ser excluídos. No entanto, você deve considerar manter vários conjuntos de backup para ter certeza absoluta de que pode recuperar seus dados.

Isso prejudica minha conclusão de que o Simpana está usando os comandos nativos do PostgreSQL para fazer backup do banco de dados/instância e de seus arquivos de log do WAL Archive no diretório /pgsql-backup/archive/postgres1/.

De acordo com a documentação, um arquivo nnnnnnnnnnnnnnnnnnnnnn.mmmmmmmm.backup é um ponteiro para o arquivo WAL mais antigo necessário para que uma recuperação rollforward seja bem-sucedida. Quaisquer arquivos WAL mais antigos no diretório Archive Log podem ser excluídos e não são mais necessários.

O que me surpreende é que há um arquivo WAL no diretório Archive Log sem um arquivo apontador *.mmmmmmmm.backup correspondente.

Perguntas

1. Se eu não estivesse usando o Simpana para o backup, quem (teria que) deletar os arquivos *.mmmmmmmm.backup no diretório WAL Archive? O pg_archivecleanupcomando?
2. Por que ainda há um arquivo WAL completo no diretório Archive Log, quando ele deveria ter sido excluído como todos os outros arquivos WAL no diretório Archive Log?
3. Por que não há um 00000003000000370000007A.mmmmmmmm.backuparquivo para o arquivo WAL ainda existente 00000003000000370000007Ano diretório de log do arquivo?

Aguardo suas respostas e espero que alguém tenha uma configuração semelhante de Simpana e PostgreSQL em algum lugar por aí.