September 20, 2010 at 7:58 am
Hi
one of our instances was constantly at 100% CPU. We noticed a steady few hundred compilations/s and drilling down we saw that similar statements categorized as AQ (adhoc) in profiler got compiled every time. I read some topics on forced parameterization and decided to turn it on for this specific database.
Results confuse me:
+
CPU went down to 10%
Procedure cache increased from 75% to 87%
-
Compilation/sec went up by 3x (300-400/sec)
#records in sys.dm_exec_cached_plans increased from 5000 to 15000
I don't understand how the CPU% decreases while the number of compiles increases.
Any thoughts?
Thx
Thierrry
September 21, 2010 at 2:20 am
Hi
thierry
When the PARAMETERIZATION option is set on, any literal value that appears in a SELECT, INSERT, UPDATE or DELETE statement, submitted in any form, is converted to a parameter during query compilation.
This link may use full to understand
http://technet.microsoft.com/en-us/library/ms175037.aspx
Ali
MCTS SQL Server2k8
September 21, 2010 at 9:06 am
Hi Ali
thx for the reply. However, I posted this topic after reading that article 🙂
What I'm confused about is that there's more plans in the cache and more compilations occurring while CPU usage decreased than when using parameterization=simple.
I'll try to explain what's happening in more detail...
There's an ad-hoc UPDATE statement being run about 4000 times per minute. Almost all 4000 statements are different in terms of parameter values and WHERE condition hence about 4000 cached plans. I was seeing about 100 compiles per second (not re-compiles) and rather high CPU usage which I attributed to the compiles.
When I set PARAMETERIZATION=FORCED on the database, CPU usage dropped significantly and procedure cache hit increased by 10%. That was a good thing and was as I expected.
However, I would have thought that the number of cached plans would have dropped significantly as well because exactly the same statements but with different parameter values would use the same plan. Instead the number of cached plans increased (x3). Also, the number of compiles trippled. Vary odd because I thought having that much compiles/sec would just increase CPU usage even more.
So, I'm still puzzled...
Regards
Thierry
February 8, 2011 at 7:30 am
Any resolution on this?
I am interested in what you found was the cause of increased compiles and increased number of cached plans.
If you are having parameterization problems with a single adhoc update, you may want to look into creating a plan guide for your specific query(S) that force PARAMETERIZATION or if you have access to the code itself you can use the PARAMETERIZATION FORCED query hint.
See the BOL articles below for help.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/f0f738ff-2819-4675-a8c8-1eb6c210a7e6.htm
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/66fb1520-dcdf-4aab-9ff1-7de8f79e5b2d.htm
February 8, 2011 at 8:22 am
Hi Eric
nope. Never really found the cause. Since I got no further response and it wasn't really critical didn't put more effort into it. Too bad but stuck on other projects now...
Regards
Thierry
November 6, 2012 at 2:48 pm
The optimizer will create shell queries that do not contain the full execution plan but only a pointer to the full plan (not actual execution plans).Run the following qry and you should see plans and they will have a size of about 24k(24576) which is 3, 8k pages. for the shell plans.
this should help more....http://technet.microsoft.com/en-us/library/cc293623.aspx
select * from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle)
November 6, 2012 at 8:04 pm
That's a very interesting article. Thx for sharing.
Cannot verify however, it's been over a year and the app has been modified in many ways and the database was also moved to new hardware, OS, SQL version.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply