If you are having slow disk reads, these will show up in the Quick Scan Reports page, part of the Database Health Monitor application.
Slow disk reads can really impact the performance of your SQL Server database.
There are a few ways to deal with the slow disk reads:
- Perhaps the most obvious, faster storage. Switch to SSDs or drive configurations that give you more IOPS.
- Multiple drive configurations. Different drives for your log files, your data files, and your TempDB. Perhaps even a different drive for the database that has the most IO.
- More memory. If feasible, add more RAM so that more of the databases are kept in memory, thus reducing the long term IO. This will however require the same amount of IO after the SQL Server is restarted, once it is up and running there will be less IO.
- Restart less often. If you have a regular process to restart your SQL Server, after the restart, there are no databases in memory, and they need to be loaded in entirely from disk. There is generally much larger IO demand right after a reboot, and as the load increases after that reboot.
Database Corruption: how is this impacted by slow disk reads?
If you encounter database corruption, one of the things you will likely need to do is to run DBCC CheckDB, DBCC CheckTable, or other DBCC checks. These will usually need to be run multiple times to try different detection and repair options. Slow disk I/O will mean that it will take longer (sometimes significantly longer) for your checks and attempted repairs to run. Dealing with slow disk reads early can have a big impact if you do detect database corruption.
Stedman Solutions, the provider of the Database Health Monitor Application offers consulting solutions, and can help with your database administration issues such as Slow IO.