January 14, 2015 at 11:21 am
I am running the following query to find some store procedure that ran today on a specific database
select OBJECT_NAME(object_id), * from sys.dm_exec_procedure_stats where type = 'P'
and database_id = 68
I am looking for a specific stored procedure 'Proc1' but it's not there in the cache.
This was called by a SQL job this morning about 6 hours ago and the job had completed successfully. I see some other stored procedures that had executed prior to this time. Any ideas why I am not seeing the particular stored procedure in the cache?
Thanks
January 14, 2015 at 3:36 pm
It aged out of cache is the first thing that comes to mind. Just because others were created earlier, they may also have been accessed more recently. Another thing is that it could have a RECOMPILE hint. Then it wouldn't get stored in cache at all. After that, I'm not sure.
Just in case, check sys.dm_exec_query_stats and join it to sys.dm_exec_sql_text and see if you can find the proc referenced that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2015 at 4:01 pm
The complexity of the plan is also a factor in how much SQL weights keeping a plan in cache (by assigning more complex plans higher initial values).
Also, I believe ad-hoc plans automatically get a "0" rating, meaning as soon as SQL gets under memory pressure and checks the plan cache they are gone barring immediate re-use.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply