Fixing Hot-Key issue in SSMS in five steps.

A lot of people in the Internet complain about their version of SSMS "forgot" some hot-key combinations. The oldest complain I remember was about the most useful combination "Ctrl-R".

The reason why SSMS "forgets" is within code sharing and reusability with other Microsoft development products.
If you have that problem, most probably I have (or had in the past) installed something else from Microsoft, such as Development Studio, Data Tools etc.

Won't concentrate on that, just provide the solution:

At first, got to the "Options":
- Menu -> Tools -> Options

- Or just simply press Alt+T -> O

Then Open "Environment" -> "Keyboard" -> "Keyboard".

Click to the text box on lower right called: "Press shortcut keys" and press "Ctrl+R"

In the correct environment you should see something like this:

In the drop down box "Shortcut currently used by:" you should see an option "Windows.ShowResultsPane (Ctrl + R (SQL Query Editor))"

If you do not have it - that is your problem.

For the sake of a Demo, I'll remove it in my SSMS and will fix it step-by-step.

Here is the Fix:

Step 1.

Go to Menu -> Tools -> Options -> Environment -> Keyboard -> Keyboard

Step 2.

In the Left-Middle ListBox search for a value "Windows.ShowResultsPane" and select it.
You should get something like this:

Step 3.

In drop down box "Use new shortcut in:" select "SQL Query Editor" and in "Press shortcut keys" text box press "Ctrl+R" to get something like this:

Step 4.

Press "Assign" button.
By doing this you assign key combination "Ctrl+R" to "Windows.ShowResultsPane" event within "SQL Query Editor" environment.
As the result, you have to get a new line in "Shortcuts for selected Command:" drop down box: "Ctrl+R (SQL Query Editor)".

Step 5.

Press OK and test how "Ctrl+R" works.


It looks easy, isn't it? The complication is only in the fact that Configuration options in SQL Server Management Studio show and allow to change shortcut keys not only for SSMS, but for other applications too.

If you have a problem with other unassigned hot keys the biggest problem is to identify "command" to assign for the key you need.

Here is a small list of commands for the most useful keys:

Shortcut Key	Command
Ctrl + T	Query.ResultstoText
Ctrl + D	Query.ResultstoGrid
Ctrl + Shift + F	Query.ResultstoFile
Ctrl + N	File.NewQuery
Ctrl + S	File.SaveSelectedItems
Ctrl + O	File.OpenFile
Ctrl + F	Edit.Find
Ctrl + H	Edit.Replace
Ctrl + Z	Edit.Undo
Ctrl + Y	Edit.Redo
Ctrl + A	Edit.SelectAll
Ctrl + C	Edit.Copy
Ctrl + V	Edit.Paste
Ctrl + X	Edit.Cut
Ctrl + Shift + U	Edit.MakeUppercase
Ctrl + Shift + L	Edit.MakeLowercase
Ctrl + K, Ctrl + C	Edit.CommentSelection
Ctrl + K, Ctrl + U	Edit.UncommentSelection
Ctrl + L	Query.DisplayEstimatedExecutionPlan
Ctrl + M	Query.IncludeActualExecutionPlan
Ctrl + J	Edit.ListMembers

SSMS 2016 Newest features

Here the list of newest features I've found:

1. Scroll bar indicates current position of a cursor within a document and indicate positions of any syntax errors:

2.Estimated Execution plan is now available from the Keyboard. You do not have to move your mouse to a Tool bar every time you want to see it, Just press Ctrl-L. Extremely useful.

3. In SSMS 2016 you can see execution in action. You just have to enable "Include live query statistics" in a Tool bar:

Then, when you run a query, execution will be shown in action:

4. Now you can easily compare query plan to previously saved plan! Just do right click on your plan, load saved plan and enjoy.

5. If you are lost in SSMS you can use "Quick Launch" at the top of SSMS window and do operation you want:

And the biggest of all....

I even haven't numbered it, because you probably already know it:

SQL Server Management Studio is now distributed separately for m the SQL Server.
The installation contains multiple other products, such as "Configuration Manager", which is actually useless, because it does not allow you to connect to the remote server.

SSMS Query Shortcuts

I've recently made a presentation for that topic and decided to post some of it's materials here.

*That post is going to improve your working experience with SSMS.

Native SSMS Functional Shortcut Keys

If you work with SQL Server for a long time you've probably learn some Keyboard combinations to speed up your administration or development process.
The full list of SSMS Shortcut keys you can find in MSDN

I will try to re-categorize the most interesting ones:

The most popular are:
F5 - Execute selected query or All queries within the current Tab;
Ctrl-R - Show/Hide Result Panel
Ctrl-Tab - Switch between tabs;

Windows wide ones:
Ctrl-N - Create new Tab;
Ctrl-S - Save Current tab to a file;
Ctrl-O - Open File or Project;
Ctrl-F / Ctrl-H - Find and Replace text within a Document, a Project or in all open Tabs;
Ctrl-Z / Ctrl-Y - Undo/Redo;
Ctrl-A - Select All;
Ctrl-C/Ctrl-V/Ctrl-X - Copy/Paste/Cut;

Less popular, popular but very handy:
Ctrl-Shift-U / Ctrl-Shift-L - Change selected text to upper case or lower case;
Ctrl-K+Ctrl-C / Ctrl-K+Ctrl-U - Comment/Un-comment selected text;
Alt-Break - Cancel Query execution;
Ctrl-L - Generate Estimated Query Execution Plan;
Ctrl-M - Turn ON functionality to Produce Actual Query Execution Plan;
Ctrl-T / Ctrl-Shift-F / Ctrl-D - Switch between output to Text, File or Grid;
Ctrl-J - List Members from IntelliSense;
Ctrl-F5 - Parse selected Query or All queries within the current Tab;
Ctrl-F6 / Ctrl-Shift-F6 - Cycle between Code, Grid Result and Messages;

Undocumented/Incorrectly Documented:
Ctrl-U - Brings active cursor to database selection;

If you fill like I missed something interesting, please let me know it to include in the list.

Native (not-changeable) SSMS Query Shortcuts.

That category includes only 3 Shortcuts, which run 3 stored procedures:

Alt-F1 - sp_help
- With no parameters returns two data sets: List of all objects with schema and type and User Defined Data types.
That list has a huge problem, it does not report actual objects' schemas and Microsoft is not going to fix it (See budg report: https://connect.microsoft.com/SQLServer/Feedback/Details/674733)
- If you select a particular object it will return minimum information about the object, such as "Creation Time". Exception is only Tables, info about Tables is awesome.

Ctrl-1 - sp_who
-  With no parameters returns very limited list of currently connected SQL Server sessions
- With selected "Login Name", returns only list of connections for that "Login Name"
I'd rather use sp_who2, it returns little bit more info.

Ctrl-2 - sp_lock
-  With no parameters returns very limited list of current locks in SQL Server.
- Accepts up to two Session IDs as parameters to filter on.

Not Native (Changeable) SSMS Query Shortcuts.

In this section I will list shortcuts which I use in my SSMS Studio.
You can download scripts from here: SSMS Query Shortcut Scripts
* These scripts are mostly developed by bright minds like: Glen Berry, Paul Randal, Adam Machanic, Ben NAvarez and others, but usually reverted from their original versions.
** Some scripts are developed by myself and I can accep all blame on them.
*** These query shortcuts are live and breathing. I'm constantly add new features, fix old bugs and adjust their functionality.

Here is their description:

Ctrl-F1 - Tables

Procedure "Ctrl-F1" returns following, depending on parameters:
1. No parameters: List of all user tables in current databases sorted by size and number of records.
    It is normal that tables with less records are bigger in size.
2. "Name of a table": "LIMITED" statistics for all indexes on that table.
    For large tables that operation might run for several minutes.
3. "Name of an index": "LIMITED" statistics for that index along with list of Page allocation.
4. "Name of a table or an index" + statistic mode: SAMPLED or DETAILED.
Example 1: tbl_MyTable
Example 2: 'CLUIX_MyTable','DETAILED'

Ctrl-3 - Locks (Similar to sp_lock)

Procedure "Ctrl-3" returns following, depending on parameters:
1. No parameters: List of all current Locks. Similar to sp_lock with some extentions.
2. "Database ID": List of all current Locks for the specified Database.
3. "Session ID": List of all current Locks for the specified session.
 That is possible that "Session ID" and "Database ID" cross each other and wxtra results are returned.
4. "Database Name": List of all current Locks for the specified Database.
5. "Table Name": List of all current Locks on that table.
    Have to be a table in the current database or with fully qualified name.
6. "Object Id": List of all current Locks on that object.
7. "TAB", "PAG", "DB": List of all current Locks for specified type of an object.
8. "X", "U", etc. : List of all current Locks with specified mode.
9. "IP" or "Host Name" or "Login Name" or "Application Name": List of all current Locks for specified source.
 That option might match table or database name.
Example 1: tbl_MyTable
Example 2: TAB
Example 3: 'Sch-M'
Example 4: 123456789

Ctrl-4 - Indexes

Function Ctrl-4: SQL Server Index Troubleshooting.
1. No options: Returns Lists of TOP 10 Not-Used Indexes and TOP 10 Missing Indexes.
   Disclosure A: Both lists are valid only after full regular business workload cycle. Without SQL Server Reboots and Index Maintenance.
   Disclosure B: Before applying Missing Indexes, always check if any similar index exist. Maybe you can just simply modify an existing one.
2. Index Name: Returns Index Statistics and general info.

 Ctrl+5 - Databases

Function Ctrl-5: SQL Server Database Details.
1. No options: Returns List of all Databasese and their files with current sizes and used space.
2. Database Name: Returns sizes and used space for specified DB and Temp db. Lists of all DB Settings.

Ctrl+6 - Performance Troubleshooting

Function Ctrl-6 Options: SQL Server Current Activity.
1. No options: Returns List of currently running tasks, list of all user opened trnsactions and Current Waits stats.
2. Letter "B": Returns only running blocking chains.
3. Letter "S": Returns data in simple mode without Quwery Plan.
4. Letter "W": Returns Only Current Waits stats.
5. Letter "W" + Number of seconds : Returns Waits Stats Delta. (Max=999) (Example: "W10" Returns Delta waits within 10 seconds).
6. Letter "M": Concentrate on Memory. Adds Memory allocation per database.
7. Letter "C": Concentrate on CPU Utilization. Adds dm_os_schedulers, dm_os_workers & dm_os_waiting_tasks.

Ctrl+7 - Recent Connection's queries

 Returns information about current sessions.
 Parameters:
 (No Parameters) - Returns full list of current sessions.
 Number - filter by Session ID
 IP Address or "<local machine>" - filter by client Net address.

Ctrl+8 - Query Cache

Function Ctrl-8 Options: SQL Server Query Cache.
1. No options: Returns: % of Adhoc Queries; TOP 1000 List of all queries stored in SQL Server Cache and TOP 10 Working time diagram.
2. "X": Query Plan in XML format. (Might generate an error: "XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.")
3. Part of a query: Will search (TOP 1000) for that part of a query within SQL Server Cache.
4. "ORDER BY + field name + ASC/DESC": Adds sorting to the full List of all queries stored in SQL Server Cache. (option #1)
5. sql_hqsh or plan_handle: Returns all plans for particular SQL Query or only one plan for plan_handle

Ctrl+9 - Statistics

Function Ctrl-9 Options: SQL Server Statistics.
1. No options: Returns List of all Statistics in current DB.
2. Table Name: Returns List of all Statistics for specified table (Applicable for SQL Server 2008R2 SP2 or higher).
3. Statistic name: Returns Statistics' Header, Density Vector and Histogram.

Ctrl+0 - Objects

Function Ctrl-0 Options: SQL Server Objects.
1. No options: Returns List of all tables and List all objects in current DB.
    It is normal that tables with less records are bigger in size.
2. Object Name: Object's code or full description.
3. Index/Table name (+ parameter): Gives info about index allocation. Second is reporting mode: SAMPLED or DETAILED.
For system tables requires fully specifired name like: 'sys.objects'.
Does not handle: Synonyms, Defaults, Constraints.
Example 1: tbl_MyTable
Example 2: 'CLUIX_MyTable','DETAILED'

SSMS Query Shortcuts' Implementation

To set these manual shortcuts you have to do following:
1. Press Alt-T + O, which will bring you to SSMS Tools and Options.
2. Go to "Environment -> Keyboard -> Query Shortcuts"

3. Select the shortcut you need and insert the query into second column's cell.

Things to pay attention to:

1. The query script must not have Carriage Return symbol with ASCII code 0x0D. You can put whole script in one line, but it won't be editable well. So, use the PowerShell script included in the package to remove Carriage Return symbol.

2. In order to use newly created or changed shortcut you have to open new Tab in SSMS. All old tabs will not see the change.

3. These scripts create Temporary Stored Procedures and if your user database  has different collation with TempDB database you'll get an error message.

4. If you going to modify my script or write your own, remember the size limit for Query Shortcuts is 32K.

5. The BIGGEST problem of Query Shortcuts that you can't Export/Import them in SSMS.

If you are  going to use Query Shortcutsand also not satisfied by inability to Export/Import them in SSMS please leave your comment in Microsoft Bug Report: "SSMS Query Shortcuts' Import/Export do not work"

Cool New feature "Include Live Query Statistics"

Exploring SQL Server Management Studio Version 2016 CTP 2.

It has very cool feature "Include Live Query Statistics"

It has an new icon in the Toolbar right near "Actual Execution Plan" icon:




What is cool with that feature?
That is a live view of current query execution.

I've tried to come up with a query, which would demonstrate that:

USE AdventureWorks2014;
GO
DBCC DROPCLEANBUFFERS;
GO
;WITH Prices AS (
    SELECT dbo.ufnGetProductDealerPrice(d.ProductID, h.OrderDate) AS Price,
        ROW_NUMBER() over(ORDER BY p.MiddleNAme) rn, p.PersonType, pr.Color
    FROM Sales.SalesOrderDetail AS d
    INNER JOIN Sales.SalesOrderHeader AS h ON h.SalesOrderID = d.SalesOrderID
    INNER JOIN Person.Person AS p ON h.CustomerID = p.BusinessEntityID
    INNER JOIN Production.Product AS pr ON d.ProductID = pr.ProductID
) SELECT * FROM Prices;

And I was be able to capture a moment on the screen when Hash Match operation is already done and Sort operation just started.

In this case it might be not so useful, but for complex queries which run for several minutes minutes it would be very useful to see what SQL Server is doing right now and what a bottleneck there can be.

It looks like Microsoft finally decided to put more improvements into SSMS.
SSMS Rocks!