Início / dba / Perguntas / 164254
Accepted
Magier
Asked: 2017-02-15 02:20:54 +0800 CST

Planos redundantes em cache devido ao tamanho do tipo ausente

  • 772

Hoje percebi que o cache do plano do nosso servidor sql está cheio de centenas e milhares de planos compilados quase idênticos.

O número total de planos é de cerca de 30.000 em um sistema ativo consumindo cerca de 4500 MB. Olhando para eles, há milhares que são quase iguais.

Algumas amostras:

(@ID uniqueidentifier,@GSS nvarchar(663))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(664))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(665))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(666))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(669))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID

(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(10))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(11))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(12))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(13))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID

O aplicativo usa sp_executesql em quase todos os lugares e passa valores como parâmetros, por isso tive certeza de que os planos estão sendo reutilizados.

Mas agora parece que o aplicativo não se importa com o comprimento do tipo de string e o comprimento é adicionado automaticamente dependendo do tamanho real dos valores, causando um plano diferente para cada combinação de comprimento de string para cada valor de string passado. Então, principalmente, parece que as instruções UPDATE e INSERT são o problema aqui. Acho que alguns desenvolvedores economizaram algum tempo neste momento simplesmente deixando o tamanho de lado.

As contagens de uso desses tipos de planos redundantes UPDATE/INSERT são relativamente baixas (geralmente 1, alguns até 10, apenas alguns até 40 usos).

Seu tamanho varia entre 0,05 MB e 2 MB. É difícil dizer valores e quantias exatos para possíveis economias porque as instruções não podem ser classificadas corretamente devido às constantes de comprimento variáveis ​​entre elas. No entanto, acho que um tamanho médio de plano aqui é de 0,17 MB em cerca de 15.000 planos de atualização/inserção, onde acho que um mínimo de 80% poderia ser salvo, aproximadamente isso pode ser uma economia potencial de 2.040 MB dos 2.550 MB realmente em uso para esses 15.000 planos. Isso reduziria o uso total do cache do plano de 4500 MB para sth. cerca de 2500 MB.

Parece que poderíamos facilmente salvar alguma memória aqui, certo?

Estamos falando de servidores sql com pouca memória (32 GB) que poderiam realmente se beneficiar de alguma memória livre adicional, no entanto, acho que isso também é uma questão de saúde geral do sistema.

Então, minha pergunta é, o que posso fazer sobre isso? Eu tenho QUALQUER opção no lado do banco de dados para obter controle sobre isso? Existem opções de otimização de consulta adhoc que eu ainda não conheço, digamos, ignore isso?

Ou é realmente necessário que o desenvolvedor adicione corretamente o tipo e o comprimento do tipo aos parâmetros de entrada?

No caso de tipos (n)varchar, o comprimento inicial já está definido na definição da tabela, portanto, existem desvantagens ao usar exatamente esses?

Informação adicional:

Enquanto isso, encontrei o post do K.Tripps sobre o cache do plano ficando selvagem ... e sua consulta me mostra que tenho planos de 1500 MB e 20300 com contagem de uso de 1 no cache ...

"Otimizar para cargas de trabalho ad hoc" é uma bala de prata no meu caso específico?

Mais algumas informações: descobri que as consultas são criadas por SqlDataAdapter e SqlCommandBuilder. O Tamanho do Parâmetro para todos os parâmetros criados não são derivados de qualquer forma da tabela de origem pelo construtor de comandos. Se eles não forem definidos, o tamanho dos valores reais passados ​​em nós será usado como valor de tamanho. Isso pode ser encontrado aqui: " Se não for definido explicitamente, o tamanho é inferido do tamanho real do valor do parâmetro especificado. "

sql-server sql-server-2008-r2

2 respostas

  1. Dê uma olhada no cache do plano, cargas de trabalho adhoc e limpeza do inchaço do cache do plano de uso único .

    Para resumir:

    • Se você estiver executando o SQL Server 2008 e tiver cache sendo desperdiçado por planos de uso único, certifique-se de usar o novo "otimizar para cargas de trabalho adhoc".
    • Se você ainda achar que está desperdiçando 100s de MB ou GB de cache, considere criar um trabalho que verifique o cache programaticamente e limpe os 'Planos SQL' do cache com base em uma das opções/código abaixo.

    OPÇÃO 1 - Limpando APENAS os 'Planos SQL' com base apenas na quantidade de
    planos de uso único Adhoc/Preparados (2005/2008):

    DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE 
                    WHEN usecounts = 1
                        AND objtype IN (
                            'Adhoc'
                            ,'Prepared'
                            )
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE 
            WHEN usecounts = 1
                AND objtype IN (
                    'Adhoc'
                    ,'Prepared'
                    )
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 10
BEGIN
    DBCC FREESYSTEMCACHE ('SQL Plans')

    RAISERROR (
            '%s MB was allocated to single-use plan cache. Single-use plans have been cleared.'
            ,10
            ,1
            ,@StrMB
            )
END
ELSE
BEGIN
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            ) —

    Note: this IS ONLY a warning message
        AND NOT an actual error.
END
GO

    OPÇÃO 2 -

    Limpando TODO o seu cache com base na quantidade total desperdiçada por planos de uso único (2005/2008):

    DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE 
                    WHEN usecounts = 1
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE 
            WHEN usecounts = 1
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 1000
    DBCC FREEPROCCACHE
ELSE
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            )
GO
    • 1
  2. Best Answer

    Terminamos alterando o código do aplicativo. Não foi possível evitar o comprimento de caracteres dinâmicos nas instruções Sql resultantes para salvar a criação e armazenamento para planos de execução adicionais por configurações do sql server. Então, o que fizemos foi ler o esquema da tabela de origem do conjunto de dados de onde o construtor de comandos criou a instrução.

        SqlCommandBuilder cb = new sqlCommandBuilder(mydataAdapter);
    var schemaDT = new DataTable();
    mydataAdapter.FillSchema(schemaDT, SchemaType.Source);

    Dessa forma, é possível obter a propriedade SqlParameter.Size correta para cada parâmetro, combinando a propriedade SqlParameter.SourceColumn com schemaDT[SqlParameter.SourceColumn].

    • 0

relate perguntas