Recently, I have done SQL in Sixty Seconds video on how to read the IO statistics of the SQL Server. The video got very popular and you can watch it over here SET STATISTICS IO ON – SQL in Sixty Seconds #128. Recently one of my clients of Comprehensive Database Performance Health Check ran this on the production server and they noticed scan count as Zero on the result. They reached out to me asking what actually it means. Let us learn about it today.
When you run SET STATISTICS IO ON before your SQL Query, it will display the IO related statistics in the messages tab. In this tab, the very information is related to Scan Count. There can be three different types of numbers in the Scan Count.
Scan Count Zero (0) – if Index seek has occurred on Primary Key
Scan Count 1 – if index seek/scan has occurred on non-unique non-primary Key
Scan Count > 1 – if multiple numbers of seeks/scans occurred
Let us see a couple of simple scripts as well.
Run the following script:
SET STATISTICS IO ON SELECT * FROM [WideWorldImporters].[Purchasing].[SupplierCategories] WHERE SupplierCategoryID = 3
The script above will give the following result as there is a seek on Primary Key.
Table ‘SupplierCategories’. Scan count 0, logical reads 2
Now run the following script:
SET STATISTICS IO ON SELECT * FROM [WideWorldImporters].[Purchasing].[SupplierCategories] WHERE LastEditedBy = 1
The script above will give the following result as there is a scan clustered index.
Table ‘SupplierCategories’. Scan count 1, logical reads 2
I hope this simple explanation helps to understand what actually scan count stands for. If you have any questions, please leave a comment on this blog post. Additionally, do not forget to watch another video on a similar topic SET STATISTICS TIME ON – SQL in Sixty Seconds 139. You can subscribe to my YouTube Channel.
Reference: Pinal Dave (https://blog.sqlauthority.com)
