“And the world could use a little less human and a little more divine right now.” is a quote from the following, informative and useful, piece:
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.
Sometimes, you hear of something and you think „that’s just what I need!“ only to find that in the real world „things are a bit more complicated than you think“
Is an example of this. At first sight I thought „why isn’t this the standard setting“ but this question-and-answer article might explain why…
„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.
You might be able to tell that TempDB has been on my mind a bit lately. Unfortunately, TempDB is not the name of some person or social thing, it’s about work and the place where SQL Server stores its temporary stuff (and mine or any other SQL programmer’s stuff.)
In this webcast you get a pretty good idea of what you should do and look out for in your (if you have a SQL Server) Database environment. (And, nowadays, who doesn’t have a „database environment“? … comeone, sure you do… or?)