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:

Kalen Delaney : Geek City: Lost Identity.

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.

The actual title of the piece is:

How I Get By Without sysadmin

But I think the essence is in a subtitle: Write Queries Organically

via How I Get By Without sysadmin | Facility9.

If I had my way, anyone who touches a SQL Server (that I have some responsibility for) would have to sign a declaration that they have read this article and pledge an oath to abide by it.

Similarly, anyone coming to complain about the SQL server being slow would have to provide a written explanation as to why they can’t improve the execution plan of the top three most expensive queries. Anyone caught with a 1,5GB heap table will be made to do the top ten most expensive queries before we even think of checking any performance counters.

Of course, the SQL Servers we’re talking about are never going to be perfect (not for the price the Business Partners are willing and able to pay) so there will often be something the DBA can do to improve performance or something they can suggest to the developer and/or user which would improve performance. However, going straight to the DBA without checking the basics is like calling the electrician when your TV doesn’t work because the batteries in the remote are dead. Unnecessary expense and possibly even ridicule may occur.

Yours, 100% friendlywise, A

Everybody’s worried that adding memory is somehow cheating.

Says this article:

via Using Memory for Performance Tuning in SQL Server by Brent Ozar Unlimited®.

I can’t say I’ve ever heard that worry expressed in that way. I hear one group worried about the price of adding memory. I hear one group worrying that adding memory will just delay the onset of the problem and that as the data volume increases, there will come a time when you can’t chuck memory at it anymore and the Godzilla like table/query will eat your performance.

We had a classic example the last few days. Customer screaming for blood because his tool is unusable and everyone passes the buck down the line until it stops where the poor sucker has no one to pass it on to. The louder the screams the more it will spread and the more „the buck stops here“ points/people will get hit with the stuff flying off of the fan.

So, we do traces and Perfmons whilst a precursory look at the database reveals a few heap tables with a Gigabyte of data each that are getting hit pretty often. It doesn’t take much thinking to realise that you can throw memory at the problem to get the heaps into memory or you can get indexes added and if still necessary, add memory. Adding an index is not exactly rocket science but the developer had previously tried to bully the assembled crowd with loud assertations that with all their years of experience they have optimised their indexes until there’s nothing more to be won. At this point a gentle suggestion that a few indexes must have then gone missing scince a 1.5GB indexless heap table would be one of the most obvious signs of poor database design, allowed the poor fellow to save some face and to later admit that some indexes must have gone missing in the porting process from MySQL.

The fact that the porting from MySQL to MSSQL was done using a Microsoft migration tool and that there were ample, but unheeded, warnings to check that all design structures had been ported/migrated can, of course, be put down to a blustering answer of „well we can’t check everything can we?“. The bluster is of course true, I cannot expect them to check that the moon is not made of green cheese or where the first occurence of my telephone number within the decimal expression of PI happens to be, but from my position, I do believe that if someone has a problem with „my“ product then I do think it is my duty to check that I have my house in order before I try to shovel the blame on someone else…