Performance Tuning
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 q LEFT OUTER JOIN dbo.Posts a ON q.Id = a.ParentId LEFT OUTER JOIN dbo.Votes vQ ON q.Id = vQ.Id LEFT OUTER JOIN dbo.Users uVotedQuestion ON vq.UserId = uVotedQuestion.Id LEFT OUTER JOIN dbo.Users uQuestioned ON q.OwnerUserId = uQuestioned.Id LEFT OUTER JOIN dbo.Users uAnswered ON a.OwnerUserId = uAnswered.Id GROUP BY q.Title, q.Id, a.Id, a.Body, uQuestioned.DisplayName, uAnswered.DisplayName; GO |
Not pretty, but that’s why you’re here, right? Real world code is ugly.
And say my users are running queries like these:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT Title, QuestionId, AnswerId, Answer FROM dbo.vwQuestionsAndAnswers WHERE Questioner_DisplayName = 'Brent Ozar' ORDER BY Title; GO SELECT Title, UsersWhoVotedOnQuestion FROM dbo.vwQuestionsAndAnswers WHERE Answerer_DisplayName = 'sp_BlitzErik' ORDER BY Title; GO SELECT TOP 100 Title, QuestionId, AnswerId, Answer FROM dbo.vwQuestionsAndAnswers ORDER BY VotesOnQuestion DESC; GO |
Notice that the select, where, order by etc all have variations in them. They’re not all asking for the same fields, which means SQL Server can make different decisions about:
- Which tables in the joins are necessary
- Which indexes should be used on each table
- How much memory to allocate to each query
As a result, the execution plans all look totally different:
The thing those queries all have in common is the view, so sometimes folks say, “I need help tuning this view.” They run:
|
1 |
SELECT * FROM dbo.vwQuestionsAndAnswers; |
They get the execution plan, post it at DBA.StackExchange.com, and everybody tells them how terrible that view is. Thing is, that doesn’t really prove anything – because SQL Server uses the view in different ways. It’s just a starting point for the query’s execution plan.
When you need to tune a query that hits a view, don’t just investigate the view’s plan. Investigate your slow query’s plan, and you’ll get much more useful and targeted advice.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Yep… same thing for inline table valued functions as well, the plans need to be analyzed as a part of how they are called.
Yup, after all there’s no such thing as a “tuning a view” or a “view’s plan”. We gotta tune the queries and their plans.