Help understanding the buffer cache and logical reads

I am trying to find out the memory usage for a particular query, and to
understand how data pages can be re-used in the buffer cache. I am using the
Total Server Memory counter (in performance monitor) and dbcc memusage in
analyzer.
I am trying to prove if the following is right. If I run a SELECT * FROM
Contact and cache the data pages. If I run subsequent queries involving the
same table will I incur any more overall memory usage because the necessary
data pages will already be in cache.
I suppose this test is difficult to do because of the inherent memory usage
associated with users connecting and and compiling and executing queries.
However, I am going down this route of restarting SQL Server to clear out
the buffer, and so that it starts off very small. Then, in query analyzer I
run a dbcc memusage (to see the top 20 items in cache and to check that it
hasn't got any tables in it)
dbcc dropcleanbuffers (so the cache is clean)
then I run a query, e.g. SELECT * FROM Contact
the results after running a dbcc memusage is that there are contact table
pages are in fact in the cache because the total buffer pool page count has
increased and you can see it in the table (by it's ID)
However, I've noticed that the logical reads is very high and physical reads
is 0. To me this indicates that the data is already in cache (i.e. the
contact table) because no physical reads had to be done to get them into
cache in the first place? How is this possible?
Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
read-ahead reads 4704.
I would have expected to see that the physical reads would be 5045?
Does anyone know more about this and if there is a better way to understand
the memory usage for a particular query and the effects of caching?
Read ahead is occurring, and when read ahead turns on it does larger reads
( perhaps an extent and maybe larger)...Logical reads is in pages, since
read ahead is reading larger blocks, there are reads.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
> I am trying to prove if the following is right. If I run a SELECT * FROM
> Contact and cache the data pages. If I run subsequent queries involving
the
> same table will I incur any more overall memory usage because the
necessary
> data pages will already be in cache.
> I suppose this test is difficult to do because of the inherent memory
usage
> associated with users connecting and and compiling and executing queries.
>
> However, I am going down this route of restarting SQL Server to clear out
> the buffer, and so that it starts off very small. Then, in query analyzer
I
> run a dbcc memusage (to see the top 20 items in cache and to check that it
> hasn't got any tables in it)
> dbcc dropcleanbuffers (so the cache is clean)
> then I run a query, e.g. SELECT * FROM Contact
> the results after running a dbcc memusage is that there are contact table
> pages are in fact in the cache because the total buffer pool page count
has
> increased and you can see it in the table (by it's ID)
>
> However, I've noticed that the logical reads is very high and physical
reads
> is 0. To me this indicates that the data is already in cache (i.e. the
> contact table) because no physical reads had to be done to get them into
> cache in the first place? How is this possible?
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
> I would have expected to see that the physical reads would be 5045?
> Does anyone know more about this and if there is a better way to
understand
> the memory usage for a particular query and the effects of caching?
|||Robert,
You will most likely confuse yourself by going that route. Look up "free
buffer pages" in BooksOnLine for details on how the cache works in more
detail. But in a nutshell sql server will keep data and indexes in cache as
long as you have enough free memory to do so. At some point when it runs
out of cache it will start to reuse the pages. Each page gets a counter
that determines which ones should be forced out to make room for new ones
over the others. This is determined by several factors including how often
you access it. So it is likely that if you run select * and read in a whole
table you can actually reuse portions of cache for the end of the table that
you just read in for the beginning of the table. Other times it will cache
the whole table. It depends. SQL Server does a really good job of
determining what should stay and what should get overwritten so you
shouldn't concern yourself with it. What you need to look at are the Buffer
Cache Hit Ratio and Page Life Expectancy counters. If the Cache hit ratio
is above say 95% your doing good. If the Page Life expectancy is low you
have a lot of memory pressure and could probably benefit from more ram or
most likely more optimization. Inside SQL Server 2000 also has a great
section covering this topic.
Andrew J. Kelly SQL MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
> I am trying to prove if the following is right. If I run a SELECT * FROM
> Contact and cache the data pages. If I run subsequent queries involving
the
> same table will I incur any more overall memory usage because the
necessary
> data pages will already be in cache.
> I suppose this test is difficult to do because of the inherent memory
usage
> associated with users connecting and and compiling and executing queries.
>
> However, I am going down this route of restarting SQL Server to clear out
> the buffer, and so that it starts off very small. Then, in query analyzer
I
> run a dbcc memusage (to see the top 20 items in cache and to check that it
> hasn't got any tables in it)
> dbcc dropcleanbuffers (so the cache is clean)
> then I run a query, e.g. SELECT * FROM Contact
> the results after running a dbcc memusage is that there are contact table
> pages are in fact in the cache because the total buffer pool page count
has
> increased and you can see it in the table (by it's ID)
>
> However, I've noticed that the logical reads is very high and physical
reads
> is 0. To me this indicates that the data is already in cache (i.e. the
> contact table) because no physical reads had to be done to get them into
> cache in the first place? How is this possible?
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
> I would have expected to see that the physical reads would be 5045?
> Does anyone know more about this and if there is a better way to
understand
> the memory usage for a particular query and the effects of caching?
|||"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
>I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
> the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
.. . .
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
>
The thing you're missing is the read-ahead. There is a thread devoted to
read-ahead caching. When SQL Server decides that it may need to tablescan a
table, it sets the read-ahead thread to reading the pages into the cache.
Then when the execution plan of the query needs the pages, they may already
be in the cache.
Point is, the read-ahead reads are physical reads, they are just performed
by another thread.
David
|||I see, great.
So in fact physical reads are occuring, but this is shown by the read-ahead
read value. So if I start off with an empty cache or nearly empty cache, pin
a table and do a select * from table, then it might not appear as physical
reads, but as read-ahead reads (and from a previous reply these aren't shown
in pages, but could be extents, so the value is larger than the logical
reads)...and overall this achieves the same thing by placing the data in
cache for future anticipated reuse.
Next, I was going to find the total buffer page count before and after a
query and multiply this value by 8. Does this sounds feasible? Perhaps, is
there a better way to measure the memory use for a query?
"David Browne" wrote:

> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> .. . .
> The thing you're missing is the read-ahead. There is a thread devoted to
> read-ahead caching. When SQL Server decides that it may need to tablescan a
> table, it sets the read-ahead thread to reading the pages into the cache.
> Then when the execution plan of the query needs the pages, they may already
> be in the cache.
> Point is, the read-ahead reads are physical reads, they are just performed
> by another thread.
> David
>
>

Help understanding stored procs

I am having trouble understanding how to correctly use stored procs. Here is what I think the process is. Correct me where I am wrong.

I am developing a read-only program with VB 2005 as a front end and SQL Server back end. The user will enter an ID# for search criteria which will be passed as a parameter to a stored proc. I want to create a stored proc that alters a view by changing the ID# the view filters on. A dataset will be created in the front end from that view.

So in SSMS, I create a new proc like this:

CREATE PROC {blah, blah}

@.IDnum

AS

BEGIN

ALTER VIEW {blah, blah}

AS

SELECT {blah, blah}

FROM {blah}

WHERE blah.ID = @.IDnum

END

I would run the code to create the proc, then modify it to an ALTER PROC. I've tried this without success. What am I missing. Yes, I am new to this.

Thanks for the help

Hi,

why do you want to alter a view, rather than using a filter on the view (although the view isn′t that column rich :-) )

So you could create the View as

CREATE VIEW SomeView
AS

SELECT col1, col2 From SomeTable

and then select using

SELECT * FROM SomeView Where Col1 = 'SomeId'

HTH, Jens Suessmeyer

http://www.sqlserver2005.de

|||

Thanks for the reply. I am entering the world of SQL Server from an Access background. In Access I often change the filtering of a query by changing the sql with code or by referencing a field on a form from the criteria field of a query.

That is the thinking with which I have approached views. The purpose of my post is to check my thinking against the experienced community and hopefully get better ideas and understanding. Your reply helps. Thanks again.

|||You′re welcome :-)

Help understanding Stored proc

hi guys! Can anybody please explain what does lines 4,6,8,13 and 26 does? Thanks in advance!

1 USE [PawnShoppeDB]
2 GO
3 /****** Object: StoredProcedure [dbo].[sp_Customer_AddCustomer] Script Date: 06/14/2007 16:50:07 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER Procedure [dbo].[sp_Customer_AddCustomer]
9 @.Customer_FirstName varchar(50),
10 @.Customer_LastName varchar(50),
11 @.Customer_MiddleInitial varchar(5),
12 @.Customer_Address varchar(100),
13 @.Identity int output
14 AS
15 Begin
16 Declare @.DateCreated DateTime
17 Set @.DateCreated = getDate()
18
19 Insert into RCPS_Customer
20 Values (@.Customer_FirstName,
21 @.Customer_MiddleInitial,
22 @.Customer_LastName,
23 @.Customer_Address,
24 '0',@.DateCreated)
25
26 Set @.identity = Scope_identity()
27 EndLol.

Check books online (SQL Server help) for:
ANSI_NULLS
QUOTED_IDENTIFIER
Scope_identity()
output parameters

Alter proc replaces an existing proc with the same name with the code you posted. That'll be in the help files too.

Help understanding generated MDX WHERE clause

When your report datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

Really no MDX guru around who can explain this to me?|||

Henk,

I would suggest posting your MDX question in the SQL Server Analysis Services forum at this url:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

|||Thanks, I'll do that.