Query Tuning

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.

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.

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.

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.

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.

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.


Oh, and yes… this is also to do with partitioning!!!

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.

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.

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.

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)…

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”.

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.

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.

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.

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.

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.

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.

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.

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.

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?


This is where these trace flags come into their own… they will give you just that information.

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.

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.

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.

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.

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.

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.

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.

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 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: