Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
 Posted - 2010-03-11 : 14:53:17
|
| I know it's a pretty stupid question, but I can't find a good answer anywhere. They all seem circular "A read is when the server reads".For example, what does 58 reads mean in SQL Profiler on a sproc that couldn't have looked at more than 1 record?Thanks,JimEveryday I learn something that somebody else already knew |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-11 : 15:07:07
|
I found that good explained:Firstly, Reads represent the number of 8kb "page reads" performed by SQL Server to execute any given query. SQL Server stores table rows on 8kb pages on disk & these pages are read from disk or memory buffers (if they're already cached in memory) during query execution. The Reads value reported for each SQL Trace event includes the number of these reads performed by each query (whether from disk or memory) & is therefore a key indicator of general query performance. The more Reads performed by any given query, the less efficiently it is performing. Common causes of inefficiencies in this area are lack of suitable indexes on tables, leading to table scans when SQL Server executes its queries. Usually these problems can be resolved by "tuning" the query by adding better indexes to the database (Database Tuning Advisor is useful in this area for less experienced DBAs).A couple of problems with filtering by the Reads column though - firstly, it includes non data page reads such as Procedure Cache page reads. Don't forget that SQL Server tries to avoid compiling every query it runs by "remembering" query plans by caching them in its Procedure Cache. Later, when the same query runs again, SQL Server first looks up its Procedure Cache to try & find a query plan rather than recompiling it each time it is run. The reads performed when looking up the Procedure Cache (which also uses 8kb memory buffers) are also included in the figure reported by SQL Trace's Reads output. Another problem is that the figure reported in the Reads output provides only a whole query total, giving no table by table breakdown. To obtain a more granular breakdown (at a table by table level), SET STATISTICS IO ON can be used at the connection level when re-running a query. So, whilst Reads does provide a generally useful measurement of query workload, it isn't perfect & can sometimes be mis-leading if the Procedure Cache is abnormally large or transient.Source: http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2006/12/11/492.aspx
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-03-11 : 15:17:22
|
| Thanks Webfred, you rock! That makes a lot of sense and explains a lot of what I was seeing in the Profiler.Thanks,JimEveryday I learn something that somebody else already knew |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-12 : 12:45:39
|
I ask that very question to every DBA candidate I interview -- even junior DBAs   |
|
|
|
prodigy2006
Yak Posting Veteran
66 Posts |
Posted - 2010-03-12 : 16:18:11
|
| Logical reads-. A logical read is something where the database engine requests a page from the buffer cache. Physical reads- If the page is not found in the buffer cache, a physical read is then performed to read the page into the buffer cache. |
|
|
|
|
|
|
|
|
What is a read?