The New Lightweight Query Plan Profile Hint

Recent Updates To SQL Server 2016 and 2017 introduced a new USE HINT that lets you direct an actual execution plan to a new Extended Event, called query_plan_profile. The hint by itself doesn’t do anything, and the XE by itself doesn’t do anything. You need to have both. Alright then, let’s do that. Sessions Assuming you’re on…
Read More

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

What’s New in SQL Server 2019: Faster Table Variables (And New Parameter Sniffing Issues)

For over a decade, SQL Server’s handling of table variables has been legendarily bad. I’ve long used this Stack Overflow query from Sam Saffron to illustrate terrible cardinality estimation: Transact-SQL declare @VoteStats table (PostId int, up int, down int) insert @VoteStats select PostId, up = sum(case when VoteTypeId = 2 then 1 else 0 end), down…
Read More

Do You Have Tables In Your Tables?

This Isn’t A Trick Question Hopefully it’ll get you thinking about your tables, and how they’re designed. One of the most consistent problems I see with clients is around wide tables. I don’t mean data types, I mean the number of columns. Going back to Michael Swart’s 10% Rule, if your tables have > 100…
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