Ssms-Tips-Tricks

Back when SSMS for SQL Server 2016 was released, a search tool called Quick Launch was added. It was this bar up the top of previous versions:

Note there was also another bar underneath it to the left. That was the Find bar. While the Find bar was useful for searching for text within queries, etc., the Quick Launch bar was useful for searching within SSMS itself. This was great because it means you don’t have to remember where all the options for various things are set. Here was an example:

2025-09-22

This one is a very simple and quick tip.

When working in SSMS, I often need to open Windows File Explorer in the folder where the script file is stored. Turns out there is an easy way to do that.

There are two interesting options when you right-click the tab at the top of a query window. (Note: not on the File menu)

Note that you can open the containing folder for the script. You can also copy the path to the script into the clipboard.

2025-09-20

Every now and again, I come back to my laptop and find that it has rebooted for some reason, while I wasn’t expecting it. A prime cause of that is Windows Updates. I really, really wish that wasn’t so, but someone at Microsoft has decided that I must apply these updates. I have very little control over the time when that occurs. For example, if I’m on the road delivering presentations, there’s no “wait till I get home” option for Windows Updates.

2025-09-18

SSMS keeps a list of the server names that you have connected to, and prompts you with those when you drop-down the list while making a connection:

Eventually, that list can either become messy, it can include servers that don’t exist anymore, and so on. You might want to clear up the list.

To do this in early versions of SSMS, you needed to locate the SqlStudio.bin file from the Documents and Settings area in your user profile. Fortunately, that’s no longer required. In more recent versions, all you needed to do was to open this dialog, arrow down to the ones that you want to remove, and hit the Delete key.

2025-09-16

In SSMS, when you click New Query, a new query window is opened, but because it’s blank, what might not be immediately obvious is that it’s based on a template.

The location of the template depends upon the version, but for SSMS 17.6, you’ll find it in this folder:

C:\Program Files\Microsoft SQL Server Management Studio 21\Release\Common7\IDE\NewFileItems\SQLFile.sql

The file is called SQLFile.sql. If it’s not in the location above, just search for SQLFile.sql.

2025-09-14

SQL Server execution plans can become quite large. That makes them hard to navigate because you are endlessly scrolling around the results pane in SSMS.

The pane does have some zoom features. Note that if I right-click in the whitespace, I get these options:

So I can zoom in and out, set a custom zoom level, or zoom until the entire plan fits. Generally though, that would make the plan too small to read, as soon as you have a complicated plan.

2025-09-12

In an earlier post, I described how query plans could be saved as .sqlplan file, shared, and loaded again in SSMS. It’s also possible to extract them out of SQL Server Profiler or Extended Events Profiler.

This is useful, but the same applies to deadlock graphs. SQL Server 2005 added Deadlock graph as a type of event in SQL Server Profiler. (It’s also part of Extended Events Profiler).

If I open a new trace in Profiler, I can add Deadlock graph to the list of events:

2025-09-10

SQL Server query plans are stored as XML. You can see what they look like by right-clicking in any query plan in SSMS, and clicking Show Execution Plan XML:

That will return a whole bunch of XML like this:

It’s important to understand that when SSMS is showing a graphical execution plan, it’s just graphically rendering some XML like the plan above.

The Properties window in SSMS is also showing details extracted from that same XML.

2025-09-08

I’ve mentioned before that SSMS is a good tool for analyzing queries, as much as for executing them.

Way back in SQL Server 2005, query plans had missing index details added. When a query plan was created, SQL Server recorded that it thought it could have executed the query better, if only you’d provided it with appropriate indexes. But at that point, the suggestions weren’t very good, and the tools didn’t show them.

2025-09-06

One of the advantages of SSMS is that it can be used to analyze queries, not just to execute them.

There are two basic types of query plan: estimated execution plans, and actual execution plans.

For a typical query, I can obtain the estimated execution plan, by hitting Ctrl-L, choosing the option in the Query menu, or clicking on the toolbar icon: