There are a few situations which might make my Dr. Bruce Banner style of social interaction turn into something much uglier, this poster illustrates one of them 😉
Let’s all give a hearty round of applause for whoever thought it would be a good idea to let this kind of danger out into the wild.
Now that the technology is out there and has been found, any bad guys can get their hands on the technology and apply it for their own purposes.
Remember, any weapon is a weapon you don’t want in the hands of your enemies so don’t go handing them out like candy.
Anybody doing SQL Server service providing will have uttered this phrase at least once in their professional carreer. This piece provides some anyalyis of the reasons.
I know, it’s hard to do somethings right. I also know there’s a lot of bad advice out there. This is pretty good though, for now. One of the problems with security is that as soon as a method becomes popular, it becomes a target for the bad guys/gals/ones.
Some of these points will (famous last words) remain relevant until passowrds become a thing of the past.
I too used to belong to the crowd thanking Microsoft for the IDENTITY column type, I came from an MSACCESS background around about the end of SQL Server 7 (sic) and I was relieved to find I didn’t have to do any tedious „mucking around“ with identity value tables.
Of course, I also knew that there is no such thing as a free lunch and that SQL Server must be doing some housekeeping for me so it couldn’t be as performant as it would be with „raw“ data. But it was a heck of a lot faster than any T-SQL solution.
In SQL Server 2012 Microsoft, bless their cotton socks, „improved“ the IDENTITY column type and, naturally, in some minds broke it. Microsoft weren’t (all) born yesterday so they added a Trace Flag to enable you to get the old behaviour back. Nice. Here are some details:
“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.