How Am I Using sp_helpExpandView? #SQLNewBlogger Challenge

Welcome to my first blog post of my good friend Ed Leighton-Dick’s (b|t) #SQLNewBlogger Challenge! A few weeks ago, he challenged new #SQLFamily bloggers to commit to writing 4 blog posts in April – 1 per week. The response has been tremendous and I am excited to read everyone’s posts.

Today, I’m going to write about how I’ve been using my sp_helpExpandView tool. I joined a new company in 2015, and their environment has many nested views. These views are a significant contributing factor to their performance issues.  In order to address these performance issues, I am spearheading a project to consolidate nested view code.  The first key step is untangling each view.

Why Are Nested Views Bad?

In brief, heavy usage of nested views will force the Query Optimizer to work through even more steps when creating a query execution plan. Each query within each nested view must be parsed. Keep in mind that when generating plans, the Query Optimizer only has a limited amount of time. It will not generate the “best possible” plan for your query, but a “good enough” plan. And often times that “good enough” plan is still a poor execution plan.

Imagine how much work SQL Server must do to put together a plan consisting of 5 JOIN operations. Now what if each of those objects were nested views, which contained their own set of JOINs. And underneath those, more JOINs. It can go on and on and on.

How Can sp_helpExpandView Help Me?

This is where sp_helpExpandView comes into play. I wrote it to aid in identifying every single object that a particular view makes reference to. My environment has views that are nested 7 levels deep. Another colleague who tried my tool shared that he was de-tangling a nested view that has 31 JOINs, plus sub-queries and UDFs!!!

When you run sp_helpExpandView against a view, with horizontal output, you can quickly see how many layers of nesting your view contains. You’ll be able to identify all objects that are referenced.

Another thing to look for is whether any tables are referenced multiple times, due to being referenced in different sub-views. The Query Optimizer may not be able to consolidate the repeated references, which could result in an execution plan that repeatedly references the same object unnecessarily.

Can I Check My Entire Database’s Codebase At Once?

sp_helpExpandView works great against a single view, but what if you are not quite sure where to start? Maybe your database has dozens or even a couple hundred views?

To help tackle that, I’ve written a companion script that executes sp_helpExpandView against all views on a database.

Tool – sp_helpExpandView All Views

This script saves each individual view’s output into a single table, then dynamically pivots the entire resultset to present it in horizontal format. I then export the entire resultset into Excel, which allows me to quickly skim, filter, and analyze my entire database with ease!

How Did This Help Me?

When I ran my companion script in Dev, I found that 2/3’s of our views were nested views!  I was able to catalogue and prioritize which views’ complexity based on number of levels and number of objects referenced.  Visually showing this output to my developers also helped them to realize how much work SQL Server was being forced to do.  Because all of the other objects were abstracted away in the nested views, my developers didn’t fully realize and appreciate all of the objects they were querying when referencing one of these views.

Call To Action!

Does your environment contain nested views? Do you even know? Try out sp_helpExpandView and run the companion script (not in Production please). The results may surprise you!

Don’t Forget Everyone Else!

Be sure to read other posts by other #SQLNewBlogger participants!  Use this Twitter Search to help you find them quickly!

Advertisement

sp_helpExpandView – Well I didn’t expect that…

So a few minutes ago, I had a random thought.  sys.dm_sql_referenced_entities(), which is the fundamental basis of sp_helpExpandView, works on an assortment of objects, not just views.  Hmmm… I wonder… let’s run it against a stored procedure?  BOOM!  I had a list of all objects referenced in that proc!!!  And of course, all of the views that were referenced were also expanded out.

Well that’s nifty!  You can use sp_helpExpandView to quickly assess objects referenced by a stored procedure!

Yep – I planned that feature… sure did!  Hah!

Check out the latest version here:  sp_helpExpandView

Debuting sp_helpExpandView

 

Debuting sp_helpExpandView

Who here hates nested views? I sure do. And whenever I find them used in code, untangling them can be a behemoth of a task.

Having faced them one too many times, I finally became fed up. I thought to myself, that there has to be a better way than opening up each object, checking each table reference, and crawling further and further down the rabbit hole. SQL Server must have functionality buried within it that would allow me to programmatically dissect and untangle a nested view.

So I started writing a script against system views. But then I ran into some roadblocks. As I researched those, I discovered the DMF sys.dm_sql_referenced_entities(). And thanks to that wonderful DMF all of those problems went away.

The result is a script tool that I am calling sp_helpExpandView!


TL;DR

sp_helpExpandView will take a view & return a list of all objects referenced underneath. V1 supports references to child views, tables, inline functions, table valued functions, and 1 level of synonyms.

Output comes in two formats – vertical and horizontal.  Default is ‘all.’

Usage:
EXEC sp_helpExpandView
@ViewName = ‘[schema].[view]’,
@OutputFormat = ‘[all|vertical|horizontal]’

Download Latest at GitHub – Update Available: 2016-05-05

Known Issues:

  • Synonyms against objects not fully qualified with 3 part names will not be correctly processed.
  • Does not process 3 part names correctly. ex: ([db].[schema].[object])
  • If no schema specified, does not default dbo. schema.
  • Error in output if 3 part name used in underlying code & no schema specified. ex: ([db]..[object])

I hope people find this helpful.  I would love to hear any suggestions or feedback you may have.

Special thanks to Ben Thul (b|t), who mentioned PARSENAME() to me on Twitter. I’d forgotten that function existed & it helped clean some things up.