December 24, 2004 at 2:54 am
I have been reviewing the results from a SQL trace and I have a number of SP:CacheHit events.
Great, but there are no stored procedures in the database being monitored and the filter to exclude system events has been set.
There is an etnry in the objectid column but if I query sysobjects for EVERY database on my system these objectids do not exist.
What is this SP:CacheHit event?
December 24, 2004 at 4:00 pm
David,
Any ad hoc query that is cached will give a Cache Insert and Cache Hit events, and apparently assigns an object id in doing so. For example, set up a session of SQL Profiler with the Cache events and run a query in Query Analyzer, e.g., select count(*) from sysobjects.
NOTE: I found that on two different machines I got differing results: on a Win XP Profressional with SQL 2000 Developers SP3a I did not get caching, but on a SQL 2000 Server SP4 with SQL Standard SP3a I did. Perhaps this is due to differences in amount of RAM available to cache But that's purely a guess.
However, sp_executesql always cached the query as expected, and gave an apparently arbitrary object id. At least I could not find the id in sysobjects in any (user, system) database.
exec sp_executesql N'select * from sysobjects where id = @id', N'id int', 2
Change the value for @id, and you find the same object id in SQL Profiler. Change the query itself, and you get a different Object ID.
I believe your trace captured some cached, ad hoc queries.
Scott Thornburg
Volt Information Sciences
December 30, 2004 at 11:10 am
Yes, SQL Server, especially 2000, does try to parameterize ad hoc queries and store the execution plan. If there is enough memory, then the execution plans will stick around for a while. As a result, the execution plan could end up getting re-used.
In Books Online, you can read more on this under:
SQL Server Architecture >>
Relational Database Engine Architecture >>
Execution Plan Caching and Reuse
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply