Forced parameterization

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Forced parameterization

Forced parameterization

Rate Topic

Display Mode

Topic Options

Author

Message

thierry.vandurme

Posted Monday, September 20, 2010 7:58 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 582, Visits: 286

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

Ali Tailor

Posted Tuesday, September 21, 2010 2:20 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, October 25, 2012 2:19 AM
Points: 394, Visits: 869

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

thierry.vandurme

Posted Tuesday, September 21, 2010 9:06 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 582, Visits: 286

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

Eric1/2aB

Posted Tuesday, February 08, 2011 7:30 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 02, 2013 11:50 AM
Points: 54, Visits: 518

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

thierry.vandurme

Posted Tuesday, February 08, 2011 8:22 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 582, Visits: 286

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

456789psw

Posted Tuesday, November 06, 2012 2:48 PM

Old Hand

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:44 AM
Points: 384, Visits: 704

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

thierry.vandurme

Posted Tuesday, November 06, 2012 8:04 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 582, Visits: 286

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 »

Permissions