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

  • 1
    The best practice would be to test the trace flags in a dev environment to see if they help first. – Erik Darling Mar 28 '17 at 14:04
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.

  • The opposite of an Oxford comma! – Erik Darling Mar 28 '17 at 19:37
  • 1
    Thank you, I've raised a call with Microsoft and they instructed to enable 8048. But, we are going to test this first before we apply it to production. These are the stats from those tables. wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms CMEMTHREAD 103861429 13897261 620 9033990 name collisions spins spins_per_collision sleep_time backoffs SOS_SUSPEND_QUEUE 173822228 305588621024 1758.053 366097 44017440 – Damian O'Brien Mar 29 '17 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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