sql databse and log disk performance

Question

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

Answer 1

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/

Answer 2

Hi,

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

---

Thanks&Regards Siva G