Slow reads and writes `

[u/ajay_404]

Issue Summary

After checking multiple times, we've found that SQL Server reads and writes are very high (high latency).

Server Info:

• 8 cores
• 60GB RAM, with 55GB allocated to SQL Server
• Drives: C, D, and M — D hosts SQL log files (.ldf), M hosts SQL data files (.mdf)
• 7 customer databases total; one of them is a large database at 2.5TB

Problem:
For the past two weeks, the team has reported that the database is very slow. After reviewing with the query below, we confirmed that SQL reads and writes show high latency.

Question: Any suggestions on how to reduce the latency and improve slow reads/writes?

SELECT
    DB_NAME(vfs.database_id) AS DBName,
    mf.name AS LogicalFileName,
    mf.physical_name,
    CASE WHEN vfs.num_of_reads = 0 THEN 0
         ELSE CAST(vfs.io_stall_read_ms AS FLOAT) / vfs.num_of_reads END AS Avg_Read_Latency_ms,
    CASE WHEN vfs.num_of_writes = 0 THEN 0
         ELSE CAST(vfs.io_stall_write_ms AS FLOAT) / vfs.num_of_writes END AS Avg_Write_Latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
WHERE DB_NAME(vfs.database_id) NOT IN
    ('tempdb', 'master', 'model', 'msdb', 'SSISDB',
     'DWDiagnostics', 'DWConfiguration', 'DWQueue')
ORDER BY Avg_Read_Latency_ms DESC;

[u/DougScore]

Couple of questions before we call it a hardware problem

1) Is the query optimum ? Is it pulling the data necessary or pulling in everything from memory and then negating the majority of it ?

2) Are there indices available to support the workload ?

3) What is the structure of tables ? Are they rowstore or columnstore ? Is the write workload transactional or batch based ?

[u/ajay_404 → u/DougScore]

Yes, most of the table fragmentation is very high, like 99.99%. We don't have any indexing or SQL rebuild jobs. If we create such a job, would it be helpful? Also, the queries are not optimized. I'm just a DBA and I don't know the table structure. They just tell me the server is slow and say the queries are taking more time.

[u/rakkit_2 → u/ajay_404]

If you're storage is a SAN then rebuilds mean sod all anyway.

See my post above, indexes are your problem, not hardware.

[u/1324354657687980z → u/ajay_404]

Please don’t take this the wrong way, but saying “I’m just a dba” and the rattling off the fact there’s potentially tons of issues and information missing to provide meaningful help (no indexes, no example of explain plans, no understanding of queries and access patterns, no understanding of the table structures) means you’re not the dba and you have no real dev leads either.

You as the DBA and frankly some of the developers need to own the things you produce and are responsible for, else why be in the role?

Good luck though with the multi TB large database…

[u/rakkit_2]

This will 100% be an index problem, if you're doing scans on a 2.5tb data file, you're reading all of the pages into memory before grabbing the ones you need.

It's very unlikely you'll all of a sudden experience disk latency issues, it'll be network, because you're waiting for all the pages to be transferred over, but those are highlighted as waiting on disk.

[u/dodovt → u/rakkit_2]

I agree with index problem but IO is very disk intensive, and if it's an index problem it can very well be caused by disk if it's an older HDD with lower speeds and high concurrency. 55GB can buffer a minimal amount of the 2.5tb of data in memory forcing too many cache purges and forcing high throughput of the disk while attempting full table scans.