terça-feira, 31 de janeiro de 2012

Pesquisa: Desperdício no data cache

Parte da análise dos servidores SQL Server que nós fazemos está em olhar com carinho para o data cache, afinal de contas, é ele que ajuda o SQL Server a manter um bom desempenho e é quem consome boa parte do BPool.

Escrevi rapidinho uma pequena consulta que mostra quanto do BPool está sendo “desperdiçado” com fragmentação interna das páginas carregadas. Já tenho uma ideia da média de alguns servidores que tenho contato, mas gostaria de publicar um número, resultado de muitos outros servidores, para TODOS os DBAs terem uma ideia dessa média.

WITH DataCache AS (
SELECT 
ISNULL(CAST(database_id AS VARCHAR), 'Total') AS DatabaseID    
, ((COUNT(page_id) * 8192.0) / 1024.0) / 1024.0 AS TotalUtilizadoMB
, (SUM(CAST(free_space_in_bytes AS BIGINT)) / 1024.0) / 1024.0 AS EspacoLivreMB
FROM sys.dm_os_buffer_descriptors
GROUP BY GROUPING SETS ((database_id), ()))
SELECT 
DataCache.*
, EspacoLivreMB / TotalUtilizadoMB AS PercentualDesperdicado    
FROM DataCache
ORDER BY EspacoLivreMB DESC  

Para me ajudar basta executar essa consulta me enviar (ou colocar no comentário deste post) o resultado que aparece na linha do “Total”. Abaixo um exemplo de saída...

DatabaseID     TotalUtiliadoMB      EspacoLivreMB        PercentualDesperdicado
Total                 7404.281250           1212.929668423      0.163814640

Note que dependendo do tamanho do BPool o SQL Server pode ler muitas páginas, mas isso não deve causar nenhum grande impacto no ambiente, dado que a DMV não espera por um latch que está prendendo a página (
http://blogs.msdn.com/b/psssql/archive/2009/01/21/how-it-works-sys-dm-os-buffer-descriptors.aspx), mas nos dará uma boa média.

E aí, gostou da ideia? Então vamos ajudar os outros DBAs e executar a consulta. Depois publico o resultado aqui...



!! UPDATE !!
Enquanto conversava com o Fabiano pelo Lync, buscando algumas coisas sobre hobt_id e partition_id, esbarramos com dois posts do Paul Randall, publicados no ano passado, fazendo o mesmo que pedi para vocês. Coincidência é pouco! 
Mesmo assim o resultado dos servidores de vocês ainda é bem vindo, fico no aguardo de mais dados.

Aqui estão os links para ver o resultado dele: http://www.sqlskills.com/BLOGS/PAUL/post/Survey-how-much-server-memory-is-being-wasted-(code-to-run).aspx e http://www.sqlskills.com/BLOGS/PAUL/post/Performance-issues-from-wasted-buffer-pool-memory.aspx

No post de fechamento vou citar algumas coisas importantes, mas como o Paul já falou muito vou aproveitar para mostrar algo diferente do que ele cita no fim do artigo, onde sys.dm_db_index_physical_stats vai estar bem diferente do coletado no data cache.

Acho que isso foi um sinal, quem sabe no futuro a Sr. Nimbus não estará a altura do SQLSkills??!!! HEHEHE
E eu ACHO que existe a chance deles conseguirem mais dados de servidores do que nós. :-)

[]s
Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br

22 comentários:

  1. Luti, segue o que deu aqui no trabalho!

    DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    Total 6299.8125000000000 943.942421913085 0.1498365898847

    ResponderExcluir
  2. Marina Malvino (Servidor Sql Server 2008)
    DatabaseID
    Total
    TotalUtilizadoMB
    6806.9687500000000
    EspacoLivreMB
    1031.045667647460
    PercentualDesperdicado
    0.1514691348696

    ResponderExcluir
  3. Luti, os resultados de uma das bases mais importantes do cliente que atendo.

    Obs: tive de fazer uma conversão de free_space_in_bytes para bigint pois estava estourando o "int" que a função SUM retorna, desta forma: SUM(cast(free_space_in_bytes as bigint)

    DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    Total 5800.531250000000 2893.475884437500 0.498829

    Muito bom, vai entrar para a lista de scripts!

    ResponderExcluir
  4. Fábio Cordeiro Alexandre31 de janeiro de 2012 11:07

    DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado

    Total 6810.6953125000000 1190.627208708984 0.1748172769561

    ResponderExcluir
  5. Valeu galera, vamos postando...
    Atualizei o script com a conversão para BIGINT do free_space_in_bytes, evitando o problema que o Erickson comentou.

    []s
    Luti

    ResponderExcluir
  6. Servidor SQL Server 2008 SP 2

    DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    Total 17898.804687500000 2465.730891227539 0.137759

    Att,
    Marcos Freccia

    ResponderExcluir
  7. DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    Total 23636.35156250 6813.436568259765 0.288260

    ResponderExcluir
  8. Servidor de produção com desenvolvimento Interno
    TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    56708.1875000000000 4857.196565627929 0.085652

    Ambiente Totvs
    TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    114420.5390625000000 15904.691816329101 0.139002

    ResponderExcluir
  9. * SQL Server 2008 SP3 Enterprise x64 (OLAP)

    TotalUtilizadoMB: 22438.6015625000000
    EspacoLivreMB: 4355.211185455078
    PercentDesperdicado:0.194094

    * SQL Server 2008 SP3 Enteprise x64 (OLTP)

    TotalUtilizadoMB: 2077.5312500000000
    EspacoLivreMB: 256.397269248046
    PercentDesperdicado: 0.123414

    Abs.
    Leandro Ribeiro

    ResponderExcluir
  10. DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    Total 18389.3515625000000 1831.352266311523 0.099587

    ResponderExcluir
  11. DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    Total 4380.4921875000000 1520.307349204101 0.347063

    ResponderExcluir
  12. DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    Total 554.6250000000000 153.213917731445 0.276247

    ResponderExcluir
  13. Total 33947.2109375000000 4877.022085189453 0.143664

    ResponderExcluir
  14. Total 143.0781250000000 31.413375854492 0.219554

    ResponderExcluir
  15. outras instâncias
    Total 2.761.093.750.000.000 51.068.777.083.984 0.184958
    Total 4.552.734.375.000.000 62.490.338.325.195 0.137258
    Total 19.708.125.000.000.000 422.512.677.192.382 0.214385
    Total 635.000.000.000.000 26.099.206.923.828 0.411011
    Total 34.830.156.250.000.000 46.967.557.906.250 0.013484
    Total 1.937.968.750.000.000 29.617.497.443.359 0.152827
    Total 478.906.250.000.000 17.075.581.549.804 0.356553
    Total 457.578.125.000.000 17.600.599.288.085 0.384646
    Total 10.762.500.000.000.000 74.596.100.806.640 0.069311
    Total 2.837.734.375.000.000 98.910.550.117.187 0.348554
    Total 4.250.781.250.000.000 85.096.024.512.695 0.200189
    Total 26.070.156.250.000.000 862.973.207.473.632 0.331019

    ResponderExcluir
  16. Rodrigo Ribeiro Gomes31 de janeiro de 2012 17:27

    Total 22965.5937500000000 5032.450761794921 0.219130
    Total 24669.7812500000000 5048.682723999023 0.204650

    Incrível estes dados. 5 GB disperdiçads... Nossa dá pra colocar muita coisa em 5GB... Muito bom isso luti! Valeu!

    ResponderExcluir
  17. Total 390.6171875000000 19.401840209960 0.049669

    ResponderExcluir
  18. Segue minha contribuição:

    DatabaseID TotalUtilizadoMB EspacoLivreMB PercentualDesperdicado
    ---------- ---------------- -------------------- -----------------------
    Total 308169.3437500 44794.578456877929 0.145357
    Total 44682.8281250 7830.732862471679 0.175251
    Total 65719.8593750 7237.522212981445 0.110126
    Total 256472.3359375 37873.706090926757 0.147671
    Total 65481.3671875 3078.349461554687 0.047011
    Total 912199.3750000 187078.873924254882 0.205085
    Total 120048.0078125 15918.282025336914 0.132599

    ResponderExcluir
  19. Este comentário foi removido pelo autor.

    ResponderExcluir
  20. segue o meu resultado:

    Total 6102.1718750000000 1203.795759200195 0.197273

    []'s

    Kleber.

    ResponderExcluir
  21. Total 29261.1250000000000 6507.887498855468 0.222407

    ResponderExcluir
  22. Total 59237.8828125000000 5022.790848731445 0.084790

    ResponderExcluir