I got a question from a reader of my post How the Query Optimizer Uses Statistics (http://sqlblog.com/blogs/ben_nevarez/archive/2009/09/03/how-the-query-optimizer-uses-statistics.aspx) and I thought that it would be a good idea to post my answer here. Basically the request was to expand the previous example for a predicate with two columns. ...

In case you are not aware, the excellent white paper “Statistics Used by the Query Optimizer in SQL Server” has been updated for SQL Server 2008.   You can find it here   Statistics Used by the Query Optimizer in SQL Server 2008 Writer: Eric N. Hanson and Yavor Angelov Contributor: Lubor ...

  This post shows how the Query Optimizer uses statistics to estimate the selectivity of expressions during query optimization.   You can also use this as a second part of my last post, The Query Optimizer and Parameter Sniffing. Here I will show you the advantage of the use of statistics when the Query Optimizer can “sniff” the ...

  As we all know, the SQL Server Query Optimizer uses statistics to estimate the cardinality and selectivity of predicates of a query to produce an optimal execution plan. The Query Optimizer accomplishes this by first inspecting the values of the query parameters. This behavior is called parameter sniffing and it is a good thing. Getting an ...

Lies damned lies and statistics - Part II In previous post I listed situations where your statistics might need some special attention (http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx). As I mentioned in that post, the symptoms are typically related to more or less random variations in ...

In my previous post I talked about contradiction detection as one of the early steps in query optimization. Another interesting step performed during query optimization is the automatic matching of computed columns. Although computed columns have been available in previous versions of SQL Server, the automatic matching feature was introduced until ...

I'm a day early; sorry.  But I have a lot of interesting items to share this week. ======================================== More control over TOP (n) in DML This week I realized that the workarounds for affecting which row(s) are impacted by UPDATE/DELETE TOP (n) were cumbersome, and so I asked for an extension of TOP that allows you to ...

I missed last week because I was having fun up in Canada... mostly without any kind of computer access at all.  It was a nice break, but now I'm back in the thick of things again.  So this week, I am going to try to beef it up a bit to compensate for last week's missing entry. =================================== DELETE vs. DROP I ...

According to this paper from the Proceedings of the National Academy of the Sciences (PNAS), social security numbers (SSNs) are pretty easy for hackers, identity thieves, and other miscreants to predict based on publicly available data. I found this interesting partly because I just recently (a few months ago) wrote a chapter for a book ...

Here are the Connect items I'd like to draw your attention to this week.  I have abandoned the idea of trying to keep track of vote counts and overall rating.  Being lazy might be a small part of it, but it is mostly because I just don't think it makes sense to assume that my postings are all that influence Connect behavior.  I know ...