Browse by Tags

Quite often, people have filtered indexes but find they’re not being used as often as they’d like. I was reminded of this recently when I read a good post by Kendra Little from brentozar.com about filtered indexes. In it, Kendra talks about how the WHERE clause of a filtered index allows an IN clause, but not an OR clause (to quote someone from ...

CONVERT_IMPLICIT isn’t the only problem with getting data types wrong. You might have the right type, but what if the length is wrong? This post will look at both getting the type wrong and getting the length wrong too.
Let’s do some testing. We’ll need a table with indexes. I’d normally use one of the AdventureWorks versions for this, but as ...

Infinitely better.
100% of the reads removed. Roughly 4000 (okay, 3890), down to zero.
None.
Let me explain...
Obviously if there’s data being returned, there are reads needed. And obviously there is some CPU needed for SQL to be given a query and do anything at all. Luckily for me, performance of a query is typically evaluated using the ...

It’s very easy to get in the habit of imagining the way that a query should work based on the Logical Order of query processing – the idea that the FROM clause gets evaluated first, followed by the WHERE clause, GROUP BY, and so on – finally ending with whatever is in the SELECT clause. We even get in the habit of creating indexes that focus on ...

Take a look at this query plan.
Yes, that arrow indicates a single row. This is an Index Seek, returning a single row. And yet it’s rubbish. That’s right – it’s rubbish!
In fact, I had to provide a hint for it to use this index. A table scan would’ve been better, and this is what happens without the index hint.
Let’s look at the query. I ...

My good friend Simon Sabin used the term ‘invertability’ on a Connect item he logged today.
Essentially, Simon’s noticed that there are lots of people that use year(someDate), but that the system doesn’t understand that this function doesn’t affect the order of the items in the index. month(someDate) does, but if you’re already using ...

No, this post has nothing to do with airport security, and nothing to do with marijuana.
Being honest, this post is only half about Hash Matches and Probe Residuals. It’s more about the types of predicates you can see mentioned in a SQL Server Execution Plan (or Query Plan, but I prefer to call them Execution Plans) – but you may well see some ...

I know that everyone misses this, because I’m yet to find someone who doesn’t have a bit of an epiphany when I describe this.
When tuning Data Flows in SQL Server Integration Services, people see the Data Flow as moving from the Source to the Destination, passing through a number of transformations. What people don’t consider is the Source, ...

When I’m at the PASS Summit in Seattle this week, I will need to remember that I can’t just step onto the road if I’m walking along the footpath on the right-hand side. In the UK and Australia, where we drive the correct side, it’s fine (just don’t tell my kids), because the cars that are nearest me are coming towards me – except of course, the ...

No really – hear me out.
Of course you create tables, and you query tables, and we say that data is stored in tables. The table is (rightly) a fundamental part of relational theory. But I find that when I think about queries and how they run, I need to approach the system thinking about the indexes that I’m querying, not the tables.
When you ...