Step 1: configure SSMS to only show file names on the tabs. Click Tools, Options, Text Editor, Editor Tab and Status Bar, and set all of the tab texts to false except file name. After all, not like all this stuff fits on the tab.
Step 2: while you’re in Tools, Options, click on Tabs and Windows. Check the box for “Show pinned tabs in a separate row.”
Step 3: start new windows for your favorite utility queries, and save them with the right names. For example, I have a window open just for sp_WhoIsActive, so I save that as sp_WhoIsActive.sql. It doesn’t actually have the sp_WhoIsActive CODE in that window, just a simple EXEC sp_WhoIsActive.
Then pin those tabs – click the little pin on those windows, and voila, they show up in their own row:
Go get SSMS 2016. It just runs more awesomely.
70 Comments. Leave new
Anybody try using SSMS 2016 to connect to previous versions of SQL Server, namely 2014 or 2012? Any known gotchas?
I’m using it for servers as old as 2008 with no issues so far. Release notes say it supported for 2008 and later, and it will work with 2000 and 2005 as well with some features perhaps not working.
I’ve been using it for months for demos, connected to 2014, and it was fine. Every now and then it crashes, but hey, so does SSMS 2014, heh.
For large queries/text, the Scroll Bars (under Text Editor -> All Languages/Specific Language) section is a very nice addition. “Use map mode for vertical scroll bar” is awesome and something I miss after all the time I spend in Notepad++ and Sublime Text.
Of course I Replied to the wrong comment, haha.
Yes, that’s very cool!
as mentioned in release notes, maintenance plans for prior versions can’t be saved in the new SSMS. That could be an issue if you’re using maintenance plans
A separate pinned tab for EXEC sp_WhoIsActive, I’m shocked Brent. Drop that goodie in a keyboard shortcut. Easy to access, one less tab in the clutter.
Nah, because I like rerunning sp_WhoIsActive and keeping the results while I’m troubleshooting an execution plan or a locking issue. I don’t want the results going away.
Ok, you win that one 😉
As Grandpa always told me, “Don’t get too excited, kid, even a blind squirrel finds himself a nut every now and then.”
Step is also also available in SSMS 2012 and 2014.
Step 1, I mean..
Yep!
I got really excited about the pinned tabs idea until I realized they don’t persist when you close/reopen SSMS. Am I missing a setting somewhere to make that happen?
Wait – why would you ever close SSMS?
(I kiiid, I kiiid)
Why? Because of this! 😉 https://twitter.com/BrentO/status/215462270497603585
You, sir, win one Internet.
Redgate Tab History…
Joe, looks like persisting partially works if your queries are part of a Project. At least it ‘remembers’ it when I close and re-open SSMS. But looks like there are certain events which ‘break’/reset it (like opening a different projects).
I see that the default project location folder in SSMS 2016 is ‘c:\users\UserXYZ\documents\visual studio 2015’ instead of ‘c:\users\UserXYZ\documents\sql server management studio’ like in previous versions. I understand that SSMS 2016 switched to VS 2015 shell but still can’t tell if this is a bug or a feature 🙂
After noticing the above mentioned default project location change, I came up with the following puzzle:
Question: What’s common between SSMS 2016 and Cuckoo:
Answer: they both leave their eggs in other bird’s nest.
Varsham Papikian
I think they do.
In Options \ Environment \ Tabs and Windows, under the Pinned Tabs group, check the option: [Maintain pin status if document is removed from well].
Has anyone found a way to not have the recent files list be cluttered with items from AppData\Temp\ (or is this just me)?
I haven’t seen that, might be just you there.
This couldmean you are opening up your scripts from third party applications, like Outlook. Or you are Shutting down your machine without saving you SQL files (Could be power outages) .
I, too, am frustrated with my recent files list being cluttered with temp files
No – it bugs the hell out of me too. There’s an issue open at connect.microsoft.com that you might want to add your weight to:
https://connect.microsoft.com/SQLServer/feedback/details/2558789/ssms-march-2016-temp-files-are-in-recent-file-menu
SMSS 17.9.1 is also bugged with this issue. I have it, too. Perhaps it does not play well with Red Gate Tab History since some people notice it and some dont.
I am having problems with it crashing quite frequently. I don’t have the same problem with SSMS 2014. I have just about given up on using SSMS 2016.
The release version seems to be doing better for me so if you haven’t upgraded to the RTM version you might try that.
Have you found the Live Query Statistics useful?
am i weird? drag-n-drop doesn’t seem to work like it used to.
With an empty SSMS (no query windows open), i _am_ able to drag and drop a file just fine. If i drag and drop _another_ file into SSMS, it doesn’t open a new tab for it. When i do this in SSMS2014, it works; it’s worked since 2008R2 probably.
I suppose it also depends _where_ you drop that second file. If you undock your query windows, you can drop multiple files as long as you drop the file into the gray-background area (not sure what that’s called). But you can’t drop right onto a maximized query window in 2016. in 2014 this works fine.
can someone duplicate this? is it a bug; please don’t call it a “feature” 🙁
Paul – for unrelated questions, you’re best off posting on a Q&A forum like http://dba.stackexchange.com.
Paul, I experienced this same behavior today in SSMS 2016 RTM (13.0.15000.23).
Drag and dropping into an already-open script doesn’t open the drag-dropped script.
As a workaround, similar to yours, but instead of undocking, the drag-drop open of a script works if I drag to the thin bar of the dark blue background area where a script tab *would* be.
This does seem to be a regression bug from my user perspective; Microsoft might see it as a “feature” of VS 2015.
Thanks John! I agree, i really dislike new features 🙁 So, what’s the best way to rage against the machine?
(Sorry Brent!)
Paul,
Good news, it looks like they fixed the drag/drop script issue in July 2016 SSMS (13.0.15500.91).
See SSMS changelog at:
https://msdn.microsoft.com/en-us/library/mt238290.aspx
•Bug fix in the SSMS editor window to allow drag-and-drop open of Sql files (Microsoft Connect item #2690658).
Cool. thanks john.
My favorite…
Query Execution > SQL Server > Uncheck the box “Prompt to save unsaved T-SQL query windows on close”
Can SSMS 2016 run with SSMS 2012 already installed on the same machine?
Yes. You will have separate installs of each. You can even run them at the same time.
Thanks Erik!
What I’m looking for is a short preview of the code in each of the many query windows I have open, on the CTRL-TAB list, as going through each one in turn is a pain.
Execution Plans — now you can simply click and drag on the plan area/background in order to navigate around the plan, which is great for complex plans. No more having to use scroll bars or the [+] button to navigate. I find this much smoother in operation and far less tedious, although the ‘map view’ with the [+] button is very useful at times. A full-screen borderless type of view would be a great next step.
Coool!
Have you tried the free SQL Server Plan Explorer? I just right click and open in there. It makes it much more clear.
Might just be me but I’m trying out the pinned tabs feature in the release version of SSMS 2016 and every time I start my laptop up in the morning and fire up SSMS, it has forgotten I’d pinned any 🙁
is there a solution?
Did Intellisense get fixed in SSMS 2016?
what bugs me most day-to-day in 2012/2014 is how messed up Intellisense got. It stopped working across VPN’s and no longer showed field lengths/properties with the Data Types (for example in 2012/2014 it will say “NVARCHAR” but not “NVARCHAR(70)”) I hope 2016 has brought back Intellisense Sanity.
Jonathan – unfortunately, if you haven’t filed a Connect request for what you’re interested in fixing, and got other people to upvote it, then it probably didn’t get fixed.
Thanks Brent.
I have submitted it to MS a couple times (with each new version) they worked great in SSMS 2008, but in 2012+ the functionality changed/diminished.
Perhaps I’m submitting through a channel that is less preferred than others. Do you have a specific link, or contact information that I should be using?
Thanks!
~Jon
Submit one here and then get everyone you know to upvote it. http://connect.microsoft.com/SQLServer
I use SSMS over VPN frequently, and feel the Intellisense pain there. It’s definitely improved in 2016, although possibly not perfect – there is a ‘resolved’ Connect issue for it though:
https://connect.microsoft.com/SQLServer/feedback/details/805430/ssms-2012-intellisense-and-vpn-problem
Hey Brent,
Not sure if anyone else is running into this issue, but with SSMS 2016, simply right-clicking on a database table takes 8-10 seconds to bring up the menu.. in SSMS 2014, this menu comes up in a second tops. This kind of slowness can also be seen in SSMS 2016 when I run a simple select top 1000 rows from that right-click menu.
I did some googling but no dice. I tried checking my video driver, uninstalling any programs that run on top of SSMS like ApexSQL, any VS plugins related to SQL, checked for updates, still no luck. Because of this, I’ve been forced to go back to SSMS 2014 but am just using that to work against the SQL Server 2016 database.
If you have any thoughts or find anything I may have missed, I look forward to hearing back from you! Perhaps something to run to try and determine what the source of the issue is, if any. Just to note, I’m running on a Surface Pro 4, i7, with 16 GB RAM, and an SSD drive with more than enough space.
Thank you very much!
-Matt
Matt – no, I haven’t seen anything like that. You may try someone else’s machine.
I see the same issue. Removing ApexSQL Refactor seemed to fix it.
Thanks for posting that. I see this slowness on context menus for table and database dropdowns also. It’s sending me back to smss 2014. This is the August 16.3 running on an i7-5930K, 32G, 1TB 850 ssd, so hardware is not the issue, and as I implied smss 2014 is plenty zippy on this box. Shame because I agree the new feature set and visual appeal is superior. But. just. too. slow.
Hi Matt,
Have you checked your anti-virus exclusions, and also I’ve turned off the User Feedback option here http://dba.stackexchange.com/a/145930. It “seems” to have worked so far, but if it slows down again I’ll let you know.
Cheers,
Ben
I am still having this issue on my SP4 i7 16gb. SSMS 2014 is fine, 2016 lags bad.
Matt,
I always disable hardware acceleration in Tools – Options – Environment – General in SSMS (and Visual Studio, and Office). I don’t know why, but this drastically improves performance on my home laptop and my work desktop. I suspect that there is something not working with handing off workload to the GPU, and a series of exceptions are bubbling up under the hood. I have no proof that is the case but startup and UI responsiveness improve drastically after turning off that options on my machines.
For anyone else stumping on this lag I found out that Red Gate Source control was the culprit. Some mods managed to speed it up a little but lag is still there,
OH THAT IS SOOOOOO COOL!!! I love it! Thank you!!!!!
Hi Brent. Do you know how to make the background of the enhanced scrollbar (they’ve added the same scrollbar as they have to Visual Studio) on the right side of the window black? I have a black theme and since the font is white, I can’t see much in there 🙁 And I couldn’t find the setting for this in Tools > Environment > Fonts and Colors. Thanks.
Darek – for unrelated questions, your best bet is to head to a Q&A site like http://dba.stackexchange.com.
Has anyone seen VERY SLOW (1-2 minute timeframes) connecting to an instance of SQL Server 2014 using SSMS 2016 (build 13.0.15700.28)? I finally gave up attempting to connect to an AAG listener (listener name, port) a little while ago.
13.0.15700.28 seems to have some serious issues, I recommend folks don’t upgrade to that version just yet (and if you have…oh well, sorry).
Issues like missing “schema” as a filter option for tables/procs/etc. — very annoying (https://connect.microsoft.com/SQLServer/feedback/details/3058105/ssms-filter-is-missing-schema), “out of memory” errors that I can’t tie to any specific reason, occasional random disconnect errors that display a modal connection dialog that can’t be dismissed — have to terminate SSMS with Task Manager, which complains that it’s being blocked by something requiring user input (yeah duh, it’s a modal dialog that doesn’t go away no matter what I click).
And I can’t revert back to prior version…tried uninstalling/reinstalling a prior version and it won’t…it just won’t. Hoping next update comes soon!
Just to give an update…. We used the sessions DMV and found we had about 250k sessions stacked up due to some poor client side development. The .NET garbage collection was stacking up sessions and the developer had to force those to be closed on the data adapter. Hope this helps someone!
[…] Brent Ozar’s simple instructions here: SSMS 2016: It Just Runs More Awesomely (It’s not just for SSMS 2016). This makes tabs easier to read and pinning tabs is a great […]
Good tip Brent but this does not work in SSMS 17.9.
Changing the values to false has no effect on the tab text.
Clearly a bug.
Works fine, Will. Just need to close your existing tabs and start new ones. Enjoy!
[…] Query tab names are too long and unreadable, unless you configure SSMS tabs to show only file names […]
Has anybody seen SSMS Intellisense being blocking in tempdb on sys.* tables? The only way to avoid this, that I have found, is to disable Intellisense. For example SSMS IntelliSense is being blocked running this query:
SELECT tr.name AS [Name], tr.object_id AS [ID] FROM sys.triggers AS tr WHERE (tr.parent_class = 0) ORDER BY [Name] ASC
Our applications rely heavily on tempdb objects.