Hyper-V 2019 SQL 2016 Std on Windows 2012R2 slowdowns

Question

Hello,
Environment:
Hyper-V 2019 256GB RAM, SQL 2016 fixed memory not dynamic 128GB on Server 2012R2. Only 2 VM's on the host, 10Gb nics (Broadcom), FC storage.CSV's.
After several days, 4-5 SQL slows-down to a crawl, queries and writes. SQL is rebooted every morning. Storage is FC and disks are all on the same LUN (probably not the best however the LUN is not shared with over VM's but all drives on the SQL server - 7 drives on the same LUN on FC storage). 2 x 10Gb Broadcom nics load balanced, VMQ etc everything is default install.
Only fix is reboot Hyper-V server then get the 4-5 days. Moving the SQL to another host does similar, 4-5 days then slow-down again.
When slow down happens disk reads are 100% and writes also for a period of time in SQL server.
Trying to figure out what to look for to figure out what's going on, any ideas of what to check? SQL server uploads files daily and does some ETL work, work is similar each day, so nothing happens on day4-5 that is different from other days.
Any ideas greatly appreciated.

Answer 1

May want to add the 'SQL-server-general' tag to bring your question to the attention of the SQL experts.

Answer 2

Hi @Giorgos ,

…Trying to figure out what to look for to figure out what's going on, any ideas of what to check? …

First, you can check the value of the wait_type field of sys.dm_exec_requests (or sys.dm_os_wait_stats). If there are often connections in the waiting states waiting for disk I/O, generally speaking, the server's I/O is still relatively busy, and this busyness has affected the response speed of the statement.
Code:
Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type

Next, the administrator needs to figure out which file in which database SQL Server is always doing I/O, whether it is a data file or a log file, and whether it is frequently read or written. We can obtain relevant information through dynamic management functions:
Code:
select db.name as database_name, f.fileid as file_id, f.filename as file_name, i.num_of_reads, i.num_of_bytes_read, i.io_stall_read_ms, i.num_of_writes, i.num_of_bytes_written, i.io_stall_write_ms, i.io_stall, i.size_on_disk_bytes from sys.databases db inner join sys.sysaltfiles f on db.database_id = f.dbid inner join sys.dm_io_virtual_file_stats(NULL, NULL) i on i.database_id = f.dbid and i.file_id = f.fileid

The preceding dynamic management functions store the historical statistical information of SQL Server since it was last started. If the administrator checks the SQL Server when the I/O problem is serious, there is also a dynamic management view sys.dm_io_pending_io_requests, which can tell the administrator every I/O request that is currently pending in SQL Server.
Code:
select database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address fromsys.dm_io_virtual_file_stats(NULL, NULL)t1, sys.dm_io_pending_io_requests as t2 where t1.file_handle = t2.io_handle

In addition to dynamic management views and SQL Trace files, some counters related to SQL Server in Performance Monitor can also provide a lot of useful information. They can reflect the operating frequency of SQL Server causing I/O actions and their completion.

More helpful information:
https://www.brentozar.com/archive/2012/06/sql-server-poor-performance-checklist/
https://www.sqlshack.com/sql-server-monitoring-tools-for-disk-i-o-performance/

BR,
Mia
If the reply is helped, please do “Accept Answer” .

Answer 3

Hi @Giorgos ,

Is the reply helpful?

BR,
Mia
If the reply is helped, please do "Accept Answer".--Mia

Answer 4

Hi @Giorgos ,

Is the reply helpful?

BR,
Mia
If the reply is helped, please do "Accept Answer".--Mia