SSMS 2016: It Just Runs More Awesomely

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.

SSMS tab name configuration

Step 1: SSMS tab name configuration

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 2: Put pinned tabs in a separate row

Step 2: Put 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:

Presto - named, pinned tabs in their own row

Presto – named, pinned tabs in their own row

Go get SSMS 2016. It just runs more awesomely.

Previous Post
Is your SAN’s cache killing tempdb?
Next Post
Getting Started With Oracle Week: Generating Test Data

69 Comments. Leave new

  • Anybody try using SSMS 2016 to connect to previous versions of SQL Server, namely 2014 or 2012? Any known gotchas?

    Reply
    • 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.

      Reply
    • 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.

      Reply
    • 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.

      Reply
    • Samuel Jones
      June 6, 2016 1:29 pm

      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

      Reply
  • 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.

    Reply
  • Step is also also available in SSMS 2012 and 2014.

    Reply
  • Joe Stefanelli
    June 3, 2016 2:26 pm

    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?

    Reply
    • Wait – why would you ever close SSMS?

      (I kiiid, I kiiid)

      Reply
    • Jeremy Garman
      June 3, 2016 3:50 pm

      Redgate Tab History…

      Reply
    • Varsham Papikian
      June 4, 2016 12:11 am

      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

      Reply
    • Franklin Cid
      June 8, 2016 4:11 pm

      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].

      Reply
  • Bryan Jones
    June 5, 2016 7:42 am

    Has anyone found a way to not have the recent files list be cluttered with items from AppData\Temp\ (or is this just me)?

    Reply
  • 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.

    Reply
    • The release version seems to be doing better for me so if you haven’t upgraded to the RTM version you might try that.

      Reply
  • Have you found the Live Query Statistics useful?

    Reply
  • 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” 🙁

    Reply
  • My favorite…
    Query Execution > SQL Server > Uncheck the box “Prompt to save unsaved T-SQL query windows on close”

    Reply
  • Can SSMS 2016 run with SSMS 2012 already installed on the same machine?

    Reply
  • 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.

    Reply
  • Marc Zellick
    June 9, 2016 10:57 am

    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.

    Reply
  • 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 🙁

    Reply
  • jonathan Jones
    June 14, 2016 8:47 pm

    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.

    Reply
  • 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

    Reply
    • Matt – no, I haven’t seen anything like that. You may try someone else’s machine.

      Reply
    • I see the same issue. Removing ApexSQL Refactor seemed to fix it.

      Reply
    • 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.

      Reply
    • 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

      Reply
    • I am still having this issue on my SP4 i7 16gb. SSMS 2014 is fine, 2016 lags bad.

      Reply
    • 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.

      Reply
    • 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,

      Reply
  • OH THAT IS SOOOOOO COOL!!! I love it! Thank you!!!!!

    Reply
  • 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.

    Reply
  • 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.

    Reply
    • 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!

      Reply
      • 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!

        Reply
  • […] 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 […]

    Reply
  • 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.

    Reply
  • […] Query tab names are too long and unreadable, unless you configure SSMS tabs to show only file names […]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}