March 26, 2012 at 12:19 pm
opc.three (3/26/2012)
Brandie Tarvin (3/26/2012)
opc.three (3/26/2012)
Cool, thanks for posting back....
I would explore enabling the adhoc workloads server configuration if it's not already on.
Opc, you do realize I'm not the OP, right? I only posted because you asked, not because I needed advice in addition to what's already been posted on the thread or is in Kimberly's blog. But thanks, anyway.
Understood...this is a discussion site, right? Just carrying on...sorry.
Yeah, I'm just getting hit by a lot of this today: "Tell me the answer." "Here it is." "Well, you're not doing that right."
Sorry. Didn't mean to snap.
EDIT: Plus what Lynn said too.
March 26, 2012 at 12:28 pm
Thanks, Lynn. No worries, Brandie. I realize I can get a bit frenetic with my posts at times 😛 I wasn't trying to be pushy about contacting Kim, just saying, she may like to know from the person that ran into it so she can amend the post if she sees fit. I know you're not the OP, just carrying on with the thread is all. Hopefully the OP is still out there, and is getting something out of the discussion. Out of all the posts and mini-diversions I think we're still tracking the original issue trying to improve with PLE and cache hit ratios...would love to see the results of the corrected version of the plan-use query on her system.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 1:15 pm
these are my results on a consolidated SQL server supporting about 10 apps with 13GB out of 16GB assigned to SQL (64bit SQL2008 windows 2008 enterprise)
CacheType Total Plans Total MBs Avg Use Count Total MBs - USE Count 1 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- -------------------- --------------------------------------- -------------------------
Adhoc 73972 2419.91 8 717.03 14080
Prepared 1200 175.82 1796 71.80 459
Proc 548 134.45 1291 20.64 54
Trigger 24 5.74 61 2.55 9
Check 34 0.81 10 0.13 6
Rule 4 0.06 20 0.00 0
View 515 55.35 6 0.00 0
UsrTab 2 0.44 6 0.00 0
so if I read it right a preponderance of ad hoc SQL but they are getting some re-use?
---------------------------------------------------------------------
March 26, 2012 at 1:40 pm
george sibbald (3/26/2012)
these are my results on a consolidated SQL server supporting about 10 apps with 13GB out of 16GB assigned to SQL (64bit SQL2008 windows 2008 enterprise)CacheType Total Plans Total MBs Avg Use Count Total MBs - USE Count 1 Total Plans - USE Count 1
-------------------- -------------------- --------------------------------------- -------------------- --------------------------------------- -------------------------
Adhoc 73972 2419.91 8 717.03 14080
Prepared 1200 175.82 1796 71.80 459
Proc 548 134.45 1291 20.64 54
Trigger 24 5.74 61 2.55 9
Check 34 0.81 10 0.13 6
Rule 4 0.06 20 0.00 0
View 515 55.35 6 0.00 0
UsrTab 2 0.44 6 0.00 0
so if I read it right a preponderance of ad hoc SQL but they are getting some re-use?
You are seeing some re-use. Your adhoc plan avg use count is only 8. The server where I am freeing the 'SQL Plans' cache regularly hovers around 3. Brandie's was 30,609 meaning whatever is issuing queries to her instance looks to be in pretty good harmony with how SQL Server matches adhoc SQL to plans.
Your single-use adhoc plans as a percentage of all adhoc plans is much lower than Brandie's (19% to 46%) but the MBs used as a percentage of your buffer pool is much higher (18% to 6%). Were your readings from a warm server? If your server is in a stable state of equilibrium you may not need to worry. Do you have 'optimize for adhoc workloads' option enabled? If not, you may be able to benefit a bit from enabling it, as it could reduce the average size in MBs of the plans in the Adhoc cache.
Note: the 'SQL Plans' cache consists of both the Adhoc and Prepared caches and TMK we cannot free just the Adhoc or Prepared caches independent of each other.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 1:46 pm
george sibbald (3/26/2012)
so if I read it right a preponderance of ad hoc SQL but they are getting some re-use?
Not really. That last column tells me that the number of plans that have only been used once are listed there.
The thing that we aren't taking into consideration is dynamic SQL (which I know we have in my environment) and the ad hoc queries like the one we on this thread just ran to determine what settings or problems we have with our SQL Server. Things that truly are "one time". According to the articles, stubs will be saved and run, but I wonder how much that will really change things in my environment. Is everything going to save as a plan stub? Or will there actually be plan re-use by using this setting?
Only way to know for sure is to try it, if I can get buy in from the boss.
March 26, 2012 at 2:02 pm
If your server is in a stable state of equilibrium you may not need to worry. Do you have 'optimize for adhoc workloads' option enabled? If not, you may be able to benefit a bit from enabling it, as it could reduce the average size in MBs of the plans in the Adhoc cache.
Note: the 'SQL Plans' cache consists of both the Adhoc and Prepared caches and TMK we cannot free just the Adhoc or Prepared caches independent of each other.
its been up for over 24 hours so is pretty close to stable. I don't have optimise for adhoc on but have considered it, its a setting I make a note of for the apps I upgrade to SQL2008. Problem is I dont have any memory pressure so if it aint broke I'm not fixing it 🙂
---------------------------------------------------------------------
March 26, 2012 at 2:06 pm
george sibbald (3/26/2012)
If your server is in a stable state of equilibrium you may not need to worry. Do you have 'optimize for adhoc workloads' option enabled? If not, you may be able to benefit a bit from enabling it, as it could reduce the average size in MBs of the plans in the Adhoc cache.
Note: the 'SQL Plans' cache consists of both the Adhoc and Prepared caches and TMK we cannot free just the Adhoc or Prepared caches independent of each other.
its been up for over 24 hours so is pretty close to stable. I don't have optimise for adhoc on but have considered it, its a setting I make a note of for the apps I upgrade to SQL2008. Problem is I dont have any memory pressure so if it aint broke I'm not fixing it 🙂
I can't argue with you there 🙂
As a side note, some in the community have gone so far as to say you should enable the option by default on all new instances, and disable it when there is evidence against it. I am on the fence about whether I would take it that far.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 2:13 pm
Brandie Tarvin (3/26/2012)
george sibbald (3/26/2012)
so if I read it right a preponderance of ad hoc SQL but they are getting some re-use?Not really. That last column tells me that the number of plans that have only been used once are listed there.
You cannot only look at the total #. The percentage of single-use to the total and the average use-count are more important in my opinion.
The thing that we aren't taking into consideration is dynamic SQL (which I know we have in my environment) and the ad hoc queries like the one we on this thread just ran to determine what settings or problems we have with our SQL Server. Things that truly are "one time". According to the articles, stubs will be saved and run, but I wonder how much that will really change things in my environment. Is everything going to save as a plan stub? Or will there actually be plan re-use by using this setting?
As I understand when SQL is issued:
1. if a plan or stub is not found a new stub is generated and cached
2. if a stub is found stub is ejected from cache and full plan is cached
3. if a full plan is found it is re-used (same behavior as when option is disabled)
The first logic branch is as far as a single-use plan will ever go, idea being it limits cache bloat.
So for your stats, you will have 37,624 stubs in your cache instead of full plans. It will depend on your workload how much space that will save you, but it's worth looking into. On the flip-side, of the 81,031 total plans, the plans that have executed more than once (43,407) will get a stub at first, and will then be replaced by full plans on their second execution. That adds overhead. Where is the tipping point? As usual it will depend on the specifics of the workload, but you have a lot of single-use plans in terms of GBs and a percentage of total plans so it may be worth a look.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 4:17 pm
opc.three (3/26/2012)
As a side note, some in the community have gone so far as to say you should enable the option by default on all new instances, and disable it when there is evidence against it. I am on the fence about whether I would take it that far.
I'm not on the fence at all. But that's because I've been able, when I've been responsible for databases, to enforce a rule that no ad-hoc queries at all are allowed except for support staff to collect diagnostics when there's a problem (which shouldn't happen often, as all those systems are supposed to be 24/7 with a monthly maintenance slot of less than 1 hour, and some time out for major upgrades, which are rare. Everything is stored procedure calls, and I controlled what stored procedures existed. So optimising for ad hoc queries would be a total waste of time.
Tom
March 26, 2012 at 4:58 pm
L' Eomot Inversé (3/26/2012)
opc.three (3/26/2012)
As a side note, some in the community have gone so far as to say you should enable the option by default on all new instances, and disable it when there is evidence against it. I am on the fence about whether I would take it that far.I'm not on the fence at all. But that's because I've been able, when I've been responsible for databases, to enforce a rule that no ad-hoc queries at all are allowed except for support staff to collect diagnostics when there's a problem (which shouldn't happen often, as all those systems are supposed to be 24/7 with a monthly maintenance slot of less than 1 hour, and some time out for major upgrades, which are rare. Everything is stored procedure calls, and I controlled what stored procedures existed. So optimising for ad hoc queries would be a total waste of time.
I would not be on said fence either if I were in your situation. Kudos to you for being able to assert your views on the environment in which you work. For databases where I am present from concept to completion it is the ideal I aim for, but unfortunately I cannot say I have always been successful enforcing such a rule given the prevalence of third-party tools and lack of thought towards database health of disparate development teams for which I am sometimes asked to support databases. Further to that, in terms of inheriting systems from others the exclusive use of stored procs for data access is unfortunately, from what I gather, far from the norm.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 4:20 am
Hi,
Already enable Adhoc distribute query at instance level.
CacheTypeTotal PlansTotal MBsAvg Use CountTotal MBs - USE Count 1Total Plans - USE Count 1
Adhoc11177297.083992.085016
Proc240102.4245376.6138
Prepared27212.511212.9841
Trigger30.1820.162
Check140.370.125
UsrTab10.13200
View12011.837200
As per this result tells like, Adhoc Total plan - 11177, Average use count - 39, and single use - 5016.
Please suggestion me, why average use count very less & also single use plan 50 % of total cache plan? It seems like query optimizer is not using existing cache plan also not reuse.
Thanks
ananda
March 27, 2012 at 4:24 am
How much dynamic SQL do you use in your environment, ananda?
March 27, 2012 at 4:28 am
Brandie Tarvin (3/27/2012)
How much dynamic SQL do you use in your environment, ananda?
Thanks for reply..
Total 4GB RAM, 32 bit system, os - win 2008 enterprise edition,
SQL - SQL server 2008 sp3
I had set the max memory 3200 MB, Min Memory - 0
Currently dynamic Memory using 1599.813 MB for both Total & target
Thanks
ananda
March 27, 2012 at 5:26 am
ananda.murugesan (3/27/2012)
Brandie Tarvin (3/27/2012)
How much dynamic SQL do you use in your environment, ananda?Thanks for reply..
Total 4GB RAM, 32 bit system, os - win 2008 enterprise edition,
SQL - SQL server 2008 sp3
I had set the max memory 3200 MB, Min Memory - 0
Currently dynamic Memory using 1599.813 MB for both Total & target
Dynamic Memory is not the same thing as dynamic SQL.
Have you ever set a variable with the value of a T-SQL statement and run it? (Example below)
Declare @MyVar varchar(100);
Set @MyVar = 'SELECT Table_Name FROM Information_Schema.Tables';
Execute (@MyVar);
That's just a tiny example. This can be done in stored procedures, functions, job steps. Do you ever use this?
March 27, 2012 at 5:42 am
Thank you Mrs.Brandie Tarvin for explain dynamic SQL in sql server coding.
I m not developed Application & Database, Developer might be used dynamic SQL coding at database level. I am just monitoring server & database that's all.
Please suggestion me, how to calculate or collect these how much dynamic sql using this database? Could you share me any script for the same.
thanks
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply