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
New Class Week: Developer’s Guide to SQL Server Performance Tuning
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":""}