0

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??

Dor Cohen
  • 16,769
  • 23
  • 93
  • 161
Storm
  • 4,307
  • 11
  • 40
  • 57
  • What are you asking? Is it "What is a logical read?" or "Why am I getting this number of them?" If the second please show more context (function definition, how you are calling it) also are you getting the numbers from Profiler or `SET STATISTICS IO ON` output? – Martin Smith Jun 10 '12 at 09:59
  • The query you've listed is just a variable assignment, and does not produce any IO data, so I suspect you've forgotten to show us the actual query (or queries) where you're seeing reads. – Aaron Bertrand Jun 10 '12 at 13:57

1 Answers1

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.

Community
  • 1
  • 1
Dor Cohen
  • 16,769
  • 23
  • 93
  • 161
  • I would agree with you if there was a table involved, but look at the query, there's no table !! – Storm Jun 11 '12 at 06:59