March 17, 2011 at 6:35 am
Question...
Our developers have ported over an application originally developed on Oracle. Therefore, there are no SPROC's. The application sends Ad-HOC queries to SQL. The problem here is every statement is getting compiled. There are around 100,000 compiles per hour.
So, to battle this, I have added a Plan Guide for a few of the heavy hitting queries with FORCED Parameterization for these Guides.
I can see the parameterized plan being used in the plan cache, so I think its working properly. My question however is when I look in profiler, I see a SP:CacheHit and a SP:CacheInsert for each statement. Why am I getting the SP:CacheInsert? Does this ensure the plan stays in the cache?
Thanks-
Jason
March 17, 2011 at 8:55 am
It's an artifact I wouldn't sweat it. If you're getting the cache hit, you're good to go. You can also look at the execution plans to see if the plan guide is getting used. It's in the property sheet for the first operator in the plan.
Also, you might want to take a look at the "Optimize for ad hoc workloads" option. It could help you a bit.
"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
March 17, 2011 at 9:00 am
Great to know! I do see the parameterized plan in the plan cache.
I'm checking out the ad hoc workload option. Looks like it will help. We also have several SPROC's. It's roughly a 75/25 ratio of adhoc/sproc. How will the ad hoc option affect the sprocs?
Thanks again!
Jason
March 17, 2011 at 10:44 am
jason.spangler (3/17/2011)
Great to know! I do see the parameterized plan in the plan cache.I'm checking out the ad hoc workload option. Looks like it will help. We also have several SPROC's. It's roughly a 75/25 ratio of adhoc/sproc. How will the ad hoc option affect the sprocs?
Thanks again!
Jason
Good question. I'm not entirely sure. The only place I've used it was on systems that were running ORM tools.
I would assume a positive influence since it reduces the amount of cache used up by single use queries, but it's not something I've tested.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply