Ever go into Tools-Options? SSMS has a stunning number of options these days. Here are some of my favorites:
On the Documents options, I uncheck “Check for consistent line endings on load” because I constantly get scripts with all kinds of wacko line endings. That warning is a pain in the butt.
On the Fonts and Colors options, I used to get fancy. There are all kinds of “best programming fonts” articles out there with great-looking fonts. However, when I did screenshots for presentations or clients, people kept asking, “Why does your SSMS look so weird? Is it because you’re on a Mac?” These days, I leave those options at their defaults.
On the Query Shortcuts screen, you should set up shortcuts for the scripts you run most often. I don’t – but it’s only because I have a wacko job as a consultant. I’m constantly jumping into an SSMS on someone else’s desktop, and they won’t have the shortcuts set up, so I don’t wanna develop muscle memory for something I won’t have access to. If I was you, though, dear reader, I’d set these up.
On startup, SSMS defaults to just opening Object Explorer. I like to open a query window too, though – after all, I’m probably opening SSMS to run queries.
Under “Tabs and Windows,” check the box for “Show pinned tabs in a separate row.” This way, when you click the pushpin on a given tab, he pops up to the top like this:
I love that for frequently-used tabs – I might have a dozen query windows open, but I keep coming back to, say, the window with sp_WhoIsActive open. I save that tab with a recognizable query file name, and then when I pin it, it pops up to the top in that separate row.
Speaking of which, those default tabs are hideous – go to Text Editor, Editor Tab and Status Bar:
Scroll down to “Tab Text” and set everything to False except for “Include file name.” When you click OK, it doesn’t take effect on existing tabs, but after you close & reopen them – ahhh, much more legible. Check out how many more tabs you can fit on a screen:
Next up, going back a little in Text Editor, go to All Languages, Scroll Bars:
The default behavior is bar mode, but if you change it to map mode, you get a text map down the right hand side scroll bar. I don’t find that all that useful, so I don’t enable it, but if you’re the kind of person who has long stored procs, you might. The really cool part is when you hover your mouse over the scroll bar map on the right, you get a little zoom popup so you can see a preview of the code at those lines:
I don’t set mine up that way, but I can see why people do, and if you’re reading this post, you’re probably interested in that option. Anyhoo, moving on to All Languages, Tabs:
SSMS defaults to tabs, and so I switch it to “Insert spaces.” Insert religious flame war here. Moving on….
Under Transact-SQL, General, I check the box for “Line numbers.”
I would just like to point out that no, I do not set my deadlock priority to high. As far as you know.
Under Query Results, SQL Server, Results to Grid, I change my XML data size to unlimited so that it brings back giant query plans. (Man, does my job suck sometimes.)
A lot of presenters like to check the box for “Display results in a separate tab” and “Switch to results tab after the query executes” because this gives them more screen real estate for the query and results. I’m just really comfortable with Control-R to hide the results pane.
Under Designers, I uncheck the box “Prevent saving changes that require table re-creation” because I never just hit save when I make changes in a designer anyway. I always click the generate-scripts button, but strangely, you can’t even generate scripts when a table re-creation would be required. Personally, I’m a big fan of recreation. Also, parks.
Under Object Explorer, Commands, I change “Surround object names with brackets when dragged” to False. I usually find that when I’m dragging an object from the OE pane over into a query, that I specifically need it without brackets for some reason.
After I’m done in Tools, Options, I go into View, Toolbars, Customize. Click on the Commands tab, then choose Toolbar, SQL Editor:
These are the buttons that get shown when you’re working with T-SQL. I click on the Debug control, take a deep breath, and while I’m clicking the Delete button on the right hand side, I scream at the top of my lungs, “WHO THE HELL THOUGHT IT WAS A GOOD IDEA TO PUT THIS BUTTON RIGHT NEXT TO EXECUTE?!?!?”
I have less passionate feelings about the rest of the buttons, but I still end up deleting most of them. I don’t really need a button for Query Options or IntelliSense, and I like a clean, minimal UI. After I’m done cleaning out the SQL Editor toolbar, I click the toolbar dropdown, choose the Standard toolbar, and clean that out too. No, I’m never starting an Analysis Services DMX Query. I certainly don’t need buttons for copy or paste. (The only reason I even leave “Execute” as a button is because sometimes I like showing training class attendees that the execution is about to start.)
The end result is a much smaller set of buttons, and they all fit on a single row even when I’m editing queries.
50 Comments. Leave new
I used to set my font for SSMS to Terminal, mostly because I like how it looked like an old-school green-screen terminal.
Sadly, MS took that font away from us.
I always recommend setting the Query Governor Option to 20000, just in case.
Then I keep a snippet for removing the option via TSQL
SET QUERY_GOVERNOR_COST_LIMIT 0;
Great tips, these. The ones I don’t already use, I am trying.
Any tips for Sam’s plugins and their ideal configs?
I’m not sure who Sam is?
Thanks for the tips Brent
Great tips Brent! My favourite is: Under “Tabs and Windows,” check the box for “Show pinned tabs in a separate row.” I use this all the time for regular scripts such as WhoIsActive & sp_Blitz bits. My only bug bear is that upon closing SSMS and reopening the pinned tabs are no longer on their separate row. Not sure if this is SSMS or Redgate tools (all my tabs reopen when I start SSMS)? Thanks SQL Dan
Is there any way to share registered server to the dba team? Presently, i have to export my registered servers to a file and ask my team to import it.
A central management server would certainly do. https://docs.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group
yes use Central Management Server.
is there anyway to change the font to wingdings for the query window? asking for a friend that is 100% not “helping” a guy install SSMS that requested “administrator access for sequel server”
I totally would never do something like change the font for the grid results to be white on white.
Any idea why [sql server object explorer]/[scripting] options keep getting reset? Seems like every week I have to change “script permissions” and “script indexes” to TRUE. They keep getting set back to FALSE.
In an ideal situation you could save these settings and reimport them as needed when you get a new desktop management machine (or publish a blog post on your recommendations)
Did you look at Tools, Import and Export Settings…?
Is the Xml_data exported? I’ve tried to import and this option not changed in my new environment.
Any idea if there is a way to change the generated scripts to default to SORT_IN_TEMPDB = ON and / or ONLINE=ON?
There is traceflag. I am not sure but such as maybe 746.
useful tips man!
And if you’re configuring someone else’s SSMS – someone you don’t like:
Change the batch seperator from GO. Changing it to SELECT can be fun.
Change the error text colour to white.
Just those two above can be hours of fun.*
* Or very very unprofessional, to say the least. 🙂
What type of Query Shortcuts would you set up? I have had sp_WhoIsActive set up for years, but have never really figured out what to use the other keys for.
Shortcuts can be powerful because of the context–they append whatever you have highlighted.
Some generic shortcuts I use:
* select top 1000 * from
* exec sp_executesql N’xp_readerrorlog’;
* select [schema] = s.name,[table] = t.name,[column] = c.name from sys.schemas s inner join sys.tables t on s.schema_id = t.schema_id inner join sys.columns c on t.object_id = c.object_id where c.name =
* exec sp_executesql N’select name = case c.column_id when 1 then ”” else ”,” end + case when charindex(”-”,c.name) > 0 or lower(c.name) in (”status”) or charindex(” ”,c.name) > 0 then QUOTENAME(c.name) else c.name end ,datatype = typ.name ,c.max_length ,c.[precision] ,c.scale ,c.is_nullable ,c.is_identity ,c.is_computed ,c.column_id from (select object_id,schema_id,name from sys.tables union all select object_id,schema_id,name from sys.views) t inner join sys.columns c on t.object_id = c.object_id inner join sys.types typ on c.user_type_id = typ.user_type_id where t.name = replace(replace(@table,”[”,””),”]”,””) order by t.schema_id, c.column_id ;’,N’@table varchar(100)’,
(yes, you can get pretty crazy with the shortcuts)
Wow thanks a lot for this tip 😮 This is going to be so helpful
Any tips for making it launch faster on Windows 10? Always takes around 30 to 45 seconds to launch. Tested on multiple Win10 machines with and without AV running.
No, I don’t use Windows 10. (Had to install it on a VM for the first time this week for a client gig, and I recoiled in horror. Man, that thing is full of crapware.)
Shut down SSMS.
Go into IE, select Tools|Internet Options|Advanced
If “Check publisher’s certificate revocation” under the security node is checked, then uncheck it.
that worked for me.
Thanks for the tips Brent!
[…] How I Configure SQL Server Management Studio Interested how Brent Ozar (t) does configure his SSMS? […]
[…] There has been many a blog post out there that shows you some of the great ways you can customize the look and feel of your management studio windows. One of my favorite recently published ones is from the great people at Brent Ozar Unlimited (Brent himself in this case) here. […]
[…] There has been many a blog post out there that shows you some of the great ways you can customize the look and feel of your management studio windows. One of my favorite recently published ones is from the great people at Brent Ozar Unlimited (Brent himself in this case) here. […]
Strangly, from time to time, all my modification to the SSMS configuration are lost and I got all the “by default” config back.
I couldn’t find any explanation as my profile (where the configs should be saved) haven’t been deleted.
As the export/import feature does not seems to work either, I end up doing all my config manually 😮
Any taught ?
Dominique – no, sorry, for support, you’ll need to check with Microsoft.
While using Always on Read only replicas, developers had to manually go into options tab select database name and then go to connection properties tab to mention “applicationIntent = ReadOnly”..
Can we automate it …. or somehow add this to registered server settings ??
Avijeet – for Q&A, head on over to a Q&A site like https://DBA.StackExchange.com.
Does anyone know of a keyboard shortcut to disable intellisense and then turn it back on while typing queries?
Thanks…
Mike
Mike – for random Q&A, your best bet is to head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
Hey Brent – how do you get rid of the super annoying green squiggly lines. I believe they are intellisense warnings.
Turn off IntelliSense. It’s in Tools, Options.
I think I have it turned off an they are still there…
OK, for random Q&A, your best bet is to head to a Q&A site like https://dba.stackexchange.com or https://sqlservercentral.com.
Will do, Thanks!
In SSMS v17.9.1, how do you turn off the Output window at the bottom of the screen at startup?
Great suggestions, I gained an entire line on my screen cleaning up the buttons.
Never mind ?
Do you know where Copy with text format go?
When I copy from SSMS to an email it just go as plain text without formatting.
It used to be under Options -> Editor -> Behavior, but that doesn’t exist anymore.
Thanks !
Barak – sounds like a great question for SQLhelp. Here’s how it works: https://www.brentozar.com/go/sqlhelp
Great tips Brent – thank you
Thanks for these tips.
I also set the option “By default, open new queries in SQLCMD mode” under “Query Execution”.
Hey, love the suggestions for SSMS. Here’s one you may or may not have heard:
There is a Registry hack whereby you can enable thumbnails for open tabs in SSMS so that when you hold down Ctrl and cycle the Tab key, you will see a list of open tabs. As you cycle through each one in the list, you get a thumbnail of the query and results pane. Super helpful if you usually work with lots of tabs open, can’t remember what each named query was for, but can tell by looking at the Results which one to jump to.
Works with Visual Studio as well.
Here’s the hack:
HKEY_CURRENT_USER \ Software \ Microsoft \ SQL Server Management Studio \ 11.0 \ General
(May need to adjust the version above)
Right-click on the folder, select: New >> D-WORD (32 bit)
Name: ShowThumbnailsOnNavigation
Modify and set the value to 1
We’ve been really bad at SQL code management in our group. I’ve taken to using the SSMS Generate Scripts function to dump out various database instances, then using WinMerge to compare them. That’s worked pretty well, once you get the right settings (like don’t generate that time-stamped comment header, include compression options, etc). I changed a 6-10 of the default settings under Tools > Options > Sql Object Explorer > Scripting.
Now we’re trying to standardize sql code management so we can maintain things better (get a consistent naming convention, get all the objects out, etc) but the issue is getting all the same scripting conventions used.
I’ve been looking for a while for some way to codify the Tools > Options > Sql Object Explorer > Scripting options so that everyone will be consistent but so far have not found a way to do that.
Are there better tools for exporting sql ddl and code? Or a way to export/import those settings for SSMS?
I see there are management objects for C# to do SSMS-type things, so presumably we could write our own wrapper around that, but I was looking for a way to use the existing tool consistently.
Thanks
Registered Servers is a great feature, for two main reasons:
Reason #1 – Instead of trying to remember the full path to a SQL server (FQDN), such as sql.uat.companyxyz.com, especially when your company has numerous SQL Servers (prod, uat, qa, etc), simply register each Server in the REGISTERED SERVERS with an Alias Nickname such as “UAT”, “UAT DW”, “PROD MIRROR”, “PROD DW”.
1. In SSMS, click VIEW >> REGISTERED SERVERS ( or just press CTRL + ALT + G )
2. Expand “Database Engine” to see “Local Server Groups”
3. Right-click “Local Server Groups” and choose “New Server Group” and name it “UAT Servers”, for example.
4. Right-click “UAT Servers” and choose “New Server Registration”
5. Provide details as shown in the below example. At a minimum, supply:
5a. Server name (FQDN of the SQL server)
5b. Registered server name (the alias of your choice) such as “UAT DW”
5c. ALSO, optionally set the DEFAULT DB (eg: PPSBackend), on the Connection Properties tab.
5d. ASLO, optionally choose a COLOR per each server name. This will change the SSMS status bar color, upon connecting, as a visual confirmation (i.e. RED for PROD connections).
6. Repeat for NEW UAT server .. and any others deemed necessary for your efforts.
Reason #2 – While working in a UAT setting, I needed to compare data from a PROD table against a UAT or QA table (or vice versa). Since I’ve defined “Local Server Groups”, SSMS can pull data from ALL servers within the group(s) and present the results as a SINGLE result set!
1. Select group name “UAT Servers” (for example) and click NEW QUERY (or drag one of your .SQL script into the SSMS workspace) and execute it.
2. SSMS will show results from ALL SERVERS within that Server Group.
Rather useful, I think, when trying to compare UAT PROD or whatever combination of servers you wish to cross-compare within a defined “local server group”!
Unfortunately, your alias “registered server” names do not show up, on the SSMS connection prompt. Not sure why .. seems like a Microsoft oversight. The work-around is simply to highlight/select the desired server alias, BEFORE you open a .SQL file or a NEW QUERY window. Then, open an existing .SQL file, either via the FILE >> OPEN menu .. or by dragging one from Windows Explorer into the SSMS workspace .. or clicking NEW QUERY.
Then, confirm that the query is using the desired connection .. especially, if you chose a color as noted above!