SQL Trace Flag T2453

Trace Flag T2453:

Function: Allow a table variable to trigger recompile when enough number of rows are changed with may allow the query optimizer to choose a more efficient plan.

The performance tuning Tech Ref guide says to turn this on. So does this EpiCare article: Knowledge Page - EpicCare (epicor.com)

However, the P&D Tool config check gives us a FAIL if we turn it on… “Fail - Please turn off the following trace flag for the server, use steps from the following link http://blogs.msdn.com/b/psssql/archive/2014/08/11/if-you-have-queries-that-use-table-variables-sql-server-2012-sp2-can-help.aspx

What should we do? @aidacra

Did you end up turning this flag off or on?

Does it make a differences?

Support never got me an answer on this. I turned it off because Support uses any P&D “failures” as an excuse to stop troubleshooting…

I doubt T2453 makes much of a difference either way; our performance is pretty good.

1 Like

Personally, as a DBA overseeing our Epicor instances, I find that PDT behind the times when checking items like this. Depending on your SQL version, some of those trace flags are baked in automatically. Some are redundant like 1222 for Deadlock logging in the event log. I would rather use Extended Events for items like deadlocks and blocking along with other metrics. It’s also the first thing support or consultants pick on, even if they are minor infractions.

Also, your milage may vary depending on your workload for different trace flags or recommendations. Especially if you SQL instance does double duty for SSRS or you have unneeded services loaded and running like SSIS or SSAS.

If SQL performance is an issue, using the PDT tool is not the only weapon for that fight. I would recommend using a monitoring built to measure SQL performance along side it, like offerings from Red Gate or Quest. Even if it’s just a trial to get you out of harm’s way and get a peak into the internal side of you SQL instance.

Edit: Also check your CU patch level. Some performance issues may have been dealt with that way too.