Usuário com melhor resposta
Problemas de Lock / Performance

Pergunta
-
Boa tarde pessoal;
Estou com um problema em um server e notei que toda vez que vou coletar alguns dados sobre performance/locks tem um wait_type lá
SOS_SCHEDULER_YIELD
Fiz algumas pesquisas e descobri que isto pode indicar uma pressão de processador, achei até um script que retorna quantos sockets seriam necessários para rodar ok.
Este script no geral me rertora algo assim: 0,65, ou seja eu preciso de quase um core a mais para rodar legal, não acho que isto seja um problema tão sério aponto de ficar gerando locks na minha base.
O fato é que este processo é justamente um Assembly em .net que utiliza algumas funções do FullText.
Os locks não ficam por tempo indefinido, contudo, normalmente permanecem por 2~4 minutos.
Eu já havia detectado uma pressão de memória neste servidor e gostaria de saber se por exemplo, uma pressão de memória pode gerar uma pressão de CPU mesmo o uso do CPU no Taskmanager do Windows ficar todo o tempo abaixo de 75%.
Que tipo de estudo eu poderia fazer em cima deste Assembly em .net para saber se efetivamente ele é o causador do problema?
Abraço.
Respostas
-
ricardo,
como esta a configuracao de CPU e memoria do seu server?
Quando eu falo memoria, quero dizer a area da memoria memory-to-leave.
executa esse comando e nos retorna o resultado:
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 ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER', 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES', 'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK') ) SELECT W1.wait_type AS WaitType, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 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
fonte: http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
um bom material sobre wait types eh um white paper Performance_Tuning_Waits_Queues.
http://www.diaadiasql.com.br- Sugerido como Resposta Junior Galvão - MVPMVP, Moderator domingo, 22 de maio de 2011 23:09
- Marcado como Resposta Ricardo Muramatsu terça-feira, 24 de maio de 2011 19:57
Todas as Respostas
-
Ricardo,
Não sei a query que voce utilizou para retornar este 0,65.
Sim, um problema de memoria pode sim gerar um problema no seu processador, porem creio que o mesmo iria crescer, todo caso, seu ambiente se mantem, quando em condições normais, proximo a 75%?...Isso pode ser um problema e fugindo um pouco do assunto, eu recomendaria um upgrade rs....
Para verificar lock, utilize:
SELECT * FROM sys.sysprocesses where blocked <> 0
Em relação aos estudos, eu utilizaria o Perfmon, para abri-lo, va em executar -> PerfMon, colocaria varios contadores, mas creio que os principais que voce deva colocar:
Para processamento:
Process (sqlservr) -> Processor time
Process (msmdsrv) -> Processor time
Processor -> Processor Queue length
System -> Context Switches/sec
Para Memoria:
Memory -> Available Mbytes
Pages Input/sec
Pages/sec
Paging file -> %usage
%usage peak
A avaliaria, lembrando que de especial tente criar uma baseline para seu ambiente rodando esse contadores enquanto seu ambiente estiver sem problema algum.
Oracle OCA11g, MCC 2011! Dicas e novidades: www.fabrizziocaputo.wordpress.com -
ricardo,
como esta a configuracao de CPU e memoria do seu server?
Quando eu falo memoria, quero dizer a area da memoria memory-to-leave.
executa esse comando e nos retorna o resultado:
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 ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER', 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES', 'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK') ) SELECT W1.wait_type AS WaitType, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 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
fonte: http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
um bom material sobre wait types eh um white paper Performance_Tuning_Waits_Queues.
http://www.diaadiasql.com.br- Sugerido como Resposta Junior Galvão - MVPMVP, Moderator domingo, 22 de maio de 2011 23:09
- Marcado como Resposta Ricardo Muramatsu terça-feira, 24 de maio de 2011 19:57
-
@Frabrizzio;
Estes estudos eu já fiz, e também tenho um baseline baseados nesses contadores e mais alguns.
A query eu também não sei qual é pois é um Assembly .net (CLR), sabe como posso fazer um estudo sobre isto no meu banco?
Edit:
A query é esta:
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); go select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / si.os_quantum * scheduler_count), 2) as Additional_Sockets_Necessary from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type = 'SOS_SCHEDULER_YIELD'
@Renato;
CPU está setado:
-Automatic set affinity mask
-Automatic set I/O affinity mask
-Boost SQL Server priority
-Max worker treads = 0
-Todos os cores marcados para affinity e I/O
A utilização do processador raramente passa dos 75%, as vezes da um pico e cai para 30% por exemplo.
MEMÒRIA está setado:
-Min. 6Gb
-Max. 9216Mb
-AWE desativado
*O resto em memória está como padrao (0 / 1024)
A utilização de memória no server está 100% do tempo em seu uso máximo, já fiz vários estudos e sei que tenho uma pressão de memória, pois é bem comum a ocorrência de Target Memory maior que Total Memory, pouca coisa, mas é frequente.
Vou dar uma lida nos links que você passou enquanto isso segue o resultado da query:
*Desculpa tentei colocar a imagem aqui mas não consegui, o link é do picasa.
-
Ricardo,
pelo seus waits, vejo que vc esta com espera a nivel de CPU (CXPACKET e SOS_SCHEDULER_YIELD), isso nao quer dizer que esta ruim ou errado, mas vc pode ajustar as configuracoes relacionadas a paralelismo.
executa esse select e me retorna o resultado:
/*infomaçoes da tabela SYS.CONFIGURATIONS*/ declare @TABELA_INFO table ( NAME CHAR(7), [max degree of parallelism] INT, [cost threshold for parallelism] INT, [min server memory (MB)] INT, [max server memory (MB)] INT ) /* Pivot TABELA SYS.CONFIGURATIONS */ insert into @TABELA_INFO SELECT 'Valores' AS Configuracoes, [max degree of parallelism], [cost threshold for parallelism], [min server memory (MB)], [max server memory (MB)] FROM (SELECT NAME, convert(int,VALUE) as'value' FROM SYS.CONFIGURATIONS ) AS SourceTable PIVOT ( AVG(VALUE) FOR NAME IN ([max degree of parallelism], [cost threshold for parallelism], [min server memory (MB)], [max server memory (MB)]) ) AS PivotTable; /* SELECT FINAL*/ SELECT @@SERVERNAME as 'ServerName' , Serverproperty('productversion') as 'ProductVersion' , Serverproperty('productlevel') as 'ProductLevel' , Serverproperty('edition') as 'Edition' ,SI.CPU_COUNT ,SI.HYPERTHREAD_RATIO ,SI.PHYSICAL_MEMORY_IN_BYTES/1024/1024/1024 AS 'MEMORIA TOTAL' ,[max degree of parallelism] ,[cost threshold for parallelism] ,[min server memory (MB)] ,[max server memory (MB)] FROM SYS.DM_OS_SYS_INFO SI,@TABELA_INFO
Em relacao a memoria, vejo em seus waits que vc utiliza alguns componente de "fora"do sql server. Esse componentes (CLR,xp, CLR, linked server) utilizam um componente de memoria chamada memory to leave.Vc pode alterar o valor dessa porcao de memoria com parametro -G.
http://www.diaadiasql.com.br -
-
Ricardo,
MEMORIA TOTAL:10
( se sua maquina for 32 bits, é preciso configurar corretamente a memoria, seja para componentes externos como para o uso interno do sql server. )
max degree of parallelism:0
( em ate qual grau de paralelismo sua queries vao ser aplicadas. Zero significa o maximo possivel)
cost threshold for parallelism:5
( queries acima deste custo vão utilizar paralelismo )Pelas configuracoes do seu server, praticamente toda rotina esta sendo usado paralelismo. Paralelismo nem sempre é bom. Muita vezes ,inclusive, pode ser muito ruim.
Eu precisaria entender melhor o seu ambiente para indicar alguma coisa....
Envia um email para: renato arroba diaadiasql ponto com ponto br , explicando um pouco mais sobre seu ambiente que posso te ajudar melhor.[]s!
http://www.diaadiasql.com.br -
-
Pessoal, essa é a minha 1° mensagem aqui no forum, estou comaçando em SQl server, tenho experiência em Oracle,e gostaria da ajuda de vcs.
Estou em um ambiente de desenvolvimento.
Queria tentar resolver os waits.
Configuração
Win Server 2003
Sql server 2008 enterprise
declare @TABELA_INFO table ( NAME CHAR(7), [max degree of parallelism] INT, [cost threshold for parallelism] INT, [min server memory (MB)] INT, [max server memory (MB)] INT ) /* Pivot TABELA SYS.CONFIGURATIONS */ insert into @TABELA_INFO SELECT 'Valores' AS Configuracoes, [max degree of parallelism], [cost threshold for parallelism], [min server memory (MB)], [max server memory (MB)] FROM (SELECT NAME, convert(int,VALUE) as'value' FROM SYS.CONFIGURATIONS ) AS SourceTable PIVOT ( AVG(VALUE) FOR NAME IN ([max degree of parallelism], [cost threshold for parallelism], [min server memory (MB)], [max server memory (MB)]) ) AS PivotTable; /* SELECT FINAL*/ SELECT @@SERVERNAME as 'ServerName' , Serverproperty('productversion') as 'ProductVersion' , Serverproperty('productlevel') as 'ProductLevel' , Serverproperty('edition') as 'Edition' ,SI.CPU_COUNT ,SI.HYPERTHREAD_RATIO ,SI.PHYSICAL_MEMORY_IN_BYTES/1024/1024/1024 AS 'MEMORIA TOTAL' ,[max degree of parallelism] ,[cost threshold for parallelism] ,[min server memory (MB)] ,[max server memory (MB)] FROM SYS.DM_OS_SYS_INFO SI,@TABELA_INFO
CPU_COUNT | HYPERTHREAD_RATIO | MEMORIA TOTAL | max degree of parallelism | cost threshold for parallelism max
4 4 3 0 5
SELECT top 10 * FROM sys.dm_os_wait_stats WHERE wait_time_ms > 0 and wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK' ,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE' ,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT' ,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT' ,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN') ORDER BY wait_time_ms DESC
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
SQLTRACE_INCREMENTAL_FLUSH_SLEEP -------- 1220 4880376 4040 0
ASYNC_NETWORK_IO ----------------------------- 1927 119025 1339 106
CXPACKET --------------------------------------------------6006 61211 207 1135
IO_COMPLETION --------------------------------------- 7195 15207 269 11
SOS_SCHEDULER_YIELD ------------------------------- 308457 14161 26 13622
LATCH_EX -------------------------------------------------11396 7460 14 2595
LCK_M_U ------------------------------------------------- 5 6531 2324 0
LCK_M_S ------------------------------------------------ 6 4763 4707 0
LCK_M_IX ---------------------------------------------- 1 4733 4733 1
SLEEP_BPOOL_FLUSH --------------------------------1722 2535 30 2WITH 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 ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER', 'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES', 'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK') ) SELECT W1.wait_type AS WaitType, CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S, W1.WaitCount AS WaitCount, CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage, CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S, CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S, CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 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; GO
WaitType Wait_S Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S
ASYNC_NETWORK_IO 119,06 118,95 0,11 1929 48,67 0,0617 0,0617 0,0001
CXPACKET 64,48 63,33 1,15 6293 26,36 0,0102 0,0101 0,0002
IO_COMPLETION 17,47 17,45 0,01 8287 7,14 0,0021 0,0021 0
SOS_SCHEDULER_YIELD 14,2 0,54 13,66 311724 5,81 0 0 0
LATCH_EX 7,55 4,9 2,65 11958 3,09 0,0006 0,0004 0,0002
LCK_M_U 6,53 6,53 0 5 2,67 1,3062 1,3062 0
LCK_M_S 4,76 4,76 0 6 1,95 0,7938 0,7938 0
e ai pessoal, podem me ajudar a resolver esses waits e melhorar a performance do meu ambiente.