Learn Something New – SSMS Tips & Tricks

TSQL2sDay150x150

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.

filters-1

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.

filters-2

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.

client-stats-1

Turning it on is simple. You can access it from Query Menu, a keyboard shortcut, the toolbar.

client-stats-2

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.

split-1

Second half:

split-2

Instead of scrolling back and forth, try using the handy split screen functionality!

split-3

Click & hold the split screen icon on the upper right, and drag down.

split-4

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!

Advertisements

2 thoughts on “Learn Something New – SSMS Tips & Tricks

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 )

Google+ photo

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

Connecting to %s