Início / dba / Perguntas / 72256
Accepted
Vojtěch Dohnal
Asked: 2014-07-25 03:59:23 +0800 CST

SQL 2008 Server - perda de desempenho possivelmente relacionada a uma tabela muito grande

  • 772

Temos o SQL Server 2008 com 3 bancos de dados ativos em execução.

  • DB1 - cca 400 MB de tamanho
  • DB2 - tamanho cca de 8 GB
  • DB3 - tamanho de cca 42 GB - mas a maioria dos registros não é usada

No DB2 temos esta tabela

CREATE TABLE [dbo].[PenData](
    [IndicatorID] [smallint] NOT NULL,
    [Time] [datetime2](0) NOT NULL,
    [Value] [real] NULL,
    [ValueMax] [real] NULL,
    [ValueMin] [real] NULL,
 CONSTRAINT [PK_Data] PRIMARY KEY CLUSTERED 
(
    [IndicatorID] ASC,
    [Time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Esta tabela por si só ocupa cerca de 8 GB e possui 283.029.812 registros. A grande maioria dos registros nesta tabela são registros históricos e são acessados ​​muito raramente ou nunca. Mas uma pequena parte dos registros recentes é bastante utilizada e a cada hora muitos novos registros são inseridos nesta tabela.

O problema é que recentemente observamos problemas de desempenho no DB3. Embora o desempenho do DB2 e PenData esteja OK.

Minha pergunta é:

1. o tamanho da tabela PenData pode ser um fator importante para o desempenho geral do servidor? Como esses muitos registros de tabela não utilizados afetam a memória alocada pelo servidor?

2. Posso obter um ganho significativo de performance no servidor (em DB3) se deletar metade dos registros da tabela muito grande PenData?

3. E existem ferramentas para monitorar o desempenho quando não tenho permissões para acessar o Monitor de atividades?

EDITAR

Fiquei bastante apavorado ao ver (usando scripts fornecidos nas respostas) que a tabela PenData ocupava 60-70% de toda a memória do SQL Server (que é relativamente baixa, cca 6 GB). Não sei por que, já que este é o aplicativo que eu mesmo programei e não vejo nenhum motivo, por que tantas linhas desta tabela devem permanecer armazenadas em cache na memória. Também foi um erro meu executar SELECT COUNT(*) FROM PenData antes de tentar ver quanto de PenData permaneceu armazenado em cache na memória.

Omiti uma chave estrangeira que tenho nesta tabela, então a apresento aqui:

ALTER TABLE [dbo].[PenData]  WITH NOCHECK ADD  CONSTRAINT [FK_Data_Indicator] FOREIGN    KEY([IndicatorID])
REFERENCES [dbo].[Indicator] ([IndicatorID])

Eu deletei milhões de registros em lotes de 100.000 registros usando SET RECORDCOUNT 100000 de PenData. Agora ele tem 211 120 425 registros. Executei o DBCC SHRINKDATABASE (PenData, 20) - somente depois disso o consumo de memória do PenData diminuiu significativamente.

O desempenho e o consumo de memória de outros bancos de dados melhoraram.

Mas depois de um dia a tabela PenData ocupa novamente quase toda a memória...

EDITAR

Alterei um único comando SQL em um único procedimento armazenado e agora tudo está perfeito, o banco de dados SupervisionP ocupa apenas 184 MB no cache! Veja detalhes aqui

Busca de índice muito mais lenta com condição OR em comparação com SELECTs separados

Obrigado pela ajuda.

sql-server sql-server-2008

2 respostas

  1. Você pode usar a seguinte consulta para determinar quanta RAM está sendo usada por cada banco de dados:

    USE master;

SELECT d.name, CAST(COUNT(1) AS BIGINT) * 8192 / 1048576 AS MBinMemory
FROM sys.dm_os_buffer_descriptors bd
    INNER JOIN sys.databases d ON bd.database_id = d.database_id
GROUP BY d.name
ORDER BY d.name;

    Para ver a memória usada por objetos em um banco de dados específico, faça:

    USE <DB_NAME_IN_QUESTION>

SELECT 
    obj.name AS ObjectName
    , index_id
    , CAST(count(*) AS BIGINT) * 8192E0 / 1048576 AS CachedMB
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            , index_id
            , allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id 
ORDER BY count(*) DESC;

    A última coluna na consulta acima mostra a quantidade de RAM sendo usada pelo objeto de banco de dados fornecido.

    Você pode ver as estatísticas de consulta observando: (entre muitas outras coisas)

    SELECT qs.execution_count
    , qs.total_worker_time
    , qs.total_elapsed_time
    , qs.total_logical_reads
    , qs.total_physical_reads
    , qs.total_logical_writes
    , qt.text /* This is the actual query text */
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.execution_count DESC;
    • 4
  2. Best Answer

    Eu vou levar você a questionar o ponto sábio

    1. Na minha opinião, é altamente improvável que uma tabela grande não utilizada cause problemas com a execução de consultas para bancos de dados diferentes. A memória do SQL Server é dinâmica por natureza se supor que grande parte da memória está ocupada por páginas de dados do gravador DB1 Lazy e as páginas de ponto de verificação funcionarão juntas para envelhecer as páginas que não foram usadas recentemente ou que têm registros confirmados. Portanto, se as páginas de dados do DB2 exigirem memória, elas serão concedidas e não acho que a crise de memória estaria lá

    2. Não, acho que não, como eu disse, uma consulta para um banco de dados específico (DB2) não afetará os registros presentes em outro banco de dados (DB1) SE as tabelas do banco de dados DB1 não forem usadas nesta consulta. Você pode definir seu problema de desempenho: lentidão de consulta, lentidão de disco, memória, o quê? Por favor, use este link para analisar consultas lentas

    3. Sim, existem muitas ferramentas de monitoramento disponíveis no mercado. Tenho o Spotlight em meu ambiente, você também pode usar o SCOM.

    Você gostaria de consultar este whitepaer Solucionando problemas de desempenho no SQL Server

    • 3

relate perguntas