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:

Not pretty, but that’s why you’re here, right? Real world code is ugly.

And say my users are running queries like these:

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:

Totally different query plans

The thing those queries all have in common is the view, so sometimes folks say, “I need help tuning this view.” They run:

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.

Previous Post
What’s New in SQL Server 2019 System Tables
Next Post
The New Lightweight Query Plan Profile Hint

2 Comments. Leave new

  • Yep… same thing for inline table valued functions as well, the plans need to be analyzed as a part of how they are called.

    Reply
  • Alex Friedman
    October 4, 2018 1:35 am

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}