Latest Posts

Parameter sniffing, pt 2

A while back I wrote about parameter sniffing, the situation where SQL compiles and caches an execution plan that is appropriate for a certain value of a parameter, but is non optimal for other values. There’s another side to parameter sniffing though – when the optimiser can’t sniff at all.

When a batch is submitted to the optimiser, the value of any parameters (from stored procedure, auto parametrisation or sp_executesql) are known to the optimiser. So are the value of any constants used in the SQL statement. The value of local variables, however, are not.

When the value of a variable used in the where clause is not known, the optimiser has know idea what value to use to estimate the number of affected rows. Hence, it has no idea how many rows from the table will satisfy the condition.

(more…)

Status report

Just a very quick post, so no one thinks I fell off the edge of the planet.

This blog just passed 2000 hits today. Considering the 1000 mark was passed just over a month ago, that’s really, really good. I’m also up to 15 rss readers, which is quite encouraging

On the downside, I’m swamped with real work, work for university,  prep for my roleplaying game and an assortment of other stuff. Hopefully there’ll be a post up by end of week.

RIP Ken Henderson

I just ran across the news now. Oh my.

I attended a presentation that he did at the Pass conference in Denver last year. I was struck by his passion and humility. I have two of his books, and they are for me a first point of reference for any problem.

He will be sorely missed.

Edit: Just ran across a very nice tribute to Ken.

Ken Henderson

Execution plan operations – aggregates

On to aggregates.

I’m not going to dwell too long on the logical operators for aggregates, as they aren’t as interesting as the joins were. Rather I’m going to concentrate on the physical operators involved.

The two physical operators used for aggregates (and a couple of other logical operations) are:

  • Stream Aggregate
  • Hash Aggregate

Stream Aggregate

The stream aggregate is the faster and more efficient of the aggregate operators. For it to be used, the input rowset must be sorted in order of the grouping columns.

(more…)

Comparisons with NULL

Or ‘True, False or FileNotFound’ (1)

Null seems to confuse a lot of people. The main issue with null is that it is not a value. It’s the absence of a value, a place holder for unknown data. Hence, when it is used in a comparison, it returns results that look surprising. Take this as a quick example

DECLARE @Var1 INT
SET @Var1 = NULL

SELECT
CASE WHEN @Var1 = NULL THEN ‘Var1 = Null’ ELSE ‘Var1 <> NULL’ END AS EqualityTest,
CASE WHEN @Var1 <> NULL THEN ‘Var1 <> Null’ ELSE ‘Var1 = NULL’ END AS InequalityTest

The results look a little strange. They’re supposed to. (No, I didn’t mean IS NULL/IS NOT NULL)

(more…)

SQL 2008 training

I’ve run into a bunch of free SQL Server 2008 training over the last few days.

Becoming a better … – lookback

I thought I’d take a quick look back over the last two months, see how I faired on what I set out to do.

  • Finish 2 database books by the end of the year – I didn’t manage to finish either, though I started both the ones I said I’d read, and a third one. Part of the problem is that I was studying for an exam until mid december
  • Write at least 1 blog post a week here – Looking back I see that I wrote 8 posts in 8 weeks, however one was decidedly not SQL related and a second was just a comment on an exam. Not too bad.

Now for the next 6 months.

  • Read and finish one database book every 2 months. I don’t think I’m going to be able to do more than this, especially since I have a thesis to write.
  • Read at least one C# book
  • One blog post a week
  • One SQL or development related podcast a week.

We’ll see in 6 months how well this goes.

Execution plan operations – joins

It’s about time I picked this series up again.

I’m not going to go into too much detail on joins. There are some very good articles elsewhere on joins. The important thing to notice about joins, in the context of an execution plan, is that there are six logical join operators and three physical join operators. The logical operators are what you ask for in the context of the query, the physical operators are what the optimiser picks to do the join.

The six logical operators are:

  • Inner Join
  • Outer Join
  • Cross Join
  • Cross Apply (new in SQL 2005)
  • Semi-Join
  • Anti Semi-Join

Craig Freedman wrote a long article on the logical join operators – Introduction to Joins

The semi-joins are the exception, in that they cannot be specified in a query. Nonetheless, they are present in disguise. They’re the logical operators for EXISTS, IN, NOT EXISTS and NOT IN. They’re used when matching is required, but not a complete join.

(more…)