Remove From My Forums

Problemas de Lock / Performance

Pergunta
0

Entrar para Votar

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.

quinta-feira, 19 de maio de 2011 20:14

Responder

|

Citação

Respostas

Entrar para Votar

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

sexta-feira, 20 de maio de 2011 01:15

Todas as Respostas

0

Entrar para Votar

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

quinta-feira, 19 de maio de 2011 20:23

Responder

|

Citação

Moderador

Entrar para Votar

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

sexta-feira, 20 de maio de 2011 01:15

0

Entrar para Votar
@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:

https://lh4.googleusercontent.com/_m0X2AgZkwnM/TdZXE9by1NI/AAAAAAAAEZQ/hdwN2sGrk8U/s400/Capturar2.PNG

*Desculpa tentei colocar a imagem aqui mas não consegui, o link é do picasa.
sexta-feira, 20 de maio de 2011 11:57

Responder

|

Citação

Entrar para Votar

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

sábado, 21 de maio de 2011 16:32

0

Entrar para Votar

Show de bola Renato, Seguem os dados que pediu: CPU_COUNT:8 HYPERTHREAD_RATIO:2 MEMORIA TOTAL:10 max degree of parallelism:0 cost threshold for parallelism:5 Abraço,

terça-feira, 24 de maio de 2011 13:42

Responder

|

Citação
0

Entrar para Votar

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

terça-feira, 24 de maio de 2011 14:58

Responder

|

Citação
0

Entrar para Votar

Oloko, Precisa não cara, é obrigação minha ler sobre estes parâmetros e entender onde está meu problema. Mas se eu tiver dúvidas, esteja certo, irei pedir ajuda. Po, vlw mesmo a ajuda, já tenho um caminho das pedras para entender/aprender. Abraço, Ricardo Muramatsu

terça-feira, 24 de maio de 2011 19:56

Responder

|

Citação
0

Entrar para Votar
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 2

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; 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.
sexta-feira, 11 de novembro de 2011 14:58

Responder

|

Citação

Produtos

Resources

Solutions

Atualizações

Avaliações

Sites relacionados

Treinamento

Certificações

Outros recursos

Por produtos

Outros links

Não é um IT Pro?

Usuário com melhor resposta

Problemas de Lock / Performance

Pergunta

Respostas

Todas as Respostas