Some underappreciated productivity features of SSMS


As we look forward to Project Juneau; I thought of quickly writing about some features of it’s predecessor – the SQL Server Management Studio (well-known as SSMS). SSMS had power-packed features available at a single click of the mouse – which we might be using in our daily work.

None of what I am about to mention is new, it’s just that they are some great productivity enhancing methods of doing the same thing. I hope that you like these tips, and adopt them in your day-to-day encounters with SSMS.

Trace Query in SQL Server Profiler

When working on a performance tuning exercise, the SQL Server Profiler is one of the most used tools. Assume that we have a query all ready for execution in SSMS and need to launch a new instance of the SQL Profiler. There’s no need to navigate out of the Query Editor window! The context menu has a direct link to launch the profiler! What’s better is that launching Profiler using this option logs in to the server automatically, and starts off your default trace!

Once the profiler is launched and the default trace started off (automatically!), all we need to do is run the query after coming back to SSMS.

image image

Query Options

We may want to run a SQL query with multiple different execution settings, and do not really want to use the keyboard to type in the options with their proper syntax, the context menu on the query editor is there for our rescue.

In the screen-shots below, I have demonstrated how simply using the Query options window takes effect:

image image

Because setting the query options is only local to the particular session, opening up a new SSMS window and running the query there will confirm that changes are not persisted.

Other “cool” features

In addition to the two features I wrote about today, you can also do the following directly from the Query Editor window itself:

  1. Change the active connection
  2. Display Estimated and Actual execution plans
  3. Launch the Database engine tuning advisor
  4. Include Client statistics – (great introduction to client statistics is from Mr. Pinal Dave’s (blog) post – http://blog.sqlauthority.com/2009/10/01/sql-server-sql-server-management-studio-and-client-statistics/)

If you want to know more about SQL 11 (“Denali”)

If you want to know more about SQL 11 (“Denali”), a good place to start will be the “What’s new” listing available on this very web-site! You can reach this list right here – http://beyondrelational.com/whatisnew/sqlserver/denali.aspx. Simply click on the feature you want to explore and if one of us from the community has written about it, chances are, it is linked here.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

One thought on “Some underappreciated productivity features of SSMS

  1. Pingback: SSMS – Query Result configuration – Generate comma separated output without BCP or query changes | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

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