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 (https://blog.sqlauthority.com)
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.
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.
Thanks, that saved me a lot of time. How many decades have you spent blogging MSSQL? You seem like a bit of an internet ninja at it!
Thanks again, Katharr.