Saturday, February 13, 2016

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"



Friday, February 5, 2016

SQL Saturday in Baltimore. Pre-Con survey results.

Just in case you do not know: I'm one of co organizers of SQL Saturday in Baltimore, which will take a place in Columbia, MD on April 30st.

SQL Saturday is wonderful Free learning event about SQL Server technologies (you only pay for your own lunch).
Our SQL Saturday officially is in BI edition. That means there will be not only about SQL Server, but also about SSIS, SSAS, SSRS, Hadoop, BigData, PowerBI, Data Warehousing and etc.

If you live in Baltimore-DC Metro Area, Maryland, Northern Virginia, Southern Pennsylvania, Eastern part of West Virginia, Delaware and as part of your job you are touching SQL Server - there is no excuse for you not to visit Columbia, MD on April 30st.
If you can't be there whole day it is OK, you can come only for the session you want to attend.
Unfortunately in this case you'll miss all great sponsors' prizes at the end of the event.

If you are interested in which sessions and speakers will be present at the event you can take a look here: http://www.sqlsaturday.com/506/Sessions/SubmittedSessions.aspx

If you haven't registered yet here is the link: https://www.sqlsaturday.com/506/registernow.aspx
Do not be late to get to the waiting list and possibly miss the event.

As part of our SQL Saturday we will also do couple of Full Day paid Pre-Con sessions, which will be scheduled for day before: Friday, April 29th in the same Loyola University building in Columbia, MD.


Final decision on who will be speaking at Pre-Con is not made yet, but here I will publish the results of our survey we did on whom people want to speak and what topics do they want to see in Baltimore:

In the nomination of topic the winners are:
1. T-SQL
2. Performance/Internals
3. ETL/SSIS
That choice of topics will drive the decision on who will be the speakers.



Speaker nomination is much more difficult.
At first list was so big that I couldn't grab the whole diagram at once.
That probably also plaid a bad role in people's decision. 10 people couldn't determine whom they would like to see.


















































































Some people added new names in the list of suggestions, some of which I immediately added to the questionnaire.
Ben Galluzzo and Rob Volk weren't in the list from the very beginning, that would explain their not perfect results.
I also had as suggestions names of Brent Ozar and Grant Fritchey.
Guys, they were my first choices and I had conversations with them way before I've made the survey.
However, their busy life can't accommodate extra traveling to Baltimore.
Maybe next year?

Had a request to get ANYBODY fro SQLSkills. The person who suggested it also read my mind. I've asked them, but they are too busy and ravel only for their own events and to PASS Summit.

Also have suggested: Kevin Boles, Jason Brimhall, Wayne Sheffield, Devin Knight, Marco Russo, Alberto Ferrari, Chris Webb, Kasper De Jonge. Unfortunately it was too late to include all of them to the list and then the list would be tooooooo big.
Another my concern is that those names are not very famous in our area. Besides of Wayne Sheffield none of those SQL speakers recently presented in Baltimore.
I'd suggest to bring those guys to SQL Saturday first, and then the next time people will be more knowledgeable and will vote for the best.

Back to the diagram:
I've already contacted the winners: Itzik Ben-Gan and Adam Machanic, unfortunately they also can't come. As a satisfaction Itzik helped as to get book sponsorship from his publisher and we will have his books as a prize at our event.

From all others we have to choose two speakers:
1. Best for SQL, Internals, and troubleshooting;
2. Best for ETL and SSIS

It is already too late to vote or make suggestions.
I've sent invitations and hope these people will accept them.

As soon we will have Pre-Con speakers I will officially announce them.

If you want to be informed immediately, please register for the SQL Saturday event then you'll get an email as soon as Pre-Con registration will be available.