1

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

0

1 Answer 1

1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.