Which Columns Are Being Updated
Produced: 26/12/2014 10:14:00
This came about as a result of a series of mass updates being applied to a replicated table. This series of updates was causing replication to lock up and become so far behind that the only acceptable course of action was a full rebuild. This is by far and away the course of action I least like taking, but in this example it was unavoidable.

Huge tempDB Log File
Produced: 19/12/2014 16:27:00
I had an interesting problem appear this morning in which the tempDB Log file began to grow… and by grow I mean it went from just 10GB to well over 150GB in one morning and showed no signs of stopping.

Copying User Access From Database to Database
Produced: 12/12/2014 16:59:00
This topic came about the other day when I was required to move some tables into a completely new database as part of a schema restructure and new project.

Don't Panic Your Code Isn't Lost
Produced: 05/12/2014 16:58:00
The other day I had a very stressed developer coming to see me… he had been in the middle of coding something when his PC pulled the ever inventive “Blue Screen of Death” trick on him.

Forcing Parallelism (TF 8649)
Produced: 28/11/2014 16:54:00
In this first episode of Sheldon Cooper’s Fun with Flags I’m going to be looking at my favourite flag of the moment… Trace Flag 8649.

And yes, before anyone points it out, I know having a favourite trace flag is infinitely more disturbing than having a favourite real life flag but I’ve learnt to live with it and you should too.

Memory Settings in sp_configure
Produced: 21/11/2014 14:27:00
Now this is a fun thing that I REALLY should have known, but have never really played with the therefore was mystified when it came up in conversation recently. I’ve asked a few other SQL guys and they were unaware of the true meaning of this setting either (which made me feel a little better). It’s not something I’ll be forgetting anytime soon though.

Hash Join Operator
Produced: 14/11/2014 13:56:00
This is the last of the join operators and possibly the most powerful. It is possibly the most common join operator and, for some reason, one of the ones I hear people most saying they’d like to avoid. Personally I wouldn’t agree with that at all… although it does have a few down sides (which I’ll cover later in this post), it’s by far and away the best choice when joining large datasets together and should therefore be welcomed.

Merge Operator
Produced: 07/11/2014 17:42:00
Continuing with the posts on Join Operators I’m going to move on from the Nested Loop join and give a very brief explanation of the MERGE operator.

Nested Loop Operator
Produced: 31/10/2014 17:36:00
Join operators within SQL Server seem to be very misunderstood in general as people are forever telling me that they want a query to use this or that types of operator for the joins in their plan and they will even use hints to force these. Sadly it seems to come from a misunderstanding that certain types are better than others. This is not the case as they are all designed for a reason and each have their speciality.

Local Variable Gotcha
Produced: 24/10/2014 17:11:00
We all use local variables and in many cases they’re invaluable… however, there is a lesser known (in my experience anyway) gotcha with them which can badly affect your queries and leave you wondering why performance has unexpectedly dropped.

Using Duration in Profiler and SSMS for Query Tuning
Produced: 17/10/2014 16:57:00
This is a quick post regarding a couple of things I found someone doing the other day when using Profiler and SSMS to tune one of his queries. Basically he was getting frustrated because he had been making changes to his code which he believed would speed up his query and yet he wasn’t getting anything consistent out of either SSMS or Profiler to prove this.

Nicely Formatted HTML Email of SQL Table
Produced: 10/10/2014 16:49:00
Okay, in my last post I showed you how easy it is to include a table of results in an email, but by all accounts it was a little bit bland when produced. So in this post I’ll deviate a little from SQL Server to give you some HTML which will turn this:

SQL Table of Results in an HTML email
Produced: 03/10/2014 16:44:00
To be honest I couldn’t think of a good title for this particular post so I’ve gone with the above. Basically what I’m referring to is that sometimes a simple text email isn’t enough and you want to include some output with it… therefore what you need is a table.

SQL Column to Comma Separated List
Produced: 26/09/2014 17:57:00
This is a handy little “trick” which I use all the time when I need to turn the column of a table into a comma separated list. It’s incredibly quick, has very little overhead, and it’s effective.

sp_Merge
Produced: 19/09/2014 17:51:00
There are numerous times in which I, and many devs in the company, need to merge new data into existing tables and prior to the MERGE command this tended to be done in a procedure which created a “dev” table (table prefixed with “dev_”), executed multiple comparisons and joins, and then proceeded to perform an sp_rename to swap the dev table for the live one.

Different Query Results with row_number
Produced: 12/09/2014 18:30:00
Today I was asked a question that left me baffled for a good few minutes whilst trying to come up with an answer. Sadly my mistake all along was that I trusted a developer. Ah well, happens to everyone sometimes.

Query Tuning - Scalar Value Functions
Produced: 05/09/2014 18:16:00
Now, let me start by saying that if you have a scalar function which includes table access then you REALLY need to read this as they are bad... so, so bad that I never want you to use them. Seriously. These are horrible, horrible, horrible things when used in queries… please just abandon them now… there are better ways to achieve results.

Query Tuning - "not in"
Produced: 04/08/2014 17:58:00
This is something I’ve seen a lot recently and rarely has it been anywhere near the best way to achieve a result. In essence I’m talking about this type of query:

Using OPENROWSET instead of a Linked Server
Produced: 28/07/2014 17:30:00
Ever been writing some code and found that you need some data from another SQL Server instance? You have security access to the other instance so what do you do?

Well you could easily use a linked server to obtain it, but what if there is no linked server set up and you lack the rights to (or maybe there’s a company rule forbidding) setting one up?
Applying Policies to Multiple Servers
Produced: 21/07/2014 19:28:00
The good thing about a Policy is that you don’t have to have a copy on every single server to be able to enforce it. This makes Policy Based Management very appealing to DBAs who have a central server which they can use to create and hold policies to then run against multiple other instances on the network in order to bring them into line without having to create numerous different policies on numerous different sets of servers.

FULL Recovery Model - Policy Management
Produced: 14/07/2014 19:17:00
In most SQL Server environments with fast moving data we want to ensure that we will suffer the least amount of data loss in an outage or disaster. This means using the FULL recovery model. There are some circumstances in which someone might wish to change the database to a SIMPLE mode in order to, for example, shrink a runaway transaction log.

Email Alert from On Change Log Only
Produced: 07/07/2014 19:07:00
Having Policy Management set up is great… being able to schedule a job to start up, check your systems, and report anything that doesn’t fit your ideal is fantastic. But what about those policy breaches which cannot be automatically rolled back (On Change: Prevent is not an option) but for which you want to be immediately notified?