This one is *great* even if you probably might never need it.

That having been said, I do come across some queries written by hot-shots who use hints in their queries…. and they often don’t maintain the queries when the table structure has changed making the hints counter-productive. How could they remember where they have used which hints and know which ones to fix? How will they find the time to do so? Being able to demonstrate that the hints are no longer correct without changing the source is a terrific way of avoiding the „you must have changed something else as well“ kind of reactions from  certain types of people.

SQL Server Trace Flag 8602.

„If you are still facing SGAM contention then you can use trace flag 1118“ it says in the article.

This flag is often quoted as one you should set in order to improve sqL Server performance. To be fair, the flag used to have a lot more significance than it does today but nowadays, before knee-jerk reflex applying the flag you should first look at the performance counters to see if you are actually being slowed down by the thing this trace flag changes.

The key here is to look at your tempdb usage to see if you are getting any PAGELATCH_EX locks on the tempDB. If you aren’t then adding this trace flag might actually negatively impact the SQL Server performance as a whole.

By the way, the „SGAM“ referred to in the linked article is „Shared Global Allocation Map“ so as you might guess it’s a map of memory which is shared so there might be some contention if two or more processes are trying to get at memory in the shared allocation map at the same time.

The bottomline is, as so often, take a baseline, make the change if you find some cause to do so, check the results, and keep or reject the setting as appropriate.

via sql server trace flag 1118.