SQL Server Best Practices – Before Applying Trace Flags

sql serversql-server-2012

We are considering enabling trace flags 1118, 4199, 8048 on a server to fix some some performance issues

We are running into performance issues since migrating from a physical server running SQL Server 2008 to a virtual server running SQL Server 2012 (2 sockets 24 core, 40GB RAM) particularly around one of the ETL process.

As it is a difficult service to get downtime for, is it safe to apply without any downtime? Or, is it wise to restart the service with these as startup parameters, I'm particularly concerned with the trace flag 8048

Best Answer

As for Trace Flag 8048, this MSDN blog says:

HOW DO I KNOW IF I NEED THE TRACE FLAG?

The issue is commonly identified by looking as the DMVs dm_os_wait_stats and dm_os_spinlock_stats for types (CMEMTHREAD and SOS_SUSPEND_QUEUE). Microsoft CSS usually sees the spins jump into the trillions and the waits become a hot spot.

Caution: Use trace flag 8048 as a startup parameter. It is possible to use the trace flag dynamically but limited to only memory objects that are yet to be created when the trace flag is enabled. Memory objects already built are not impacted by the trace flag.

Have you identified the need for trace flag 8048 as per the instructions above?

Since 8048 needs to be implemented as a startup trace flag, I'd configure 1118 at the same time. I'd also consider adding 1117 if you are concerned about tempdb performance.

4199 does not need to be implemented as a startup trace flag, although that is certainly recommended once you've established that you really need it, and your workload will benefit from the great many changes it introduces.

On that note, I'd recommend enabling these trace flags only after extensive testing with representative load on a test server.

Related Question