Temos vários bancos de dados nos quais um grande número de tabelas é criado e descartado. Pelo que podemos dizer, o SQL Server não realiza nenhuma manutenção interna nas tabelas base do sistema , o que significa que elas podem se tornar muito fragmentadas com o tempo e aumentar de tamanho. Isso coloca pressão desnecessária no pool de buffers e também afeta negativamente o desempenho de operações, como calcular o tamanho de todas as tabelas em um banco de dados.

Alguém tem sugestões para minimizar a fragmentação nessas tabelas internas principais? Uma solução óbvia seria evitar a criação de tantas tabelas (ou criar todas as tabelas transitórias no tempdb), mas para o propósito desta questão, digamos que o aplicativo não tenha essa flexibilidade.

Editar: pesquisas adicionais mostram esta pergunta sem resposta , que parece intimamente relacionada e indica que alguma forma de manutenção manual ALTER INDEX...REORGANIZEpode ser uma opção.

Pesquisa inicial

Os metadados sobre essas tabelas podem ser visualizados em sys.dm_db_partition_stats:

-- The system base table that contains one row for every column in the system
SELECT row_count,
    (reserved_page_count * 8 * 1024.0) / row_count AS bytes_per_row, 
    reserved_page_count/128. AS space_mb
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('sys.syscolpars')
    AND index_id = 1
-- row_count:       15,600,859
-- bytes_per_row:   278.08
-- space_mb:        4,136

No entanto, sys.dm_db_index_physical_statsnão parece oferecer suporte à visualização da fragmentação dessas tabelas:

-- No fragmentation data is returned by sys.dm_db_index_physical_stats
SELECT *
FROM sys.dm_db_index_physical_stats(
    DB_ID(),
    OBJECT_ID('sys.syscolpars'),
    NULL,
    NULL,
    'DETAILED'
)

Os scripts de Ola Hallengren também contêm um parâmetro para considerar a desfragmentação de is_ms_shipped = 1objetos, mas o procedimento ignora silenciosamente as tabelas básicas do sistema mesmo com esse parâmetro ativado. Ola esclareceu que esse é o comportamento esperado; apenas tabelas de usuário (não tabelas de sistema) que são ms_shipped (por exemplo msdb.dbo.backupset, ) são consideradas.

-- Returns code 0 (successful), but does not do any work for system base tables.
-- Instead of the expected commands to update statistics and reorganize indexes,
-- no commands are generated. The script seems to assume the target tables will
-- appear in sys.tables, but this does not appear to be a valid assumption for
-- system tables like sys.sysrowsets or sys.syscolpars.
DECLARE @result int;
EXEC @result = IndexOptimize @Databases = 'Test',
        @FragmentationLow = 'INDEX_REORGANIZE',
        @FragmentationMedium = 'INDEX_REORGANIZE',
        @FragmentationHigh = 'INDEX_REORGANIZE',
        @PageCountLevel = 0,
        @UpdateStatistics = 'ALL',
        @Indexes = '%Test.sys.sysrowsets.%',
        -- Proc works properly if targeting a non-system table instead
        --@Indexes = '%Test.dbo.Numbers.%',
        @MSShippedObjects = 'Y',
        @Execute = 'N';
PRINT(@result);

Informações adicionais solicitadas

Eu usei uma adaptação da consulta de Aaron abaixo do uso do buffer pool da tabela de sistema de inspeção, e descobri que existem dezenas de GB de tabelas do sistema no buffer pool para apenas um banco de dados, com ~ 80% desse espaço sendo espaço livre em alguns casos .

-- Compute buffer pool usage by system table
SELECT OBJECT_NAME(p.object_id),
    COUNT(b.page_id) pages,
    SUM(b.free_space_in_bytes/8192.0) free_pages
FROM sys.dm_os_buffer_descriptors b
JOIN sys.allocation_units a
    ON a.allocation_unit_id = b.allocation_unit_id
JOIN sys.partitions p
    ON p.partition_id = a.container_id
    AND p.object_id < 1000 -- A loose proxy for system tables
WHERE b.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY pages DESC

CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))

INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st) values ('d')
INSERT INTO TestTab (st) values ('e')

INSERT INTO TestTab (st) SELECT TOP 10000 st from testtab
GO 30

ALTER TABLE TestTab ADD newcol nvarchar(10) DEFAULT 'newcol'
UPDATE TestTab SET newcol = 'newcol'  --6 sec
ALTER TABLE TestTab ADD newcol1 nvarchar(10) DEFAULT 'newcol1' NOT NULL

DROP TABLE TestTab

Quando executo este script de teste, o ALTERcom UPDATEleva 6 segundos, o que é compreensível.

No entanto, o ALTERwith DEFAULT NOT NULLé executado instantaneamente mesmo em uma tabela muito maior. Existe alguma explicação sobre por que isso é instantâneo? No disco físico, os dados ainda precisam ser gravados em todas as linhas, certo?

Eu tentei olhar SET STATISTICS IO ONe o plano de consulta, no entanto, eles não parecem estar disponíveis para operações DDL.

Encontrei um código de desenvolvedor onde o método SqlCommand.Prepare() (consulte MSDN) é amplamente usado antes da execução de consultas SQL. E eu me pergunto qual é o benefício disso?

Amostra:

command.Prepare();
command.ExecuteNonQuery();
//...
command.Parameters[0].Value = 20;
command.ExecuteNonQuery();

Eu brinquei um pouco e tracei. A execução do Comando após a chamada do Prepare()método faz com que o Sql Server execute a seguinte instrução:

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@id int,@desc text',N'INSERT INTO dbo.testtable (id) VALUES (@id)',@id=20'
select @p1

Depois disso, quando o parâmetro obtém seu valor e SqlCommand.ExecuteNonQuery()é chamado, o seguinte é executado no Sql-Server:

exec sp_execute 1,@id=20

Para mim, parece que a instrução é compilada assim que Prepare()é executada. Eu me pergunto qual é o benefício disso? Isso significa que ele é colocado no cache do plano e pode ser reutilizado assim que a consulta final for executada com os valores de parâmetro desejados?

Eu descobri (e documentei em outra pergunta ) que SqlCommands que são executados com SqlParameters sempre são encapsulados em sp_executesqlchamadas de procedimento. Isso torna o Sql Server capaz de armazenar e reutilizar os planos independentemente dos valores dos parâmetros.

Em relação a isso, eu me pergunto se o prepare()método é meio inútil ou obsoleto ou se estou perdendo alguma coisa aqui?

Ao atualizar o armazenamento em uma instância do SQL Server 2014 SP1 (12.0.4422.0), encontramos um problema em que dois dos bancos de dados não iniciavam no secundário após reiniciar o SQL Server. O servidor ficou offline por algumas horas enquanto instalávamos novos SSDs (maiores) e copiávamos os arquivos de dados para o novo volume. Quando reiniciamos o SQL Server, todos os bancos de dados, exceto dois, começaram a sincronizar novamente. Os outros dois foram exibidos no SSMS como Not Synchronizing/Recovery Pending .

Tendo tido um problema semelhante Não Sincronizando / Em Recuperação antes, verifiquei o status na seção Grupos de Disponibilidade -> Bancos de Dados de Disponibilidade, mas eles exibiram um X vermelho:

e mesmo tentar Suspender a Movimentação de Dados gerou uma mensagem de erro:

Falha ao suspender a movimentação de dados no banco de dados 'StackExchange.Bycycles.Meta', que reside na réplica de disponibilidade 'ny-sql03' no grupo de disponibilidade 'SENetwork_AG'. (Microsoft.SqlServer.Smo)

Informações Adicionais: Ocorreu uma exceção ao executar uma instrução ou lote transact-SQL. (Microsoft.SqlServer.ConnectionInfo)

O banco de dados 'StackExchange.Bycycles.Meta' não pode ser aberto devido a arquivos inacessíveis ou memória insuficiente ou espaço em disco. Consulte o log de erros do SQL Server para obter detalhes. (Microsoft SQL Server, Erro: 945)

Eu verifiquei e os arquivos existiam e não tinham problemas de permissão. Também verifiquei os logs do SQL Server no SSMS em Gerenciamento, mas não vi nada sobre recuperação pendente ou problemas com os dois bancos de dados.

Procurando ajuda, encontrei dois artigos diferentes que diziam que os bancos de dados precisariam ser restaurados.

Existe alguma maneira de retomar a replicação de dados em um secundário quando um banco de dados está travado em Recuperação pendente?

Eu tenho uma tabela de usuários:

|Username|UserType|Points|
|John    |A       |250   |
|Mary    |A       |150   |
|Anna    |B       |600   |

e Níveis

|UserType|MinPoints|Level  |
|A       |100      |Bronze |
|A       |200      |Silver |
|A       |300      |Gold   |
|B       |500      |Bronze |

E estou procurando uma consulta para obter o nível de cada usuário. Algo na linha de:

SELECT *
FROM Users U
INNER JOIN (
    SELECT TOP 1 Level, U.UserName
    FROM Levels L
    WHERE L.MinPoints < U.Points
    ORDER BY MinPoints DESC
    ) UL ON U.Username = UL.Username

De modo que os resultados seriam:

|Username|UserType|Points|Level  |
|John    |A       |250   |Silver |
|Mary    |A       |150   |Bronze |
|Anna    |B       |600   |Bronze |

Alguém tem alguma idéia ou sugestão sobre como eu poderia fazer isso sem recorrer a cursores?

Nosso fluxo ETL tem uma instrução SELECT INTO de longa duração, que cria uma tabela em tempo real e a preenche com várias centenas de milhões de registros.

A declaração é algo comoSELECT ... INTO DestTable FROM SrcTable

Para fins de monitoramento, gostaríamos de ter uma ideia aproximada do andamento desta instrução, enquanto ela está sendo executada (aprox. rowcount, número de bytes gravados ou similar).

Tentamos o seguinte sem sucesso:

-- Is blocked by the SELECT INTO statement:
select count(*) from DestTable with (nolock)

-- Returns 0, 0:
select rows, rowmodctr
from sysindexes with (nolock)
where id = object_id('DestTable')

-- Returns 0:
select rows
from sys.partitions
where object_id = object_id('DestTable')

Além disso, podemos ver a transação em sys.dm_tran_active_transactions, mas não consegui encontrar uma maneira de obter a contagem de linhas afetadas em um determinado transaction_id(algo semelhante a @@ROWCOUNTtalvez, mas com o transaction_idargumento as).

Entendo que no SQL Server a instrução SELECT INTO é uma instrução DDL e DML em uma e, como tal, a criação implícita da tabela será uma operação de bloqueio. Ainda acho que deve haver alguma maneira inteligente de obter algum tipo de informação de progresso enquanto a instrução está em execução.

Criei uma tabela em um BD que já existe em outro BD. Ele foi inicialmente preenchido com os dados antigos do banco de dados. O PK da tabela tinha que receber os valores que já existem nesses registros, então não poderia ser autoincremento.

Agora preciso que a nova tabela tenha seu PK como autoincremento. Mas como posso fazer isso depois que o PK já existe e tem dados?

No Postgres 9.5, fiquei surpreso ao ver o resultado visto abaixo enquanto experimentava o ano 0001(sem ano zero 0000).

Deslocamento de -07:52:58?

Algum código de exemplo. Observe que eu misturei o uso de TIMESTAMP WITH TIME ZONEe TIMESTAMP WITHOUT TIME ZONE, então leia com atenção.

SET TIME ZONE 'America/Los_Angeles' ;

SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0', 
        TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z', 
        TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z') ;

("2015-01-01 00:00:00-08","0001-12-31 16:07:02-07:52:58 BC","0001-01-01 00:00:00")

Estou surpreso com esse segundo valor: 0001-12-31 16:07:02-07:52:58 BC. Entendo que devemos retroceder oito horas, pois America/Los_Angelesestá oito horas atrás do UTC com um deslocamento de -08:00. Mas em vez do -08:00deslocamento é -07:52:58. Por quê?

Sem problemas sob UTC

Não existe esse problema ao inserir dados em UTC.

SET TIME ZONE 'UTC' ;

SELECT (TIMESTAMP WITH TIME ZONE '2015-01-01 00:00:00.0',  
        TIMESTAMP WITH TIME ZONE '0001-01-01 00:00:00.0Z', 
        TIMESTAMP WITHOUT TIME ZONE '0001-01-01 00:00:00.0Z');

("2015-01-01 00:00:00+00","0001-01-01 00:00:00+00","0001-01-01 00:00:00")

sem ano zero

A propósito, a parte da data parece estar correta. Parece que não há ano 0000, sendo esse o ponto pivô entre as eras “BC” e “AD”. Pegue o primeiro momento do ano 0001, subtraia uma hora e você obtém o ano 0001 BC– portanto, nada de ano zero.

SET TIME ZONE 'UTC' ;

INSERT INTO moment_  -- TIMESTAMP WITH TIME ZONE.
VALUES ( TIMESTAMP '0001-01-01 00:00:00.0Z' - INTERVAL '1 hour' ) ;

SET TIME ZONE 'UTC' ;

TABLE moment_ ;

O resultado é o ano 0001 BC, então saltamos de 0001para 0001 BC; nenhum ano zero 0000.

"0001-12-31 23:00:00+00 BC"

Para um COUNT(DISTINCT)que tem aproximadamente 1 bilhão de valores distintos, estou obtendo um plano de consulta com uma agregação de hash estimada em apenas aproximadamente 3 milhões de linhas.

Por que isso está acontecendo? O SQL Server 2012 produz uma boa estimativa, então isso é um bug no SQL Server 2014 que devo relatar no Connect?

A consulta e estimativa ruim

-- Actual rows: 1,011,719,166
-- SQL 2012 estimated rows: 1,079,130,000 (106% of actual)
-- SQL 2014 estimated rows: 2,980,240 (0.29% of actual)
SELECT COUNT(DISTINCT factCol5)
FROM BigFactTable
OPTION (RECOMPILE, QUERYTRACEON 9481) -- Include this line to use SQL 2012 CE

-- Stats for the factCol5 column show that there are ~1 billion distinct values
-- This is a good estimate, and it appears to be what the SQL 2012 CE uses
DBCC SHOW_STATISTICS (BigFactTable, _WA_Sys_00000005_24927208)
--All density   Average Length  Columns
--9.266754E-10  8               factCol5
SELECT 1 / 9.266754E-10
-- 1079126520.46229

O plano de consulta

roteiro completo

Aqui está uma reprodução completa da situação usando apenas um banco de dados de estatísticas .

O que eu tentei até agora

Pesquisei nas estatísticas da coluna relevante e descobri que o vetor de densidade mostra aproximadamente 1,1 bilhão de valores distintos. O SQL Server 2012 usa essa estimativa e produz um bom plano. O SQL Server 2014, surpreendentemente, parece ignorar a estimativa muito precisa fornecida pelas estatísticas e, em vez disso, usa uma estimativa muito menor. Isso produz um plano muito mais lento que não reserva memória suficiente e derrama para tempdb.

Eu tentei trace flag 4199, mas isso não corrigiu a situação. Por fim, tentei me aprofundar nas informações do otimizador por meio de uma combinação de sinalizadores de rastreamento (3604, 8606, 8607, 8608, 8612), conforme demonstrado na segunda metade deste artigo . No entanto, não consegui ver nenhuma informação explicando a estimativa incorreta até que ela aparecesse na árvore de saída final.

Problema de conexão

Com base nas respostas a esta pergunta, também registrei isso como um problema no Connect

Eu tenho uma tabela CustPassMastercom 16 colunas, uma das quais é CustNum varchar(8), e criei um índice IX_dbo_CustPassMaster_CustNum. Quando executo minha SELECTdeclaração:

SELECT * FROM dbo.CustPassMaster WHERE CustNum = '12345678'

Ele ignora o índice completamente. Isso me confunde, pois tenho outra tabela CustDataMastercom muito mais colunas (55), uma das quais é CustNum varchar(8). Criei um índice nesta coluna ( IX_dbo_CustDataMaster_CustNum) nesta tabela, e utilizo praticamente a mesma consulta:

SELECT * FROM dbo.CustDataMaster WHERE CustNum = '12345678'

E usa o índice que criei.

Existe algum raciocínio específico por trás disso? Por que ele usaria o índice de CustDataMaster, mas não o de CustPassMaster? É devido à baixa contagem de colunas?

A primeira consulta retorna 66 linhas. Para o segundo, 1 linha é retornada.

Além disso, nota adicional: CustPassMasterpossui 4.991 registros e CustDataMasterpossui 5.376 registros. Este poderia ser o raciocínio por trás de ignorar o índice? CustPassMastertambém possui registros duplicados com os mesmos CustNumvalores. Esse é outro fator?

Estou baseando essa afirmação nos resultados reais do plano de execução de ambas as consultas.

Aqui está o DDL para CustPassMaster(aquele com o índice não utilizado):

CREATE TABLE dbo.CustPassMaster(
    [CustNum] [varchar](8) NOT NULL,
    [Username] [char](15) NOT NULL,
    [Password] [char](15) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustPassMaster_CustNum] ON dbo.CustPassMaster
(
    [CustNum] ASC
) 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]

E o DDL para CustDataMaster(omiti muitos campos irrelevantes):

CREATE TABLE dbo.CustDataMaster(
    [CustNum] [varchar](8) NOT NULL,
    /* more columns here */
    [VBTerminator] [varchar](1) NOT NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_dbo_CustDataMaster_CustNum] ON dbo.CustDataMaster
(
    [CustNum] ASC
)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]

Não tenho um índice clusterizado em nenhuma dessas tabelas, apenas um índice não clusterizado.

Ignore o fato de que os tipos de dados não correspondem inteiramente ao tipo de dados que está sendo armazenado. Esses campos são um backup de um banco de dados IBM AS/400 DB2 e esses são os tipos de dados compatíveis para ele. (Tenho que ser capaz de consultar esse banco de dados de backup com exatamente as mesmas consultas e obter exatamente os mesmos resultados.)

Esses dados são usados ​​apenas para SELECTdeclarações. Eu não faço nenhuma instrução INSERT// nele, exceto quando o aplicativo de backup está copiando dados do AS/400 UPDATE.DELETE