The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.
If you’re returning 3 rows of data, but the query engine had to scan 200 million rows of the table to do it, that’s going to be very slow and you can probably improve that by rewriting the query or adding an index.
Less logical read the database has to do the better the performance.
Logical read are page reads. 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.
Comments