Execution Plans

When You Need to Tune A View, Don’t Just Get Its Plan

Say your database has a view, and everybody’s queries use it. Let’s take the Stack Overflow database and create this view: Transact-SQL CREATE OR ALTER VIEW dbo.vwQuestionsAndAnswers AS SELECT q.Title, q.Id AS QuestionId, a.Id AS AnswerId, a.Body AS Answer, uQuestioned.DisplayName AS Questioner_DisplayName, uAnswered.DisplayName AS Answerer_DisplayName, COUNT(DISTINCT vQ.Id) AS VotesOnQuestion, COUNT(DISTINCT uVotedQuestion.Id) AS UsersWhoVotedOnQuestion FROM dbo.Posts…
Read More

A Query That Should Be Contradicted

Innocent Enough I was writing another query, and became enamored with the fact that HAVING will accept IS NULL or IS NOT NULL as a predicate. What I ended up writing as an example was this query: Transact-SQL SELECT v.PostId, SUM(v.UserId) AS whatever FROM dbo.Votes AS v WHERE v.UserId IS NULL GROUP BY v.PostId HAVING…
Read More

A Quirk In Parallel Hash Join Plans

Bailing, Takes Me Away Both of these queries will return zero rows, in the 2010 version of the Stack Overflow database. Transact-SQL SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON v.UserId = p.OwnerUserId WHERE v.CreationDate >= '2011-01-01'; SELECT COUNT(*) AS records FROM dbo.Votes AS v JOIN dbo.Posts AS p ON…
Read More

A Surprising Simplification Limitation

When It Comes To Simplification Rob Farley has my favorite material on it. There’s an incredible amount of laziness ingenuity built into the optimizer to keep your servers from doing unnecessary work. That’s why I’d expect a query like this to throw away the join: Transact-SQL SELECT COUNT(u.Id) FROM dbo.Users AS u JOIN dbo.Users AS…
Read More
Menu
{"cart_token":"","hash":"","cart_data":""}