SQL's 50 Shades of Grey...Areas

No, this is not a forthcoming erotic SQL novel, that's next year’s big project :-) This piece has actually been brought about by a post on one of the LinkedIn forums that I frequently visit.

Recently a post went up about best practices for a new SQL Server. The article included a number of things we should do to configure and improve your SQL instance post install.

Let’s take an example question, how many TempDB files should I have? Commonly we might see one these answers (btw, all of the answers above have been taken from actual posts in reply to the question):

1 – Leave it at one, the system should look after itself.

2 – It should have one file per core.

3 – It depends.

Answer 1 is absolute rubbish. Answer 2 might be right in some cases but the guideline is wrong. Answer 3, well it’s kind of right but not very helpful!

Clearly the conflicting advice out there can be very confusing, especially for those new to SQL and the fact is that for many settings within SQL there are no definitive answers or best settings that can just be applied. Thanks to the varying mixture of advice out there many settings and actions get referred to as "grey areas", take parallelism, memory, maintenance, indexing to name just a few.

But are they really grey areas?

You see, we DBA’s are rather evidence based. Many of these grey area settings in SQL are dependent rather simply on what is going on.

Take our TempDB question above. There is no single right answer. It isn’t 2,4,8 or even 32. The answer lies in evidence, are we seeing TempDB contention or not? If we are, we need to add files. Have we added too much? Well, are we seeing an increase in IO waits?

Ultimately guidelines serve as just that, a guide to area’s that we should look into and not just to suggest a supposed magic number that should be used. It is the analysis and problem solving technique that needs to be taught because although there are grey area’s, all of the answers lie in black and white evidence.

SQL 2005 - As one door closes

I've seen lots of articles circulating about the end of life for SQL 2005 which is happening very soon, on the 12th of April next year.  At the same time we're all seriously anticipating the release of SQL Servers next version, 2016 which is claimed to be the biggest release of SQL Server ever.  

Talk about when one door closes...

When I first started using SQL I used SQL 2000. I don't recall too much about the product itself in any detail but being a developer as I was back then I do remember tools like DTS, Enterprise Manager and of course, good old Query Analyser. But oddly enough what I remember most about SQL 2000 was upgrading to 2005.

You see 2005 brought with it a bundle of new goodies. Database Mirroring and Snapshots, the Service Broker, enhancements to T-SQL like TRY...CATCH, CLR integration, a whole bunch of DMV and DMF's and plenty more enhancements and features than what I can list here but make no mistake, game changer was an understatement!

So when I say I remember upgrading to SQL 2005 I don't just mean the product. I changed with it too. Not least in that I started to move from development into database administration and I started a career that I am still doing and throughly enjoying now.

And why was SQL 2005 responsible? It wasn't that it was just around at the time. For me that version helped lift the lid on what was going on inside SQL, to me everything became more accessible. For example, the dynamic management objects provided me with new information and with that came the necessity to gain a new understanding of how it all worked. It was amazing.

Of course there have been releases since and bit by bit our 2005 estates began to disappear. 2008 and 2008R2 were fine, yeah they were good. 2012 and 2014 brought some more exciting new functionality to the table (pun intended) and started to create the way forward but nothing had that same impact as what 2005 did. That is until now.

SQL2016 CTP 3.1 has arrived. I've listened to all of the talk. I've read an awful lot about it for months now; release notes, blog posts and training material. I then jumped in. In-Memory OLTP has been enhanced from the rather disappointing SQL2014 introduction. We now have PolyBase, Stretch Database, GZip Compression, JSON support and the Query Store - among other things! 

This now feels very familiar. New and exciting functionality, new ways to explore what our instances are doing. New skills and new discovries. This is once again rather amazing and for anyone considering a career as a SQL DBA, there really is no better time - believe me!

So to SQL2005, "so long old friend".

Hello to my new friend!

Script: Last x Full Backup History

It's a very common task that we have to query backup timings and other bits of info from msdb. 

Now its pretty straight forward to select this data for a particular database or use the MAX function for example to return the last backup but how do we get the last 5 FULL backups per database? 

You might want to check for spikes in backup duration for example and for this particular bit of functionality the RANK and PARTITION functions in T-SQL come in really handy. 

SELECT database_name, backup_start_date, backup_finish_date, backup_duration 
    FROM (
        SELECT database_name, backup_start_date, backup_finish_date,
DATEDIFF(MINUTE, backup_start_date, backup_finish_date) AS backup_duration,
RANK() OVER (PARTITION BY database_name -- "group my data"
                ORDER BY backup_start_date DESC ) AS Rank -- sorting by backup start date descending
        FROM msdb.dbo.backupset WHERE [type] = 'D'  -- full backups
        ) rs 
WHERE RANK <= 3 -- controls the top x of returned records for each "group" of data

So now we can take the output of this script and include it in Excel or SSRS to provide some trend analysis charts on backup timings etc. Very useful part of your monitoring script collection.

Some things to bear in mind, the script will include backups taken with the COPY_ONLY option and if you perform maintenance on backup history removing data over x weeks old, make sure the script accommodates this.

SQL Server 2016 - TempDB Number of Files

One change to the installation process of SQL Server is the ability to configure TempDB at the point of install, not afterwards...which means another restart of the SQL Server.

Now the installation process gives us the ability to specify number of files, initial size and autogrowth options for our TempDB database.

Yes, I know its the C drive - this is my sandpit VM - in ALL other instances TempDB files are on a dedicated volume.

Why would you need to do this? 
Well multiple files help in some way to ease TempDB contention which roughly speaking refers to a bottleneck in accessing allocation pages...when an object like a temporary table is created in TempDB a page is allocated and a metadata page is held to essentially map pages to the table.

Multiple files assist in reducing the problem by spreading the allocation workload over our multiple files.  TempDB contention is seen on our SQL instances by the presence of PAGELATCH_UP or _EX waits if the resource description is 2:1:1 (PFS Page) or 2:1:3 (SGAM)

Here's a little code snippet to look for these wait types:

select session_id, wait_duration_ms,  resource_description
from sys.dm_os_waiting_tasks
where wait_type like 'PAGE%LATCH_%'
and resource_description like '2:%';

I've encountered SQL instances with only one file for TempDB so many times and a quick check for these waits can mean a very simple but effective fix for some resource problems. 

OK, thats fine but how many files? 
Good question, as a general rule it is widely accepted that there are no particular drawbacks for having four TempDB files as a minimum. You may read advice that you should assign one file per core but this is another one of those famous SQL Server myths. Evidence should be the driver here so add four files and check for contention. If it exists, add more files until a happy balance is found (note, too many files can increase PAGEIOLATCH waits).

Configuring TempDB in the install process is a real time saver and a welcome enhancement. It will (hopefully) also increase awareness of potential problems like contention...and that is only a good thing.

For more information, check out Paul Randals article on the subject of TempDB contention here: http://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/

In-Memory OLTP

SQL Server blog article on the benefits of using In-Memory OLTP in SQL Server 2016.

Although one of the most looked forward to features of the 2014 release of SQL Server In-Memory OLTP (Hekaton) had plenty of limitations to go with it. Many of these have been lifted in SQL 2016, so will it lead to more takers?

http://blogs.technet.com/b/dataplatforminsider/archive/2015/12/10/speeding-up-transactions-with-in-memory-oltp-in-sql-server-2016-and-azure-sql-database.aspx

SQL DBA Funny!

Another great read from Redgate...

Following on from my post saying that Troubleshooting for the Accidental DBA was my favourite SQL Server read, here comes another from Redgate and again its available as a free PDF download, SQL Server Hardware by Glenn Berry.

http://www.red-gate.com/community/books/sql-server-hardware?utm_source=ssc&utm_medium=publink&utm_campaign=books&utm_content=hardware_ebook&utm_term=sqlserverhardware

Here is what they say about the book:

"SQL Server Hardware will provide the fundamental knowledge and resources you need to make intelligent decisions about choice, and optimal installation and configuration, of SQL Server hardware, operating system and the SQL Server RDBMS.2"