none
sql databse and log disk performance

    Pregunta

  • Hi,

    we have compains about slow MSSQL performance. Our configuration, 2008 (not r2) VM machine on ms hyper-v cluster. We assigned V: (sql db) and T: (sql logs) from hp eva vraid5. how could we be sure that disks are cousing slow performance? what counters we should trace?

    users are connecting to server throuth network with navision (not terminal solution). Could network be the couse of problems?

    thanks,
    n




    • Editado jori5 jueves, 26 de abril de 2012 6:40
    jueves, 26 de abril de 2012 6:10

Respuestas

  • Hi,

    Use this query (safe to run on production):

    SELECT DB_NAME(vfs.database_id) AS database_name ,

    vfs.database_id , vfs.FILE_ID , io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency , io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency , io_stall / NULLIF(num_of_reads + num_of_writes, 0) AS avg_total_latency , num_of_bytes_read / NULLIF(num_of_reads, 0) AS avg_bytes_per_read , num_of_bytes_written / NULLIF(num_of_writes, 0) AS avg_bytes_per_write , vfs.io_stall , vfs.num_of_reads , vfs.num_of_bytes_read , vfs.io_stall_read_ms , vfs.num_of_writes , vfs.num_of_bytes_written , vfs.io_stall_write_ms , size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes , physical_name FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.FILE_ID = mf.FILE_ID ORDER BY avg_total_latency DESC

    Important column: avg_total_latency

    There are exists some recommendations:

    <5ms - excellent

    5-10ms - good

    10-20ms - acceptable

    20-50ms - bad performance.

    >50ms - horrible performance. IO subsystem definitively is bottleneck

    p.s. source of the query:  http://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/

    • Marcado como respuesta Stephanie Lv jueves, 03 de mayo de 2012 9:21
    jueves, 26 de abril de 2012 9:07

Todas las respuestas

  • Hi,

    Use this query (safe to run on production):

    SELECT DB_NAME(vfs.database_id) AS database_name ,

    vfs.database_id , vfs.FILE_ID , io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency , io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency , io_stall / NULLIF(num_of_reads + num_of_writes, 0) AS avg_total_latency , num_of_bytes_read / NULLIF(num_of_reads, 0) AS avg_bytes_per_read , num_of_bytes_written / NULLIF(num_of_writes, 0) AS avg_bytes_per_write , vfs.io_stall , vfs.num_of_reads , vfs.num_of_bytes_read , vfs.io_stall_read_ms , vfs.num_of_writes , vfs.num_of_bytes_written , vfs.io_stall_write_ms , size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes , physical_name FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.FILE_ID = mf.FILE_ID ORDER BY avg_total_latency DESC

    Important column: avg_total_latency

    There are exists some recommendations:

    <5ms - excellent

    5-10ms - good

    10-20ms - acceptable

    20-50ms - bad performance.

    >50ms - horrible performance. IO subsystem definitively is bottleneck

    p.s. source of the query:  http://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/

    • Marcado como respuesta Stephanie Lv jueves, 03 de mayo de 2012 9:21
    jueves, 26 de abril de 2012 9:07
  • Hi,

    Check this video:http://www.brentozar.com/sql/sql-server-performance-tuning/


    Thanks&Regards Siva G

    jueves, 26 de abril de 2012 9:16