How do you reduce lob logical readings?
One of the ways to reduce lob logical reads is to rewrite the query to simplify it. Remove unneeded columns and reduce the returned rows to the least required. When necessary, use OFFSET and FETCH for paging.
What is lob logical reads in SQL Server?
This is exactly what happens with LOB pages. The part of data is stored on In row page type and other part is stored on off row. So when you do select * it would read in row pafe as well as LOB pages thus increasing count to twice on single read. In your case it has to read two data pages and then the LOB page.
How can I see logical reads in SQL Server?
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 first copies the page from disk into the cache. In other words, when SQL Server reads data from the memory, it is called Logical Read.
How to reduce logical reads in SQL Server?
Reduce logical reads (and the according large intermediate result) by applying both criteria in one step: (This may also effect physical reads, but doesn’t necessarily have to. For instance the first query may access 100 records and then reduce that to 10, whereas the second only reads those 10.
Which is the best way to reduce logical read?
Usually the best way to reduce logical read is to apply correct index or to rewrite the query. Physical read indicates total number of data pages that are read from disk.
How can indexes reduce the number of reads?
So indexes are great for reducing reads because they allow us to store only the data that is needed for a specific query (both as key columns and included columns). Fewer columns = greater density = fewer reads necessary. However, indexes can become fragmented.
How to write more selective SQL server queries?
Writing more selective queries can be done in a few different ways. For starters, if you don’t need every column of data, don’t use SELECT *. Depending on the size of your rows, providing only the columns you need might allow SQL Server to use an index that is narrower and/or denser.