is Forced Parameterization usually more beneficial than less?
-
On a hybrid server, with ~70% OLTP vs 30% (somewhat heavy) DW workloads,
with over 60% of all heaviest recompilations coming from AdHoc queries:
We view the recompilation reasons via the Histogram (via the below XE Session)
and querying it (also below) that produces this result (this is for most recent 24 hrs).
Should I strongly consider
ALTER DATABASE <all 199 DBs on server<adhocs equally distributed accross the most dbs>..>
SET PARAMETERIZATION FORCED?
There are mixed opinions on this.
including
I would appreciate your advice/experience/comments.
Note: We are not (ready for) dealing with this at this time on query by query basis and not intending either, and we are not rewriting each adhoc into SP and modifying half a mil ASP web pages either...).
CREATE EVENT SESSION RecompileReasons ON SERVER
ADD EVENT sqlserver.sql_statement_recompile
ADD TARGET package0.histogram
(SET filtering_event_name=N'sqlserver.sql_statement_recompile',
source=N'recompile_cause',
source_type=(0)
)WITH (STARTUP_STATE=OFF, MAX_DISPATCH_LATENCY = 5SECONDS)---query:
SELECT sv.subclass_name as recompile_cause,
shredded.recompile_count
FROM sys.dm_xe_session_targets AS xet
JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)
CROSS APPLY ( SELECT CAST(xet.target_data as xml) ) as target_data_xml ([xml])
CROSS APPLY target_data_xml.[xml].nodes('/HistogramTarget/Slot') AS nodes (slot_data)
CROSS APPLY (SELECT nodes.slot_data.value('(value)[1]', 'int') AS recompile_cause,
nodes.slot_data.value('(@count)[1]', 'int') AS recompile_count
) as shredded
JOIN sys.trace_subclass_values AS sv ON shredded.recompile_cause = sv.subclass_value
WHERE xe.name = 'RecompileReasons'
AND sv.trace_event_id = 37 -- SP:Recompile
--------------------------------Likes to play Chess
-
Meh.
It's a very mixed bag. It can improve performance a little in some workloads. It does nothing at all in a lot of workloads. For a few, really rare workloads, it can actually cause pain.
Test it is really all I'd say. It's not something I avoid, but it's also not something I'd turn on by default. Unlike Optimize for Ad Hoc. That I'd turn on automatically. It doesn't always help, but I've never yet seen, or even heard about, it hurting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Grant,
Thank you for your input. It is as valuable for me as all your comments and guidance always are, both here at SSC and in your books (I can firmly say that I have read 25% of every book that you have ever written/published. And carefully flipped through probably half of them 🙂 with great appreciation).
SUBquestion:
When we enable FORCED PARAMETERIZATION, obviously, we are not going to do it on ALL databases (right away).
From the hypothetical total 200 Dbs (MSSQL 2016) on the server, if we enable this feature on 50 of them, what would be our best criteria for selecting those particular 50 out of 200? (again, with the assumption that ad-hoc queries with high recompiles are equally distributed among all 200 DBs).
What else would be important to take into account for the first batch of DBs to turn Forced Parameterization on?
Likes to play Chess
-
Have you enabled 'Optimize for Adhoc Workloads' at the server level?
As for which database - I would identify one or more databases where you can evaluate the application after making the change, or you have quick access to the users of that application. Monitor the system and work with those users to see if the change affects the performance of the application.
I would not just set that on a bunch of databases with the goal of setting it on all. It should be reviewed for each database and validated that there are no negative effects and does improve the issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - VoldemarG wrote:
Grant,
Thank you for your input. It is as valuable for me as all your comments and guidance always are, both here at SSC and in your books (I can firmly say that I have read 25% of every book that you have ever written/published. And carefully flipped through probably half of them 🙂 with great appreciation).
SUBquestion:
When we enable FORCED PARAMETERIZATION, obviously, we are not going to do it on ALL databases (right away). From the hypothetical total 200 Dbs (MSSQL 2016) on the server, if we enable this feature on 50 of them, what would be our best criteria for selecting those particular 50 out of 200? (again, with the assumption that ad-hoc queries with high recompiles are equally distributed among all 200 DBs).
What else would be important to take into account for the first batch of DBs to turn Forced Parameterization on?
Thanks for the kind words.
You're not asking an easy question here. With an equal distribution on the ad hoc queries, I'd be hard pressed to make the call there. Probably the bigger ones or the ones with higher volume overall. However, you might be just as well served by picking the ones that the boss likes the best. Without a clear distinction on the one thing that's going to get helped, ad hoc queries, it's just hard to say.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - VoldemarG wrote:
... modifying half a mil ASP web pages ...
I have to ask (as in seriously curious)... you have a half million individually programmed ASP web pages? What kind of a web site are your running?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
-
Well, i exaggerated about #webpages, just to emphasize that there are a lot. 100s..
Likes to play Chess
-
What would be a good measure to take Before and After showing that enabling FORCED PARAMETERIZATION actually worked, some improvement achieved. Or no improvement achieved.
Would the Extended Events Session referred to in the original question of this chain, be sufficient? And overall reduction of #recompiles regardless of their cause in a 24 hr period After compared to Before - is enough to make determination on whether F.P. made any difference or not?
Likes to play Chess
-
Keep an eye on CPU and waits, before and after.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I think one reason to enable FORCED PARAMETERIZATION for specific DBs with high volume of ad-hoc queries would be to try and reduce the size of TokenAndPermUserStore cache. When this cache grows very large it can cripple a server. I'm not sure there's an easy way to determine which DB(s) are contributing to this specific problem though.
On the other hand, I've run into the case where FORCED PARAMETERIZATION rendered some filtered indexes useless https://www.sqlservercentral.com/articles/strange-filtered-index-problem
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply