How to Improve I/O performance for SQL Server
If underline I/O architecture system is working properly then every time SQL Server do some transaction (Read/Write) it can do it without waiting. But if load on the system is up, then SQL transaction have to wait for their turns for processing. This can significantly reduce SQL Server performance. There is a much greater issue when you are not in charge of the underlying storage system. For that the main thing is to optimize the queries running by SQL Server.
Following are some tip to monitor and increase I/O performance of SQL Server.
Queries using maximum I/O.
This query will return top 25 queries that were the slowest in processing.
SELECT TOP 25 q.[text], (total_logical_reads/execution_count) AS avg_logical_reads, (total_logical_writes/execution_count) AS avg_logical_writes, (total_physical_reads/execution_count) AS avg_phys_reads, Execution_count FROM sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(plan_handle) AS q ORDER BY (total_logical_reads + total_logical_writes) DESC
Check which queries can be optimized and update them. Execute the above query frequently to check queries that can potentially create I/O bottleneck on system.
Reading un necessary Data.
When you are retrieving data, which is not going to be used than you are also overusing your I/O performance. One great example of this type of query is:
SELECT * FROM [Table]
Always retrieve only those columns that are going to be used. In cases where you want to get all the columns than specifically write all column names on your select clause.
Also use a WHERE clause where applicable because it will filter out rows that will not be used.
Create Covered Indexes
Once you have optimized your queries by using explicit column names and appropriate WHERE clause filtration of records, you can now focus on creating non-clustered covered index for the queries that have excessive read I/O. Covered indexes contain all columns as a part of index that are being used in SQL WHERE clause. You can also determine what columns are in need to be indexed by examining SQL execution plan of the query.