T-SQL Tuesday #68: Changing SSMS Keyboard Query Shortcut Defaults

Welcome everyone!

After being a long time read and occasional participant, I am honored and humbled to be your host for this month’s T-SQL Tuesday! In my Invitation to T-SQL Tuesday, I asked everyone to write a blog having something to do with “Defaults.”

For my contribution, I decided to blog a bit about SQL Server Management Studio. As a SQL Server Developer, I live my life within SSMS. As much as I like it, there are a variety of defaults that I like to change. And for today’s blog, I’m going to talk about Keyboard Query Shortcuts.

If you navigate to Tools -> Options -> Environment -> Keyboard -> Query Shortcuts, you will find that SSMS comes with a handful of defaults.

Tools -> Options

Query Shortcut Defaults

These three starting shortcuts are indeed very handy, but SSMS gives us a number of other blank entries to add our own. Wouldn’t it be a waste if we did not take advantage of them, to add in some of our own?

My Default Changes

As you can see above, here are ALL of the new Keyboard Query Shortcuts that I add. And here they are in full:

• Ctrl+3 = sp_help
• Ctrl+4 = sp_helptext
• Ctrl+5 = sp_SQLskills_SQL2008_helpindex
• Ctrl+6 = sp_SQLskills_SQL2012_helpindex
• Ctrl+7 = EXEC sp_whoisactive @get_plans = 2, @get_transaction_info = 1, @get_task_info = 2, @get_avg_time = 1, @get_outer_command= 1
• Ctrl+8 = EXEC tempdb.dbo.sp_help
• Ctrl+9 = EXEC sp_helpExpandView @ShowObjectCount = 1, @ViewName =
• Ctrl+10 = EXEC sp_whoisactive @get_plans = 2, @get_transaction_info = 1, @get_task_info = 2, @get_avg_time = 1, @get_outer_command= 1, @delta_interval = 10

Let’s explore how I make use of each of these!

sp_help & sp_helptext

I use these two SQL Server built-in stored procedures on an almost daily basis. They’re extremely useful while in the midst of code, to check aspects of objects. How do you make use of it? Simple! All you have to do is highlight the object in question, then hit your Keyboard Shortcut!

Using sp_help, I can quickly peruse all available columns in a given object, double-check their datatypes, or take a quick glance at what foreign keys and constraints are in place.

With sp_helptext, I can quickly scan the contents of a view or stored procedure, when I don’t want the hassle of finding it & script it out via the Object Explorer.

sp_SQLSkills_SQL20xx_helpindex

SQL Server comes with a built-in stored procedure: sp_helpindex. sp_help also returns index information about a table. However, they don’t return everything. When I’m query tuning and checking underlying indexes, I need to know about INCLUDE columns. That’s where Kimberly Tripp’s sp_helpindex rewrite comes in.

Used in the same fashion as sp_help & sp_helptext, I now have in-depth index information available at my fingertips!

sp_whoisactive

“Hey, why’s the server running slow right now?” Every time I hear that question, Adam Machanic’s sp_whoisactive is the very first thing I always run. If you don’t use this tool already, read more about it on Adam’s blog and install it NOW!

While you can run it without parameters, I like having some additional data points available to me, which require parameters. As you can see from the above command list, that’s a heck of a lot to memorize and re-type, so I’ve mapped that to Control-7.

tempdb.dbo.sp_help

If you’re like me and work with a temporary tables regularly, you’ll notice that the usual sp_help shortcut does not work. Not having sp_help information conveniently available for temporary tables is rather obnoxious, hence this particular Query Shortcut.

Executing sp_help against a temporary table, in a user database context, returns an error.

But with a modfication to execute from tempdb, we can get our sp_help data again!

Now I can have the same sp_help functionality and convenience with my temporary tables!

sp_helpExpandView

My final Query Shortcut executes my custom tool sp_helpExpandView. Used in the same way as sp_help, I can quickly reference underlying objects of a view. If you have to deal with untangling nested views, I would encourage you to check it out!

That’s it for my SQL Server Management Studio Keyboard Shortcuts. I hope you all found this insightful. If there are any Defaults that you like to change in SSMS, please feel free to share in the comments. Thanks for reading – until next time!

T-SQL Tuesday #66: Monitoring In Development Is Important Too!

Welcome back to another edition of T-SQL Tuesday. This month’s edition is hosted by my good friend Cathrine Wilhelmsen (b|t). Her topic of choice is “Monitoring.”

When the topic of monitoring arises, the focus almost universally shifts to Production environments. But as a developer, I would like to give Development environments some monitoring love as well.

Dev: “Monitor Dev? HAHAHA! Why do we care what’s happening in Dev?”

How many times have you been working on something and its performance is less than stellar? Perhaps you’re refactoring a legacy chunk of code and contrary to your expectations, your re-write is running dog slow. Or you’re working away in a shared Dev environment and suddenly queries that had taken mere seconds are now taking their own lunch break before coming back with resultsets?

Dev: “Okay, what should we watch for?”

Well, some of the metrics that are mission-critical to a Production environment may not have such a high priority in an Development environment. Perhaps you don’t care as much about about CPU spiking to 100%, because your Dev environment has fewer & less powerful CPUs than your Production hardware? Of course that hardware will be stressed more easily.

But here’s some things that you may still want to keep an eye on. How about blocking, especially in a shared environment? I’d really like to know that Fred’s new routine was written in a way that it unexpectedly takes exclusive table locks all over my database, and interfering with the rest of the development team. Better to find it causing trouble in Dev than Prod, right?

How about wait stats? If you’re working on a poorly performing chunk of code, and your Dev SQL Server is waiting on something, chances are good that your code will cause your Prod SQL Server to suffer some similar waits.

Dev: “Yeah, I can see how that kind of information can be useful. But how should we monitor?”

There are a wide variety of tools out there, both DIY and 3rd party vendor. Do you already have a product that you use to watch Production, that you could also use to watch Development? Talk to your DBAs about making use of it.

Want to roll your own solution? That’s easy too! Read up on how to baseline your SQL Server – Erin Stellato’s Baselines Series is a fantastic place to start! Leverage SQL Agent Jobs in conjuction with different diagnostic scripts like Glenn Berry’s Diagnostic Scripts, and save that data off to a table periodically for analysis.

Dev: “Huh, there’s some cool stuff in there!”

Absolutely! Be creative and do what makes sense for your enterprise and application.  Remember, the sooner you learn about a problem, the sooner you can deal with it before it becomes a fire – even in Development!

DIY Standing Desk

For several years, I’ve been reading about others who have started using standing desks. It has intrigued me and I’ve wanted to put together a standing setup of my own for some time now.  Researching various solutions out there, I was discouraged by the fact that most solutions cost $400 or more, particularly ones which could be converted from sitting to standing.  I knew there had to be a better way and thanks to Ikea & Ikea Hackers, I found my inspiration!

COMPONENT LIST

• Ikea Linnmon Table Top: $20
• Ikea Finnvard Trestle Legs: $35 (x2)
• 3M Adjustable Laptop Stand (LX550): $25
• Spare Samsung SyncMaster 226BW Montior: scavenged
• Stackable Wire Shoe Shelves: scavenged (x2)
• Stool: scavenged

So my total out of pocket cost? $115!

An hour of effort later… and voila, my new standing desk! So far it feels fantastic. Am writing this blog post from it. I went with a narrower 4 foot wide table top, so I could fit it into this particular corner of my guest bedroom, to preserve my window view. Being shorter, the height is perfect (about 38″) and I could raise it up an extra 1 1/4 inches if necessary. You could easily build this higher with some surplus 1×4″ wood, a drill, and saw. In fact, someone out there has even put out the plans for it AND even sell pre-made ones.

I must say I’m quite pleased and am looking forward to spending some quality time in this new workspace of mine!

UPDATE: Check out Part 2 here!
https://sqlbek.wordpress.com/2016/07/14/diy-standing-desk-part-2/

Learn Something New – SSMS Tips & Tricks

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!

---

CLIENT STATISTICS

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!!

---

SPLIT WINDOW

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.

Second half:

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!

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!