Development

Way Back When I blogged about the new USE HINT that logs query plans that use it to a new XE session. The use hint and session both share the same name — query_plan_profile. There are currently some rather unfortunate limitations to the event in the GUI. Right now, there’s no query plan tab so you…

The preview of the next version of SQL Server Management Studio is out, and it has a radical improvement to query plans that will shock and amaze you. It’s best to just show you: Estimated vs actual rows You can see the estimated and actual number of rows right there on the query plan just…

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…

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…

For years, when you right-clicked on a database and click Properties, the “Compatibility Level” dropdown was like that light switch in the hallway: You would flip it back and forth, and you didn’t really understand what it was doing. Lights didn’t go on and off. So after flipping it back and forth a few times,…