Peoplesoft SQL Server Tuning

Trace Flag 4199 and PeopleSoft

Whilst I have a general rule not to use SQL Server trace flags unless I actually have the underlying issue, there is one exception:

Trace flag 4199

It switches on all the Query Optimizer (QO) fixes and saves you turning on the numerous individual QO fixes one by one. This trace flag is very well documented on the web (e.g. at Microsoft) and many people consider it essential for their systems. I do too.

One of the interesting comments in the above Microsoft article (last updated in 2012, but present in the article from the original in 2010) is the statement that future query optimizer fixes will not have individual trace flags of their own. So you are pretty much forced to use it in preference to individual trace flags going forwards.

PeopleSoft and TF 4199

In my experience, TF4199 is essential for PeopleSoft systems. Some of the early 2005/2008 fixes covered by TF 4199 are fundamental to good query execution plans in many areas of Peoplesoft. In particular, the fixes related to Fast Forward Read Only cursors are especially relevant as Peoplesoft makes extensive use of them.

Note: Trace flag 4199 is not a panacea for all your PeopleSoft performance problems – I have seen it slow down some SQL statements as well. But typically the affected statements were user queries that were badly designed in the first place 🙂

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.