none
Problemas de Lock / Performance RRS feed

  • 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.


    View Ricardo Muramatsu's profile on LinkedIn
    quinta-feira, 19 de maio de 2011 20:14

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
    sexta-feira, 20 de maio de 2011 01:15

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
    quinta-feira, 19 de maio de 2011 20:23
    Moderador
  • 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
    sexta-feira, 20 de maio de 2011 01:15
  • @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.

     


    View Ricardo Muramatsu's profile on LinkedIn


    sexta-feira, 20 de maio de 2011 11:57
  • 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
  • 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,
    View Ricardo Muramatsu's profile on LinkedIn
    terça-feira, 24 de maio de 2011 13:42
  • 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
  • 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
    View Ricardo Muramatsu's profile on LinkedIn
    terça-feira, 24 de maio de 2011 19:56
  • 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