Index scans & cache usage

Question

Post reply

Index scans & cache usage

ckempste

SSCoach

Points: 17983
More actions
January 28, 2002 at 7:16 pm

#78239

Hi

In SQL Server, are "index trees" also stored in the buffer cache? for example, if I ran a query and XYZ non-clustered index is used, I may get something like this:

Table 'tc'. Scan count 1, logical reads 6, physical reads 6, read-ahead reads 0.

If I run the query again (dont clean the buffers), I get:

Table 'tc'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0.

So, as an index is a physical data structure, its also being read into the buffer cache including all its associated header, branch, and leaf node information. My questions are:

a) Is the buffer cache the place it index resides?

b) What I described above, is it correct?

Cheers

Chris

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 728159

January 29, 2002 at 12:11 pm

#422819

I beleive yes and yes, but I can't point you to a specific ref.

Steve Jones

steve@dkranch.net

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 2

Chris, one thing you can do is check syscacheobjects between the first and second times you run the query to see if an entry got added that is applicable. Something more to test with respect to caching...

K. Brian Kelley

bkelley@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/bkelley/

Edited by - bkelley on 01/29/2002 12:16:44 PM

K. Brian Kelley
@kbriankelley

ckempste SSCoach Points: 17983 More actions · Answer 3

ckempste

SSCoach

Points: 17983

January 29, 2002 at 8:15 pm

#422851

ill give it a look 🙂

Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Also keep in mind the way Microsoft uses query execution plans. They remain in memory until that have not been used recently and enough other changes have occurred (which it can tell by statistics and of course quires run more recently and often than the one in question). The query plan helps the stored query return faster after the first run, as it serves a guide map to the way to access the data. Just make a change to you query and the return to previous to see how it further changes the statistics.

K. Brian Kelley SSC Guru Points: 114532 More actions · Answer 5

The lazywriter process takes care of getting rid of old execution plans, but it doesn't get enacted unless it needs to reclaim the memory, either because another application starts up requiring the memory and the OS issues a request, or there has been enough activity such that SQL Server is forced to reclaim memory from the memory pool that it has. The lazywriter does this both for the procedure cache as well as the buffer cache since from SQL Server 7.0 on they share the same dynamic memory pool (along with connection settings, etc.)

However, what Chris is getting at is the fact that SQL Server has read from disk the first time (the physical reads), and brought the information into cache. The second time the query is run, it then returns that it didn't execute a single physical read. Data is stored in the buffer cache, but what he's after is whether or not the index tree is as well.

Chris, upon going back and looking at some things, you are indeed right, the index pages are in the buffer cache (which I think means you won't find any reference to it in syscacheobjects, silly me). From Inside SQL Server 2000:

quote:

The main memory component in SQL Server is the buffer pool; all memory not used by another memory component remains in the buffer pool. The buffer manager manages disk I/O functions for bringing data and index pages into the buffer pool so that data can be shared among users. When other components require memory, they can request a buffer from the buffer pool. A buffer is a page in memory that's the same size as a data or index page. You can think of it as a page frame that can hold one page from a database.

K. Brian Kelley

bkelley@sqlservercentral.com

http://qa.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley