Como melhorar o desempenho insatisfatório do SQL Server

Question

Temos um ambiente virtualizado usando o VMware vSphere 5.0 U1, que hospeda nosso Microsoft SQL Server e tem um desempenho muito ruim. o servidor host é um HP DL360 G5 com xeon X5450, e o espaço em disco vem de uma SAN no HP P4300 G2 usando SAS2 e Iscsi.

O próprio servidor tem 3 núcleos a 3.00ghz, 12gb de memória e 1gbps nic, executando o Server 2008 R2 Enterprise e o SQL Server 2008 R2.

Existem três bancos de dados, um é de 180 GB usado para o Microsoft Dynamics CRM, um é de 50 GB usado como data warehouse e o outro é de 500 MB usado pela Document Logistics (um sistema de gerenciamento de documentos)

Há também um cubo de serviços de análise e serviços de relatório em execução no mesmo servidor.

O uso da rede e da CPU é baixo, exceto quando os pacotes do SSIS atualizam o data warehouse quando a CPU às vezes atinge 100%.

A memória está sempre maximizada e o sistema informa uma paginação excessiva.

Não tenho certeza se apenas jogar mais memória no problema ajudará - e, em caso afirmativo, quanto devo usar - ou se for mais devido a ser virtualizado e ter um servidor separado com discos locais dedicados seria a melhor opção. / p>

UPDATE --- bancos de dados são definidos para memória limitada e os limites totais são mantidos dentro do total de 12gb todos os arquivos estão em um disco e o SO / Programas em outro O crescimento automático está definido para 1 mb, sem restrições Índices são deixados para hospedar os padrões do programa, mas parecem ok servidor é geralmente lento, assume devido à memória não há planos de manutenção estabelecidos Os logs são todos em torno de 100mb, exceto o de CRM, que é de 900mb

    

Answer 1

Quando você diz que a memória está maximizada, você considerou o fato do SQL fazer isso (basicamente) por padrão? Você já tentou limitar a quantidade de RAM que o SQL pode suportar?

Como seu layout de disco é configurado? Discos diferentes para temp / log / data / backup? Você pode executar este script para descobrir:

DECLARE @database_id int 
DECLARE @database_name sysname 
DECLARE @sql_string nvarchar(2000) 
DECLARE @file_size TABLE 
    ( 
    [database_name] [sysname] NULL, 
    [groupid] [smallint] NULL, 
    [groupname] sysname NULL, 
    [fileid] [smallint] NULL, 
    [file_size] [decimal](12, 2) NULL, 
    [space_used] [decimal](12, 2) NULL, 
    [free_space] [decimal](12, 2) NULL, 
    [name] [sysname] NOT NULL, 
    [filename] [nvarchar](260) NOT NULL 
    )

SELECT TOP 1 @database_id = database_id 
    ,@database_name = name 
FROM sys.databases 
WHERE database_id > 0 
ORDER BY database_id

WHILE @database_name IS NOT NULL 
BEGIN

    SET @sql_string = 'USE ' + QUOTENAME(@database_name) + CHAR(10) 
    SET @sql_string = @sql_string + 'SELECT 
                                        DB_NAME() 
                                        ,sysfilegroups.groupid 
                                        ,sysfilegroups.groupname 
                                        ,fileid 
                                        ,convert(decimal(12,2),round(sysfiles.size/128.000,2)) as file_size 
                                        ,convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as space_used 
                                        ,convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as free_space 
                                        ,sysfiles.name 
                                        ,sysfiles.filename 
                                    FROM sys.sysfiles 
                                    LEFT OUTER JOIN sys.sysfilegroups 
                                        ON sysfiles.groupid = sysfilegroups.groupid'

    INSERT INTO @file_size 
        EXEC sp_executesql @sql_string   

    --Grab next database 
    SET @database_name = NULL 
    SELECT TOP 1 @database_id = database_id 
        ,@database_name = name 
    FROM sys.databases 
    WHERE database_id > @database_id 
    ORDER BY database_id 
END

--File Sizes 
SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, fileid, name, file_size, space_used, free_space, filename 
FROM @file_size

--File Group Sizes 
SELECT database_name, groupid, ISNULL(groupname,'TLOG') groupname, SUM(file_size) as file_size, SUM(space_used) as space_used, SUM(free_space) as free_space 
FROM @file_size 
GROUP BY database_name, groupid, groupname

• Como seus autogrows estão configurados?
• Como estão seus índices?
• Você tem alguma lentidão no servidor (outra além do SQL)?
• Você tem planos de manutenção? Como eles estão configurados?
• Qual o tamanho dos seus registros?
• ...

Answer 2

Concordo com a primeira resposta, mas, em geral, com um Banco de Dados do Microsoft CRM com mais de 100 GB, eu recomendaria ter mais RAM do que 12 GB no SQL Server e preferiria ver pelo menos 32 GB de RAM para um banco de dados Microsoft CRM desse tamanho. Para ser mais específico sobre descobrir a causa do que está contribuindo para seus problemas de desempenho do SQL Server, é necessário identificar as consultas que estão causando a maior carga para o servidor. Essas nem sempre são a consulta única, mas às vezes são um conjunto de consultas parametrizadas que podem estar sendo executadas milhares de vezes e que estão contribuindo para a degradação do desempenho. Você pode identificá-los usando os scripts de coleta do SQL Server Perf Stats para coletar dados durante os horários de pico de carga por aproximadamente 5 minutos, link , (apenas use o StartSQLDiagTrace20082.cmd e não as coleções de rastreio detalhadas). Você pode usar a ferramenta SQLNexus disponível no link para ajudar a analisar os dados e visualizar relatórios que podem ajudar a mostrar a você quais consultas ou consultas estão causando a a maioria carrega no servidor por Duração Total, Total de Leituras, Total de CPU ou Total de Gravações. Eu normalmente me concentro nas principais consultas por Total de Leituras ou Duração Total e recebo exemplos dessas consultas para serem executadas por meio do consultor de ajuste do SQL Server Database para descobrir se há índices ou estatísticas ausentes que podem ser aplicados para melhorar o desempenho.

Com seu SQL Server com 3 núcleos, ele também obterá melhor desempenho com um banco de dados do Microsoft Dynamics CRM se você definir o Grau Máximo de Paralelismo como 1. Veja abaixo os comandos para definir isso.

sp_configure 'Mostrar opções avançadas', 1 Reconfigurar; VAI sp_configure 'grau máximo de paralelismo', 1 Reconfigurar com Override; VAI sp_configure 'Mostrar opções avançadas', 0 Reconfigurar; GO