Opinionated Software

… we have opinions about everything!

Trace Flag 4136 and PeopleSoft —

Trace flag 4136 effectively adds an OPTION(OPTIMIZE FOR UNKNOWN) to all SQL statements. This has the effect of making the optimizer ignore histogram statistics in generating execution plans.

If you have extremely skewed data distributions this can actually result in a much better execution plan – it essentially gives the impression of more “consistent” performance by ensuring that you don’t end up with an execution plan suited to only one subset of your data. But it will not necessarily be the best execution plan you could manage,

For PeopleSoft I have found this trace flag to be useful in some edge cases but my personal preference is to look at the problem SQLs and optimize them on a case by case basis. Typically this can be achieved through addition of indexes and/or multi-column statistics. Multi-column statistics in particular are especially useful with PeopleSoft applications.

Note: All too often skewed data distributions are a direct result of poor choices made at implementation time. In PeopleSoft Financials in particular I have seen this where the choice of BUSINESS_UNIT was made based on a concept more suited to a roll-up node in a tree. In that particular case, had the customer used their DEPTID as the BUSINESS_UNIT it would have eliminated the problem at source. You can always roll data up for reporting purposes!

 

 


Categorised as: Configuration | Peoplesoft | Performance | SQL Server



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.