Usuário com melhor resposta
Problema de Desempenho em Base SSQL 2008

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á!
- Editado Jerfeson S. Barbosa sexta-feira, 9 de maio de 2014 19:07 Melhorando o texto
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:
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 RamosMicrosoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform---------------------------------- Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"- Sugerido como Resposta Edinaldo Junior sexta-feira, 9 de maio de 2014 19:23
- Marcado como Resposta Jerfeson S. Barbosa terça-feira, 13 de maio de 2014 23:59
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:
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 RamosMicrosoft Partner | MTA | MCSA - SQL Server 2012 | MCSE - Data Platform---------------------------------- Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"- Sugerido como Resposta Edinaldo Junior sexta-feira, 9 de maio de 2014 19:23
- Marcado como Resposta Jerfeson S. Barbosa terça-feira, 13 de maio de 2014 23:59
-
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?
- Editado Jerfeson S. Barbosa quarta-feira, 14 de maio de 2014 00:19 texto modificado
-
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á!
-
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]
- Editado Junior Galvão - MVPMVP, Moderator sexta-feira, 23 de maio de 2014 13:57
-