October 20, 2017 at 8:49 am
I am seeing that when a stored procedure uses column-level encryption, it no longer is in the sys.dm_exec_procedure_stats DMV.
Have not been able to find any info on this and hoping someone here might be able to explain and/or help with a resolution/work around.
Many thanks
October 25, 2017 at 12:17 pm
regan.wick - Friday, October 20, 2017 8:49 AMI am seeing that when a stored procedure uses column-level encryption, it no longer is in the sys.dm_exec_procedure_stats DMV.Have not been able to find any info on this and hoping someone here might be able to explain and/or help with a resolution/work around.
Many thanks
I tried but couldn't reproduce this - are you sure that the stored procedures are in cache? Sounds like they may not be cached.
Sue
October 25, 2017 at 12:40 pm
Hi Sue-
Thanks for taking time to try a re-pro and to reply.
Actually, this is the issue - that it is not in the cache and therefore is not in sys.dm_exec_procedure_stats.
All things being equal except without column-level encryption, the sproc is in cache. Then once column-level encryption is added, it no longer is in cache.
I am looking to get stats on sproc executions and am able to see dm_exec_procedure_stats for all sprocs except those using column-level encryption.
October 25, 2017 at 1:27 pm
regan.wick - Wednesday, October 25, 2017 12:40 PMHi Sue-Thanks for taking time to try a re-pro and to reply.
Actually, this is the issue - that it is not in the cache and therefore is not in sys.dm_exec_procedure_stats.
All things being equal except without column-level encryption, the sproc is in cache. Then once column-level encryption is added, it no longer is in cache.
I am looking to get stats on sproc executions and am able to see dm_exec_procedure_stats for all sprocs except those using column-level encryption.
Are they low cost plans or were the stored procedures created with option recompile? Not sure but I thought there was a way to check things being removed from cache using Extended events but don't have anything set up and haven't had a chance to play with that. I created a few different stored procedures referencing encrypted columns and they were all cached after I executed them. Try a look through this article and see if it gives you any other ideas:
Execution Plan Caching and Reuse
Sue
October 25, 2017 at 2:23 pm
OK - I found the issue. Somehow missed it in all previous searches.
Not sure what approach we will be implementing but at least there is an explanation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply