Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Forced parameterization Expand / Collapse
Author
Message
Posted Monday, September 20, 2010 7:58 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 698, Visits: 398
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
Post #989382
Posted Tuesday, September 21, 2010 2:20 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 29, 2014 11:09 PM
Points: 434, Visits: 876
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
Post #990040
Posted Tuesday, September 21, 2010 9:06 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 698, Visits: 398
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

Post #990402
Posted Tuesday, February 8, 2011 7:30 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 9:04 AM
Points: 59, Visits: 561
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

Post #1060209
Posted Tuesday, February 8, 2011 8:22 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 698, Visits: 398
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
Post #1060282
Posted Tuesday, November 6, 2012 2:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:41 AM
Points: 391, Visits: 766


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)






Post #1381718
Posted Tuesday, November 6, 2012 8:04 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 698, Visits: 398
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.
Post #1381773
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse