What causes a high Scan Count in SQL Server IO Statistics?

Question

I understand that these are table or index scans that are being performed. I'm a little unclear on what would cause some tables to have a large scan count while others would have a small scan count. In a query I am looking at right now i am getting a scan count of about 150,000 on two tables in my query, while the rest have only a few.

I'm guessing that "it depends" but if you could point me to something that might help me identify what is causing this it would be much appreciated. Also if you are interested I could send you the query or any other info you might need. I'd just rather not post it here.

Answer 1

It depends on the query run and indexes, and whether your statistics are up to date.

Can you post your table's schema, the query being run and the 'actual' execution plane produced?

Answer 2

My understanding is that the scan count is the number of physical pages that needed to be accessed to execute your query. Simplistically, if there are no (or insufficient) indexes to help then SQL needs to read a lot more pages than it would otherwise. Have you looked at the query plan?

Answer 3

Are you just concerned about the high number of scans of do you really face some performance issue that you attribute to these scans?

How many rows do the tables have where you see these high scan counts? If there are only a handful of rows in a few pages, I wouldn't really bother.

For a start you might find this whitepaper worth reading: Troubleshooting Performance Problems in SQL Server 2005