none
Problema de Desempenho em Base SSQL 2008 RRS feed

  • Pergunta

  • Companheiros, estou com pequeno problema de desempenho em um dos servidores de banco e tenho buscado coleta algumas informações da instância para identificar onde está o problema.]

    Inicialmente utilizei a consulta abaixo de Paul Randal, no qual lista as estatísticas de esperas cumulativas no SQL Server:

    WITH [Waits] AS
        (SELECT
            [wait_type],
            [wait_time_ms] / 1000.0 AS [WaitS],
            ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
            [signal_wait_time_ms] / 1000.0 AS [SignalS],
            [waiting_tasks_count] AS [WaitCount],
            100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
            ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
        FROM sys.dm_os_wait_stats
        WHERE [wait_type] NOT IN (
            N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
            N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
            N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
            N'CHKPT',                       N'CLR_AUTO_EVENT',
            N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
            N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
            N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
            N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
            N'EXECSYNC',                    N'FSAGENT',
            N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
            N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
            N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
            N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
            N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
            N'PWAIT_ALL_COMPONENTS_INITIALIZED',
            N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
            N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
            N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
            N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
            N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
            N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
            N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
            N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
            N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
            N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
            N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
            N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
            N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
            N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
        )
    SELECT
        [W1].[wait_type] AS [WaitType],
        CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
        CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
        CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
        [W1].[WaitCount] AS [WaitCount],
        CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
        CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
        CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
        CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
    FROM [Waits] AS [W1]
    INNER JOIN [Waits] AS [W2]
        ON [W2].[RowNum] <= [W1].[RowNum]
    GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
        [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
    HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
    GO

    O resultado seguido na imagem mostra os três principais tipos de espera com percentuais consideráveis.

    Gostaria que os colegas pudessem me ajudar e resolver esse problema. Ressalto que tenho feito um trabalho de análise nos índices das bases que tem neste servidor, analisando indices ruins e definindo outros pra melhorar consultas problemáticas. Outro detalhe é sobre a configuração do hardware, onde atende a 128 GB de memória, 12 discos 300 GB RAID 10 e 2 CPUs 12 Cores cada. Entendo que talvez seja necessário configurar o grau de paralelismo. Qual seria o valor ideal pra baixar o wait type CXPACKET. 

    Agradeço desde já!


    sexta-feira, 9 de maio de 2014 18:05

Respostas

  • Jeferson,

    Os seus resultados mostram dois problemas: I/O de rede e paralelismo, que podem ser relacionados com vários outros problemas em suas configurações no SQL Server, e/ou em seu Windows Server ou até mesmo de hardware.

    Como não existe uma fórmula mágica, eu recomendo à leitura do KB abaixo para que configuração do paralelismo:

    http://support.microsoft.com/kb/2806535/pt-br

    E para a verificação e possíveis ajustes na no I/O de rede, veja este post no Fórum Americano:

    http://social.msdn.microsoft.com/forums/sqlserver/en-US/c333db35-05af-4199-8fee-c0754e8f52f0/asyncnetworkio-problem

    Além destas análises e ajuste que você deve realizar em seu ambiente, você também pode:

    - Verificar consultas com lentidão e analisar a necessidade de criar novos índices;

    - Verificar grandes consultas e analisar junto de sua equipe de desenvolvimento à possibilidade de incluir novos filtros na sua cláusula WHERE;

    - Criar e rever seu procedimento de manutenção de índices;

    - Verificar à latência de sua rede neste servidor;

    - Verificar se existe outro(s) processos executando e concorrendo com seu servidor SQL;


    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    sexta-feira, 9 de maio de 2014 18:51

Todas as Respostas

  • Jeferson,

    Os seus resultados mostram dois problemas: I/O de rede e paralelismo, que podem ser relacionados com vários outros problemas em suas configurações no SQL Server, e/ou em seu Windows Server ou até mesmo de hardware.

    Como não existe uma fórmula mágica, eu recomendo à leitura do KB abaixo para que configuração do paralelismo:

    http://support.microsoft.com/kb/2806535/pt-br

    E para a verificação e possíveis ajustes na no I/O de rede, veja este post no Fórum Americano:

    http://social.msdn.microsoft.com/forums/sqlserver/en-US/c333db35-05af-4199-8fee-c0754e8f52f0/asyncnetworkio-problem

    Além destas análises e ajuste que você deve realizar em seu ambiente, você também pode:

    - Verificar consultas com lentidão e analisar a necessidade de criar novos índices;

    - Verificar grandes consultas e analisar junto de sua equipe de desenvolvimento à possibilidade de incluir novos filtros na sua cláusula WHERE;

    - Criar e rever seu procedimento de manutenção de índices;

    - Verificar à latência de sua rede neste servidor;

    - Verificar se existe outro(s) processos executando e concorrendo com seu servidor SQL;


    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    sexta-feira, 9 de maio de 2014 18:51
  • Grande Durval, valeu pelas indicações. Estou indo por parte na solução do problema, começando com a espera CXPACKET e posteriormente com ASYNC_NETWORK_IO. O plano de manutenção de índices que adotei é de rebuild uma vez por semana. Identifiquei também muitas consultas problemáticas sem índices, mal projetadas e índices a excluir. 

    Analisando as queries custosas que rodam nos bancos de dados encontrei algumas delas com a opção OPTION ( loop JOIN, maxdop 1 ), no qual uma consulta em especifico pra retornar 375 linhas leva 40s, consumindo um tempo médio de CPU 31881 milissegundos. Então alterei o OPTION ( loop JOIN, maxdop 0 )  e executei a consulta novamente, onde a mesma caiu pra 8s.

    Sabendo que existem nas bases consultas usando paralelismo, sendo que o servidor tem dois processadores com 24 cores somados, a configuração ideal pra o grau de paralelismo seria 12 aproveitando o melhor dos meus CPUs e assim melhorando o desempenho das consultas que utilizam paralelismo?



    quarta-feira, 14 de maio de 2014 00:16
  • Pessoal, conseguir resolver o problema do waits CXPACKET configurando o parâmetro de paralelismo e reiniciando o serviço do SQL Server, porém ainda tenho problemas com o wait ASYNC_NETWORK_IO.

    Analisei também as maiores tabelas do banco e constatei que algumas tinham mais de 50 índices criados, sendo que o recomendado é pelo menos 5-15 por tabela. Foi necessário analisar tabela a tabela  e remover os diversos índices que não estavam sendo usados, ocupando apenas espaço em disco e dificultando uma melhor performance da aplicação.

    Gostaria de mais um help pra determinar uma solução definitiva pra perda de desempenho no servidor.

    Agradeço desde já!

    quinta-feira, 22 de maio de 2014 03:08
  • Jerferson,

    Em relação ao ASYNC_NETWORK_IO, este é um grande problema que normalmente não é causado pelo SQL Server, mas sim por componentes ou recursos que fazem trocas de dados com o Servidor SQL Server.

    Um passo inicialmente é verificar de forma externa quais processos ou aplicativos estão fazendo chamadas para o SQL Server e procurar identificar em qual destas processos existem querys ou transações que estão forçando este I/O.

    Você poderia através do Activity Monitor monitorar o seu SQL Server de forma básica, mas em conjunto com a ferramenta Perfmon do Windows adicionar alguns contadores relacionados com a sua instância SQL Server!!!

    Existe alguma transação que esteja com status de Suspended?

    select * from sys.dm_exec_requests where status = 'suspended'

    Veja se este link ajuda: http://social.technet.microsoft.com/Forums/sqlserver/pt-BR/73874e14-8098-48cf-85fc-9b799e2aaab1/high-asyncnetworkio-sql-server


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]


    sexta-feira, 23 de maio de 2014 13:54
    Moderador
  • Existe sim Galvão:



    segunda-feira, 26 de maio de 2014 12:23