Page read statistics

1. Page read statistics

We saw that with STATISTICS TIME we could get a measure the total time a query takes to run. Another way we can review query performance is by examining the amount of disk or memory activity generated by the query. We can do this by using the STATISTICS IO command.

2. Table data pages

Before we start, we need a brief understanding of how data is stored in a database table. All data, either memory or on the disk, is stored in 8 kilobyte size "pages". Depending on the size of each row of data, a single page could store many rows, or just one value could span multiple pages. A page can only belong to one table. If the tables are small, each table would require it's own page. SQL Server will do most of its work from pages cached in memory. If a query requests a page that is not in memory it is read from the disk into the memory cache.

3. Customers: data pages

This is an example how we might imagine the first four data pages of our Customers table is stored.

4. STATISTICS IO in SSMS

Like STATISTICS TIME, STATISTICS IO is initiated before running a query with SET STATISTICS IO ON. The statistics are displayed in SSMS in the Messages tab to the right of the Results tab.

5. Logical reads

All the statistics reported by STATISTICS IO are useful for query performance tuning. However, as an introduction to STATISTICS IO in this lesson, we’ll focus on the logical reads measure and how it might be used to tune query performance. Logical reads are reported for each table. They are a measure of the number of the 8 kilobyte pages read from memory to process and return the results of our query. In general, the more pages that need to be read the slower our query will run.

6. Example: query 1

Let's look at an example of the logical reads measure using STATISTICS IO. Our query returns the maximum earthquake Magnitude where a country capital city was the closest city to an earthquake of magnitude 7.5 or higher. This query uses three sub-queries:

7. Example: query 1

The first is a correlated sub-query in the SELECT statement to get the maximum magnitude earthquake.

8. Example: query 1

The second is another correlated sub-query to check for the presence of country capitals in the Earthquakes table.

9. Example: query 1

And the third sub-query applies a filter to be passed back to the Nations table.

10. Example: query 1

To get the statistics to display, we run SET STATISTICS IO ON before executing our query. The results are reported against each table that participates in the query. The output shows fifty-four pages read from the Earthquakes table and three pages read from the Nations table.

11. Example: query 2

We can re-write the query this time using an INNER JOIN between the Nations and Earthquakes tables. Because STATISTICS IO is already on we do need to rerun it.

12. Example: query 2

The output shows the same amount of page reads from the Nations table, three, but three times fewer page reads of the Earthquakes table, only eighteen. Once we’ve completed our assessment of the statistics, we turn it off by running SET STATISTICS IO OFF.

13. Comparing queries

Using an INNER JOIN on the Earthquakes table, in the second query, the entire table was read once when the query was processed, eighteen pages. Multiply that by the number of sub-queries accessing the Earthquakes table in the first query, and this increases to fifty-four. Therefore we may expect the first query to run slower than the second because more pages are accessed. We can confirm this by running the STATISTICS TIME command on each query, twenty-nine milliseconds for the first query and three milliseconds for the second.

14. Let's practice!

Let's practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.