Script to Update Specific Stats
Produced: 05/02/2017 12:27:00
In my previous post I was stating that we need to be more intelligent with our stats updates rather than hitting a whole table with a sledgehammer and potentially suffering poor performance as a result.

However, wouldn’t it be great to be more proactive about the whole thing…

Statistics On Views Via Linked Server
Produced: 27/11/2016 17:51:00
As a follow-up to my last post about accessing synonyms via a linked server (you can’t), I stated that it was therefore better to use a view. However, that has issues of its own to consider.

Single Column Indexes Rarely Work
Produced: 06/11/2016 22:20:00
Again, following on from my previous posts about Key Lookups, I wanted to dispel a myth that I see used in production systems everywhere… that single column indexes are useful. In the majority of cases they just plain aren’t. Get used to it.

Performance Impact of a Key Lookup
Produced: 30/10/2016 22:16:00
Following on from my last post about Key Lookups I wanted to emphasize the performance problems that you can get when these appear in your execution plans.

What is a Key Lookup?
Produced: 23/10/2016 22:12:00
I was asked this the other day and it’s actually something that I’m surprised hasn’t come up before as people all seem to know it’s a bad thing, but many people don’t know why and don’t even know what one is in the first place.


The Awesomeness of Incremental Statistics
Produced: 14/08/2016 18:29:00
I did a post a while ago about why you need to be careful with Sampled Statistics and therefore why a FULLSCAN is really the only reliable way to go… BUT there has always been the problem that a FULLSCAN update can take a VERY long time.

Partition Elimination in Action
Produced: 10/07/2016 18:13:00
There are many reasons to use partitioning in order to make maintenance easier (stats and reindexing) and allowing for partition switching (fast loading and deleting of data), but there is another which is Partition Elimination.

Delayed Durability - What is it?
Produced: 22/05/2016 16:06:00
This is one of my favourite new features of SQL Server 2014 as it can make an absolutely huge difference to your servers when performing inserts, updates, and deletes.

And best of all, this is an ALL Edition feature rather than Enterprise only.

An Alternative to a Data Accessing Function
Produced: 20/03/2016 08:59:00
Once again I’m going to refer to previous posts here as this is actually an extension of my previous two posts.

This was something I came across recently when working on a tuning project… the original code was very slow and this was quickly traced to a good old function.

Better Row Estimates with Table Variables
Produced: 06/12/2015 12:54:00
In this episode of “Fun with Flags” we’ll be looking at TF 2453.

Having been recently playing with SQL Server 2014 I was intrigued to see the following in the list of fixes included for Cumulative Update 3 (note this is also included in SQL Server 2012 Service Pack 2)…

“FIX: Poor performance when you use table variables in SQL Server 2012 or SQL Server 2014”. As I’m not a complete fan of table variables (they’re good, but for limited uses), I was intrigued.

Execution Plans and XML Documents
Produced: 01/11/2015 09:49:00
Over the past few months I’ve been working with a lot of XML and stored procedures and it’s been a different experience, and not something I’d always refer to as “fun”.

But I have learnt a couple of things along the way which I’ve found interesting… mostly involving XML Documents and the Optimizer.

Unique Index with Union All
Produced: 18/10/2015 09:36:00
This follows on directly from the previous post in regard to ensuring that you use UNIQUE if possible on an index as I’ve also found that not having UNIQUE can make quite a hefty difference when using UNION ALL.

Create UNIQUE Clustered Index
Produced: 11/10/2015 10:55:00
This is a bug bear of mine but something which I see all over the place in people’s code… if you’re going to create a clustered index on a table (including temp tables) and the key you’re using is unique, then let SQL Server know. You’ll be surprised how much difference it makes.

Revealing Predicates in Execution Plans (TF 9130)
Produced: 27/09/2015 21:21:00
In this episode of Fun with Flags I'm going to discuss trace flag 9130. I found this flag when watching a video on performance tuning by Joe Sack… it’s something that I never knew existed, but that I’ve actually now used a few times because it turned out to be surprisingly handy.

Produced: 09/08/2015 05:14:00
The debate about INSERT INTO vs SELECT INTO can be quite long and get people quite heated and therefore I’m not going to go into the entire thing right now, but what I did want to mention was something that I encountered the other day and which could be worth considering next time you’re deciding which approach to take.

Vertical Partitioning Using sp_tableOption
Produced: 17/05/2015 09:48:00
This was an incredibly cool thing that I learnt the other week whilst attending a SQLSkills course. I have previously made mention of Vertical Partioning and how useful it can be, BUT it does have the downside of re-architecting… this can change that.

Part Populating Temp Tables
Produced: 01/02/2015 19:43:00
The other day I was going through some code looking for a reduction in reads (as they were rather higher than expected), and whilst doing so I came across a piece of code that seems logical yet is actually quite a performance killer.

Vertical Partitioning To Reduce Reads
Produced: 30/11/2014 18:51:00
Directly following on from my posts about Reads and the 8k Page, Vertical Partitioning your tables can suddenly make a lot more sense and it’s very quick and easy to see how vast improvements can be made to the speed of your queries and their impact on your disks.

What is a Read?
Produced: 16/11/2014 12:12:00
I’ve spoken to a good few people now who didn’t actually know what a Read was inside SQL Server. They were discussing tuning of queries and, correctly, knew that less reads were advantageous and therefore that’s what they were looking for in a better performing query, but didn’t really know why.

What Statistics Are Being Used
Produced: 07/09/2014 17:18:00
You’re running a query and you’re looking at the execution plan… it’s all well and good, there are tables, indexes, cardinality estimates… all manner of information. We know these are all derived from statistics and that our statistics should be kept up to date. But the question is… exactly which statistics were used or considered by the optimizer?

Basic Look At Statistics
Produced: 31/08/2014 11:42:00
People are always mentioning Statistics… “Keep your statistics up to date” etc. but, although in my last post I gave a good example as to why you really should keep them up to date, there aren’t that many places explaining what Statistics actually are. Therefore I figured I would give a quick overview.

Statistics Affecting Query Performance
Produced: 24/08/2014 11:36:00
Another way in which query performance can suffer is down to statistics. These are the numbers and mathematical information that SQL Server holds in regard to indexes and tables. By using these SQL Server estimates the number of rows a query will return at each stage of the execution plan.

Memory Grant Affecting Query Performance
Produced: 17/08/2014 11:31:00
My last post about how much difference caching can make to a query left me thinking of other reasons that can cause a query to hand you a seemingly random set of execution times. This is one of the reasons I came up with… memory grants.

What a Difference Caching Makes
Produced: 10/08/2014 11:17:00
I was visited the other day by an irate developer who was complaining that he couldn’t get consistent results from his testing and queries were running in anything from 5 to 30 seconds and he thought there was something wrong with SQL Server.

Scalar Value Functions and Parallelism
Produced: 22/06/2014 16:38:00
I’ve complained before about Scalar Value Functions and how much I dislike them but, in case you weren’t convinced the first time around, here’s another reason for you.

Scalar Value Functions stop the optimizer from using parallelism.

Local Variable Gotcha
Produced: 13/04/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.

Sadly there’s no “just use this” style of cure for this problem, but there are things that you can do to alleviate the impact this issue can have on your code.

Using Duration in Profiler and SSMS for Query Tuning
Produced: 06/04/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.

Query Tuning - Scalar Value Functions
Produced: 23/02/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.

This is a pet peeve of mine and I also know it’s the same with most DBAs who ever encounter this. In fact, not that I want to you go elsewhere, but a quick search on Google for “T-SQL Scalar Value Functions are evil” will return a plethora of results showing that it’s not just me who hates these abominations of the T-SQL language.

Query Tuning - "not in"
Produced: 16/02/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:

select *
from largerTable
where id not in
select largerTableID
from subsetTable