Creating A Spinlock Convoy

Lonny Niederstadt explains how two performance issues can intertwine in an interesting manner:

I’d seen systems that implemented both trace flags as startup parameters simultaneously.  I’d helped organizations implement first T8048, then T4199 (based on the timing of my research and testing of the trace flags).  This was the first time that there was a desire to implement the trace flags one-at-a-time and we had the choice of which to implement first.

I hadn’t chosen to put T8048 in first previously – that was just the way everything worked out.  If I chose to follow that order – I’d be doing what I’d seen and done before.  But… there was also a reason to choose the reverse order, with T4199 first.  Spinlock issues – especially at that time – were considered more exotic performance issues than many of the “plan-shaping” issues that trace flag 4199 addressed.  Many administrators were much more familiar with that type of performance issue – eliminating significant waits, altering plan shapes, making the logical work of queries more efficient – than with the busy wait/management overhead of spinlocks.  Sometimes demonstrating an improvement that someone is already familiar with evaluating is a plus, helping to gain trust.  I didn’t know of a specific reason NOT to put trace flag T4199 in place, followed by T8048 later.  And in this case it seemed like building up some interpersonal capital might be a good idea.

Thinking through the full ramifications of trace flag changes is hard, even for sharp people like Lonny.  Read on for the details of what happened next.

Related Posts

Straight Talk On Trace Flags

Pam Lahoud explains the purpose of trace flags and talks about a very important trace flag, 4199: Some trace flags are used to enable enhanced debugging features such as additional logging, memory dumps etc. and are used only when you are working with Microsoft Support to provide additional data for troubleshooting. These trace flags are not ones […]

Read More

Generating SQL With Biml

Cathrine Wilhelmsen shows us you can do a lot more with Biml than just generating SSIS packages: This actually happened to me in a previous job. We had a fairly complex ETL solution for the most critical part of our Data Warehouse. Many SSIS packages, views, and stored procedures queried the tables that were replicas […]

Read More

Categories

March 2017
MTWTFSS
« Feb Apr »
 12345
6789101112
13141516171819
20212223242526
2728293031