SQL Tweaks and Tools That Make My Life Easier

It still surprises me how many people don’t know about some of the very things that make my job so much easier. So this next post is dedicated to sharing some of the tweaks and tools I’ve run across that will help anyone who works with SQL:

 

Indexes
Anyone who uses included columns is probably well aware of the frustrations that can come from having to look up information on which columns are included. I wrote a stored procedure, dba_indexLookup_sp, to help me with this, before discovering sp_helpindex2. If you haven’t heard of sp_helpindex2, it’s a re-write of sp_helpindex by Kimberly Tripp. You can find it on Kimberly’s blog. The main difference is Kimberly’s is a system stored procedure (mine is not) and my version returns partitioning information (Kimberly’s does not). Check both out and use whichever one meets your needs best.

 

KeyBoard ShortCuts

In SQL Server Management Studio (SSMS), click on:
    Tools –> Options… –> Environment –> Keyboard

Keyboard Shortcuts

Keyboard Shortcuts

For your copying convenience:

Ctrl+3   Select Top 100 * From
Ctrl+4   sp_tables @table_owner = ‘dbo’
Ctrl+5   sp_columns
Ctrl+6   sp_stored_procedures @sp_owner = ‘dbo’
Ctrl+7   sp_spaceused
Ctrl+8   sp_helptext
Ctrl+9   dba_indexLookup_sp or sp_helpindex2

Please note that these settings will not take effect until you open a new query window. Here’s an example of how you could use this: use Ctrl+4 to find a list of tables, then copy one into your query window; to view a sample of that table’s data, highlight the table name (I usually double-click on it) and press Ctrl+3. It’s a thing of beauty. Oh, and you may want to remove/change the schema filters if you use schemas other than dbo.

 

Query Execution Settings

After having one too many issues arise from non-DBA’s connecting to the production environment to run a devastating ad hoc, I’ve had all of our developers and analysts adopt the following settings. The only thing difference between my setting and theirs is that I have “Set Statistics IO” selected. FYI – you can also make these same setting changes in Visual Studio.

In SQL Server Management Studio (SSMS), click on:
    Tools –> Options… –> Query Execution –> SQL Server –> Advanced

Query Execution Settings

Query Execution Settings

 

Copy Behavior
This next tip actually has nothing to do with SQL Server, and can be done with any Microsoft product. However, I just learned about it a few weeks ago and already I use it quite frequently.

Holding down “Alt” while you drag your mouse will change your selection behavior to block selection.

Block Selection

Block Selection

 

Please note: The following tools requires SQL 2008 Management Studio. These tools will also work when you connect SQL 2008 SSMS to a 2005 instance.

 

Object Detail Explorer

Finally, there’s a reason to use the Object Detail Explorer! My favorite use is to quickly find the table size and row counts of all the tables in a database. If these options are not currently available, you may just need to right click on the column headers and add it to the display.

Object Detail Explorer

Object Detail Explorer

 

Missing Indexes

And lastly, when using SSMS 2008 to execute Display Estimated Query Plan (Ctrl+L), it will show you if you’re missing any indexes. This will even work if you connect SSMS 2008 to SQL 2005!

Missing Index

Missing Index

That pretty much covers it for now. HTH! :)

Michelle

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , . Bookmark the permalink.

5 Responses to SQL Tweaks and Tools That Make My Life Easier

  1. Pingback: Log Buffer #126: a Carnival of the Vanities for DBAs

  2. Dean B. says:

    Nice! Thanks for posting this information! I appreciate it!

  3. Pingback: Another Managment Studio custom Keyboard shortcut post | Sankar Reddy, SQL Server Developer/DBA

  4. Seema Chaudhary says:

    I am having an error “Incorrect syntax near ‘FROM’.” while executing keyboard shortcut for ctrl+3 for ‘select * from ‘ statement. I selected the table name and then clicked ctrl+3 and given the error even though I opened the new query window after the settings done.

    Please tell me if some other setting need to be done.

  5. Akshay says:

    All are very relevant tricks. Thanks much!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>