Have you ever wondered what the scan count means when you enable the SET STATISTICS IO ON option in SQL Server? In this blog post, we will explore the concept of scan count and its significance in SQL Server.

When you run a SQL query with the SET STATISTICS IO ON option enabled, SQL Server displays IO-related statistics in the messages tab. One of the statistics displayed is the scan count, which provides information about the number of index seeks or scans that occurred during the execution of the query.

The scan count can have three different values:

• Scan Count Zero (0): This indicates that an index seek has occurred on the primary key.
• Scan Count 1: This indicates that an index seek or scan has occurred on a non-unique non-primary key.
• Scan Count > 1: This indicates that multiple seeks or scans have occurred.

Let’s take a look at a couple of simple scripts to understand this concept better.

Script 1:

SET STATISTICS IO ON
SELECT *
FROM [WideWorldImporters].[Purchasing].[SupplierCategories]
WHERE SupplierCategoryID = 3

The above script will produce the following result:

Table 'SupplierCategories'. Scan count 0, logical reads 2

Since there is a seek on the primary key, the scan count is zero.

Script 2:

SET STATISTICS IO ON
SELECT *
FROM [WideWorldImporters].[Purchasing].[SupplierCategories]
WHERE LastEditedBy = 1

The above script will produce the following result:

Table 'SupplierCategories'. Scan count 1, logical reads 2

In this case, there is a scan on the clustered index, so the scan count is one.

Understanding the scan count can provide valuable insights into the performance of your SQL queries. By analyzing the scan count, you can identify whether the query is utilizing indexes efficiently or if there are any potential performance bottlenecks.

If you have any questions or would like to learn more about this topic, please leave a comment below. And don’t forget to check out our video on a similar topic: SET STATISTICS TIME ON – SQL in Sixty Seconds 139. You can also subscribe to our YouTube Channel for more informative content.