Free Community Tools: sp_helpExpandView

A few months ago, Brent Ozar called for bloggers to write about about Free Community Tools in the month of September. While it’s been a while since I’ve done any work with it, I’d like to offer up sp_helpExpandView.

Does Anyone Actually LIKE Nested Views?

A number of years back, I joined a company that happened to LOVE nested views. And they also happened to have an ETL/DW loading process that literally took SEVEN DAYS to complete.

“Wow Andy, SEVEN DAYS?!? That DW must have been several terabytes! (circa 2015)”

“Uhh, no… less than 250GB at most?”

Yeah, that thing was small. But the processing was a horrific case study in T-SQL worst practices. And the architect that created it LOVED nested views (and scalar functions… and MERGE… on Standard edition).

I spent a good amount of effort trying to unravel those as part of my efforts to improve performance, and as a result, decided to create my own community tool to help with unraveling them – sp_helpExpandView.

Andy – Why the Nested View Hate?

In a nutshell, after a number of layers of nesting, cardinality estimates in nested view operators go off a cliff. And cardinality estimates play a critical role in execution plan quality, which is why it is important to have good estimates. If you’ve dug deeper into your nested view performance, you’ll see exactly what I mean.

Want to dive deeper into that topic? Then check out my Exploring Why UDFs and Nested Views Hinder the Query Optimizer presentation.

Documentation For sp_helpExpandView?

I’ve written a few blog posts about sp_helpExpandView that you can read, which I’d strongly suggest that you do.

What About Nested Stored Procs?

Secret super-tip… sp_helpExpandView also works with Stored Procedures!

Go Forth and Unravel

I do sincerely hope that this tool helps you unravel your nested views. Sure wish there was a magic tool to rewrite T-SQL to just stop using them, but unfortunately that still requires human intervention. Hopefully you’re a consultant and can get paid by the hour for this work!

Thanks for reading!

Advertisement

2 thoughts on “Free Community Tools: sp_helpExpandView

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.