SQL Logical Reads

Question

I have a query that run a lot due to it being in a function that is called for a large row set.

the query is SELECT @sql = NULL WHERE @sql = ''

Even though this shows me 0 physical reads.

It shows me around 17000 Logical Reads.

Any explanations??

Answer 1

Look at the following question & answers:

https://dba.stackexchange.com/questions/9302/why-so-many-logical-reads

Those are page reads, mind you. That is relevant.

Possibilities:

• Improper or insufficient indexing. Are any of the filtered fields indexed? How wide are the indexes (see below)?
• Poor page density. What's your fill factor on any indexes you may have? If it's too low, you are pulling a lot of pages for this.
• Very wide indexes. If you have indexes but they have a lot of fields, or very wide fields, then you get less rows per page.

I'm guessing you have a table or clustered index scan happening for at least one of these criteria, and the table is wide-ish which causes a lot of data to be read, regardless of how much data you actually need.