Number of Reads in Profiler

Hi,

Can any of can explain, what the "Reads" column in Profiler exactly mean ? I'm not comfortable with the explanation given in BOL.

"The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution"

For the same procedure with same parameters, if the server is not loaded much, the Reads are in a few hundreds, but when there are more than 1000 concurrent users, why it is going to millions ? What other parameters affecting this reads ? And how can I reduce it ?

Environment: SQL Server 2005 64-bit Enterprise Edition on Windows Server 2003 R2 Server x64 Enterprise Edition SP2

Thanks in Advance.

Regards

Babu

This is a good question.

The reads column in the profiler represents the number of logical reads for a statement or batch.

What is a logical read?

SQL Server uses much of its virtual memory as a buffer to cache and reduce physical I/O. So SQL Server caches the physical I/O and then requests pages from the cache. Everytime your statement requests a page (pages are stored as 8K blocks) from the cache a "logical" read occurs.

The best way to reduce the number of logical reads is to tune your query. If you are using a lot of subqueries, aggregrates on subqueries, etc... this can lead to high logical i/o. One thing you should take a look at is to make sure your statements are utilizing indexes. Try using an index hint to force your statement to use a specific index. This can make a big difference in terms of performance.

Mike
|||

From the below mentioned Link

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. A logical write occurs when data is modified in a page in memory. A physical write occurs when the page is written to disk. It is possible for a page to remain in memory long enough to have more than one logical write made before it is physically written to disk.

check this ...

http://msdn2.microsoft.com/en-US/library/aa224763(SQL.80).aspx

Madhu

|||

Hi Mike/Madhu,

Thanks for your interest in this topic.

I'm still not clear, what's the "unit" for the number represented in this column.

1. Is it number of Pages ? If so, why the number of Reads increases when I request for same volume of data when the number of users connectd are more.

2. Is it number of attempts made to read a Page or Record ? Is the Locks influencing the Reads.

Database is having all feasible indexes and the procedures are tuned for the level best possible ( by me :-) ).

Thanks once again.

Regards

Babu

|||

logical reads Number of pages read from the data cache. physical reads Number of pages read from disk.

Refer SET SET STATISTICS IO documentation in BOL. ITs clearly documented there

Madhu