SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Forced parameterization


Forced parameterization

Author
Message
thierry.vandurme
thierry.vandurme
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 560
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
Ali Tailor
Ali Tailor
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1063 Visits: 885
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
thierry.vandurme
thierry.vandurme
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 560
Hi Ali

thx for the reply. However, I posted this topic after reading that article Smile

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
BrerSQL
BrerSQL
Old Hand
Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)Old Hand (306 reputation)

Group: General Forum Members
Points: 306 Visits: 578
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
thierry.vandurme
thierry.vandurme
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 560
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
456789psw
456789psw
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 841
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)
thierry.vandurme
thierry.vandurme
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1384 Visits: 560
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search