There are many tools available to assist a DBA/developer when performance tuning SQL statements. Reviewing the output of SET STATISTICS IO ON is fundamental in understanding what your query is doing. This output will show you how many physical and logical reads are performed per table.

This information can be turned on by executing SET STATISTICS IO ON in the query window before you run the query. When you execute the query the statistics will be displayed in the messages window of SSMS. Although this information is informative it can be difficult to recognise the pain points especially for a complex query that accesses a lot of tables.

e.g. (this isnt too bad, I have seen a lot worse).

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Lookup'. Scan count 112, logical reads 448, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SupplierAddress'. Scan count 0, logical reads 224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Quantity'. Scan count 56, logical reads 112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Quay'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Language'. Scan count 0, logical reads 6, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ItemsSupplier'. Scan count 6, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ItemType'. Scan count 5, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Items'. Scan count 133, logical reads 418, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customers'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Country'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Country'. Scan count 0, logical reads 117, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Port'. Scan count 0, logical reads 6, physical reads 4, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Port'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VesselType'. Scan count 30, logical reads 804, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Targets'. Scan count 5, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Berth'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Berth'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Items'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VesselType'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FreightPayer'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CustomersMapping'. Scan count 38, logical reads 76, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Sales'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Sales'. Scan count 1, logical reads 486, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The other limitation is that the output is for that query execution only, there is no history. As you modify your query you review the output and then compare this with the previous execution and you can then see the effect that your query changes make. This is a manual process (but you can use something like excel to make this easier but this involves a lot of copy/paste).

To make this process easier I have created a Power BI report that will easily combine and visualise this data so you can identify what effect your changes are having on the IO. All you need to do is download Power BI desktop (free) and the attached template (SQLIOStatusTemplate.pbit) and follow the below instructions to make performance tuning SQL statements that bit easier.

Here is the report:

You can select focus mode on each of the visualistions to expand them and see clearly what is happening.

Instructions for use

Note: You can use a different folder location but you will need to modify the location in the data source settings within the Power BI report.

Power BI has lots of options for displaying formatting data so you can slice and dice this data in whatever way you want. I have created visualisations for scan count, logical reads and phyiscal reads as that was all I was interested in at the time. However, you can easily modify this report to display whatever you like (as long as it is in the data set).

What I think is particularly useful is comparing multiple outputs so you can see clearly what is happening to the IO access as you modify your queries. Please download, use and modify the report and let me know what you think or suggest any improvements and I will try and implement them.

Thanks for reading Phil.

1. Download and install Power BI desktop.
2. Run your query with SET STATISTICS IO ON.
3. Save the output in the messages window in SSMS to C:\SQLIOStats (you can use whatever filename you like but keep the .rpt extension). The filename will be used as the source column.
4. Modify your query and execute. Save the output in the messages window to the same folder but use a different filename.
5. Download and open the attached Power BI template and click load.
6. Review the results.