1

I am using the following query:

SELECT  DB_NAME(vfs.database_id) AS database_name ,physical_name AS [Physical Name],
        size_on_disk_bytes / 1024 / 1024. AS [Size of Disk] ,
        CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [Average Read latency] ,
        CAST(io_stall_write_ms/(1.0 + num_of_writes) AS NUMERIC(10,1)) AS [Average Write latency] ,
        CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads + num_of_writes) 
AS NUMERIC(10,1)) AS [Average Total Latency],
        num_of_bytes_read / NULLIF(num_of_reads, 0) AS    [Average Bytes Per Read],
        num_of_bytes_written / NULLIF(num_of_writes, 0) AS   [Average Bytes Per Write]
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 [Average Total Latency] DESC

from this article to measure latency about the database files. The results I get are:

database_name   Physical Name   Average Total Latency
-----------------------------------------------------
tempdb          tempdb.mdf      608.7
tempdb          templog.ldf     232.7
Prod_DB           ...ldf        75.1
master          mastlog.ldf     44.4
Prod_DB         ...mdf          3.2

Apparently disk performance against latency is very bad especially for temp db.

The SQL Server has just one database. The database only receives a couple of INSERT queries on a single table every few seconds. There is also one SELECT query executed every few seconds.

I'm also getting high disk usage, it is constantly between 10 - 100MB/s for SQL Server process.

How can I go about troubleshooting this issue?

6
  • What type of disks is your database / tempdb on? What does CrystalDiskMark show?...does it match what the disks are rated at?
    – J.D.
    May 11 at 19:48
  • @J.D. A single SSD is used for both the production db and tempdb. It is a small web application running on a LAN, with 3-5 users. The biggest table has abount 2milion rows. May 11 at 20:37
  • 1
    Are you having a performance problem or a metric problem? If you have a performance problem then you should start by describing that - what is slow? Where is the time going? Don’t get trapped going through the metric checklist trap May 11 at 20:37
  • @AndrewSayer There is a performance problem. Sometimes the app runs fine for a whole day through, but then the next day some pages are getting too slow. May 11 at 20:41
  • Consider the time period that your query ran over, is it going to be useful for seeing how things are when things are slow or how things are on average. This is one reason why it’s a bad place to start. What does your instrumentation say about what DB calls are responsible for the time? If you don’t have instrumentation then you will have to start with a few educated guesses May 11 at 20:49

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.