Today’s post is not only for the SQLNewBlogger Challenge, but is serving double duty as my T-SQL Tuesday contribution! This month’s T-SQL Tuesday is being sponsored by Mike Donnelly (b|t) and he has asked us to “Teach Me Something New.”
This past weekend, I participated in my third SQL Saturday Madison event. Aside from speaking, I attended some sessions and guest-hosted the SSMS Showdown between Jes Borland (b|t) & Russ Thomas (b|t). Jes & Russ had a fantastic showdown of a bunch of neat SSMS related tips and tricks. Here’s some of the new things I learned about!
OBJECT EXPLORER FILTERS
This is a feature that I thought was rather neat. I never really noticed the little filter icon – it just sort of blended in I guess. But it is fantastic if you have a database with hundreds or thousands of objects, perhaps that is not sorted or named very well.
If you select a category folder, like Tables, the Filter icon becomes enabled. Clicking that will bring up a window which gives you a number of different options to filter on.
Next add some keywords. I added Phone and Person and voila, my Tables now show as being filtered and only the tables that matched now appear in Object Explorer!
The next feature I (re)learned about is Client Statistics. I always seem to forget that this exists, but it is a very insightful feature for performance tuning.
Turning it on is simple. You can access it from Query Menu, a keyboard shortcut, the toolbar.
Then I ran a query. I varied and re-ran my test query 4 more times. Now you can see from the next screen shot that different data points about my last 5 executions were saved. Client Statistics will save the 10 most recent queries you’ve ran. It will also average them in the final column.
This is extremely useful to see how much work your queries are really doing, and as you’re tweaking and tuning, how each iteration may differ! Great stuff!!
Ever find yourself looking at a long query or stored procedure, and need to reference something at the top along with something toward the bottom? Isn’t it a pain to scroll back and forth? Well in SSMS, it seems you can split an existing query window!
I’ve opened a stored procedure from AdventureWorks, that has a CTE of a UNION of two queries. Let’s say I am trying to troubleshoot something and want to compare the output column declaration list to the final output of my final query.
Instead of scrolling back and forth, try using the handy split screen functionality!
Click & hold the split screen icon on the upper right, and drag down.
Voila! Your window is now split in two, and you can now scroll to independently and have two different views of your query!
Well I hope you enjoyed these SSMS Tips & Tricks. Special thanks to Jes & Russ again, for inviting me to guest-host their session and teaching me these nifty tidbits!
About one year ago, I made my speaking debut at SQL Saturday Madison 287. And this past weekend, I had the pleasure of returning for SQL Saturday Madison 387. In the course of that year, I’ve had the pleasure and honor of speaking at several SQL Saturdays, User Group Meetings, and a Virtual Chapter Meeting. Becoming a speaker is one of the best decisions I ever made.
A year ago, I had maybe 15 people attend my session. This year, I packed the room with over 40 attendees! A year ago, I was a ball of nerves. This year, I’m far more comfortable and have refined my presentation skills. I know I have much more room for growth but am very pleased with the growth I’ve experienced this past year.
I cannot say enough positive things about the Madison planning team. They managed to find a new venue, work around the SQL Saturday website being offline for the final week before their event, and pull off a spectacular event. I spoke to numerous attendees who all enjoyed their experience tremendously!
On a personal note, it was awesome to meet new some folks who I only knew via Twitter. I remember last year, at my first Speaker Dinner, I was a bit nervous since I hardly knew anyone. This year, many of my fellow speakers were now my friends! I also had the pleasure of meeting a number of new people throughout the day. Had a lot of fun sharing knowledge, answering questions, and just simply talking shop. Look forward to seeing those faces again at future events!
Both SQL Saturday Madison’s also happened to coincide with two other unhappy events. Last year at SQL Saturday Madison, I found out that my SQLFamily colleague Brooke Ranne had passed away suddenly. She was the Chapter Leader of the Chicago BI User Group & a fellow staff member of the SQL Saturday Chicago planning committee. I learned about her passing in the middle of the morning, scrambled to make some phone calls, and it definitely put a damper on the day.
This year, last week, SQLFamily lost another – Larry Toothman. The week leading up to SQL Saturday Madison was rough, as we in SQLFamily struggled with the news of his passing and coming together to try and support his family however we could. I remember meeting him at a SQLSkills IE event in Chicago, then crossing paths with him on a number of occasions thereafter. After seeing him at Summit 2014, I was looking forward to seeing him again in both Madison and in May at another IE event in Chicago. The Madison team took a moment in both the opening and closing ceremony, to honor Larry and his memory.
As sobering as these two events are, they are important reminders of what is most important in life. It further reinforces what SQLFamily means to me and many others. This is why I try to give back to SQLFamily now as a speaker, Chapter Leader, mentor, and in whatever capacity comes my way.
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.
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!
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
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!
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.’
@ViewName = ‘[schema].[view]’,
@OutputFormat = ‘[all|vertical|horizontal]’
Download Here V2 released 2015-03-03
- 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.
Five weeks ago, my good friend Gina Meronek contacted me about MADPASS. Seems their scheduled speaker for February had to cancel, so they were trying to find someone to fill the spot. I only had my first and only presentation, Every Byte Counts, in my portfolio, and presented it to MADPASS last August. Buuuuutttttttt… I’ve been wanting to add a second presentation to my portfolio. I’ve had the primary idea brewing for several months but just had not gotten around to finally doing it. Well, nothing motivates like a hard deadline!
Flash forward to last night, where I debuted Cleaning House: Indexing Edition. I was pleasantly surprised that despite the lousy February weather, a couple of dozen people made their way out. Another dozen or so joined the online broadcast too! And from the questions & feedback I got, I think the session was a resounding success! I already have some ideas on tweaking content, and definitely must focus on time management, but all in all, I’m extremely pleased.
Many folks asked for my slides & demo scripts, which I’ve just finished bundling up for distribution. You can get them here.
Be sure to check the Readme.txt file, which has links to 2008 & 2014 .bak files of my demo database.
Thank you again MADPASS, for having me back, and thank you to everyone who came out, sat through my presentation, gave me great feedback, and hopefully learned something new and practical!
About a month ago, I learned that I was nominated for the 2014 Tribal Awards held by SQL Server Central & Simple-Talk. These are fun awards, whose nominees & results are determined by the SQL Server community at large.
I remember sipping my coffee before work, skimming Twitter, and seeing Jen Stirrup (b|t) tweet that she’d been nominated for a category. I thought “oh cool, I ought to check out the full list later today.” Eventually I pulled up the nomination listing and nearly spat out my coffee. My name was listed under the Best New Community Voice category!
Before I continue the story – THANK YOU to those who nominated me. It still humbles me that someone even thought to list my name and raise me up as a nominee.
Yesterday, the results of the community voting were announced. I was delighted to learn that my good friend Catherine Wilhelmsen (b|t) won our category. I still remember meeting her at Summit 2013 and have loved watching how she’s exploded within the SQL Server community in the year thereafter. I was thrilled for her – she’s definitely a rising rockstar in the community.
Then Simple-Talk surprised me with a tweet…
So THANK YOU to the SQL Server community at large, for not only everything you’ve given to me, but for inspiring me to give back as much as I can and to continue doing so!