SQL SERVER – 2005 – Different Types of Cache Objects

About two months ago I reviewed book SQL Server 2005 Practical Troubleshooting: The Database Engine. Yesterday I received a request from reader, if I can write something from this book, which is not common knowledge in DBA community. I really like the idea, however I must respect the Authors copyright about this book. This book is unorthodox SQL book, it talks about things which can get you to fix your problem faster, if problem is discussed in book. There are few places it teaches behind the scene SQL stories.

Following four lines are taken from Chapter 4 ; Procedure cache Issues Page 184-185.

Serveral kind of objects can be stored in the procedure cache:

Compiled Plans: When the query optimizer finishes compiling a query plan, the principal output is compiled plan.

Execution contexts: While executing a compiled plan, SQL Server has to keep track of information about the state of execution.

Cursors: Cursors track the execution state of server-side cursors, including the cursor’s current location within a resultset.

Algebrizer trees: The Algebrizer’s job is to produce an algebrizer tree, which represents the logic structure of a query.

Book has above four topics discussed in depth. I will limit myself with one line due to copyright reasons. You can read my review about book on amazon(not affiliate link).

Reference : Pinal Dave (http://blog.SQLAuthority.com)

4 thoughts on “SQL SERVER – 2005 – Different Types of Cache Objects

  1. Hi,

    In syscacheobjects, we see different kind of objTypes (Prepared, Adhoc), can you explain them, and how we can configure these objects accordingly, from the performance point of view.


  2. Hello Mazhar,

    syschacheobjects table returns list of all cached objects. The column objTypes tells about the type of obect like proc (stored procedure), view, trigger, prepared (statements executed using sp_executesql or other prepare function of API), ad hoc (stand alone t-sql statements), etc.
    We can not update this table as this is like a cache memory usase status report.

    Pinal Dave


  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s