SQL 2016 SSMS memory leak?

Has SSMS (SQL Server Management Studio) been crashing on you? Have you been getting Out of Memory messages when attempting to run queries?

You may have noticed that this tends to occur after you’ve opened and closed 40 to 50 query windows. I’ve noticed this when I have had as little as 5 query windows open after having already opened and closed 30 or so other query windows.

Here’s what you end up seeing with the “System.OutOfMemoryException”:

SSMS 2016 OOM

After a simple search on Bing I found KB2874903, which states that SSMS is a 32 bit application and large queries can cause this message.

Taking a deeper look

In the world of current Windows operating systems there are 32 bit and 64 bit applications. If you’re a gamer you may recall all the memory problems Skyrim had on PC. They were all related to the executable being released as 32 bit. The 32 bit address space is limited to 2 GB on a 32 bit machine or up to 3 GB if the /3GB switch is used and the process is large address aware (LAA). On a 64 bit machine a 32 bit process is limited to 2 GB or 4 GB if the process is large address aware. A 64 bit process is limited to 8 TB. Quite a difference, right?

So why would a vendor release an application compiled as 32 bit? Simply put there are many more sales opportunities when you add 32 bit in the mix. So why limit your sales to just 64 bit users? The answer is you wouldn’t and they don’t.

What’s SSMS look like?

SSMS is a 32 bit process. This can be seen by attaching the process to VMMAP. Simply add everything up and you’ll see that the limit is 2GB of VAS (Virtual Address Space). Here’s one question though. Why isn’t SSMS compiled as LAA (Large Address Aware)? This would allow the process to consume the entire 4 GB 32bit address space on a 64 bit machine. While this wouldn’t resolve the issue it would give delay it by 100%.

VMMAP Ssms

So you’ve got plenty of memory but SSMS still gives OOM errors?

Yes that’s right. RAM doesn’t really have much to do with this message at all. This message is related to your virtual address space for the process. From the image above you can see that there is only 114 MB free of 2 GB. When a process allocates and deallocates bits in the VAS the area can become fragmented.

The little bits of white in this graph represent the free areas where new bits can go. Memory in Windows is allocated contiguously so if there isn’t a space large enough to fit your data it could result in an OOM error.

VMMAP Fragmentation

After opening and closing several query windows the free space declined and didn’t release.

You can see that the free memory has reduced to 67 MB.

vmmap after open and close of query windows

Examining the process with Perfmon

When using PERFMON to examine a possible memory leak you want to monitor the following counters in the Process object:

  • Handle Count – Shows the number of references to objects like files, windows, and registry keys
  • Thread Count – Shows the number of threads created
  • Private Bytes – Shows the total committed bytes for the process
  • Working Set – Shows the total RAM for the process

open windows memory not released

The PERFMON graph above shows that when a query window is opened the handles, threads, and private bytes all grow.

When the windows are closed, SSMS does not release the associated resources. This certainly looks like a leak or at least has symptoms of one.

After a short while of these OOM messages SSMS decided to crash.

ssms crash

After the crash, SSMS restarted and you can see that all of the memory has been released.

VMMAP after crash

And now the cycle begins again with the clock being restarted. After opening and closing enough query windows I’ll be back in the same Out of Memory boat.

Let’s hope that the team working on SSMS can look into this issue and resolve it quickly. I’m seeing quite a few people starting to complain about the apparent increase of these events since SSMS 2016 was released.

I’d be interested in hearing about any issues you’ve had with SSMS. Let me know.

 

*UPDATE 2016-10-07* – Microsoft reached out to me on Twitter saying that this has been fixed in the latest update for SSMS. I have tested SSMS release 16.4.1 (build 13.0.15900.1) and can confirm that the Virtual Bytes and Private Bytes appear to be cleaning up appropriately. Be sure to get the latest build from https://msdn.microsoft.com/library/mt238290.aspx to avoid any memory related crashes that may cause you to lose your work.

*UPDATE 2016-10-14* – I was working on a small query this morning and the issue happened. The query only returned a couple rows. I ended up closing a few windows and was able to run the query. I then ran a slightly larger result for the same query and found that after scrolling down I could no longer scroll up. I checked my Virtual Bytes and sure enough I had nearly 1.85 GB used. Following this SSMS crashed and everything was back to normal. @Microsoft, release 16.4.1 did not resolve all memory leaks in SSMS.

*UPDATE 2016-11-22* –  I’ve been using build 13.0.15900.1 for a few weeks now and I can say that it is much better. I’ve only had one Out of Memory (OOM) message in the past month. I’ll be upgrading to the latest today and will keep an eye on it. One of my co-workers has been getting a lot of OOM messages from SSMS and he’s on version 13.0.16000.28 (16.5).

 

7 thoughts on “SQL 2016 SSMS memory leak?

  1. Since you asked for “any issues”, here’s mine: SSMS won’t even launch. Just downloaded and installed, and all I get is a beep as repayment when I try and launch it. So far my googling hasn’t helped.

    Liked by 1 person

  2. This has been very frustrating and while the frequency has decreased, it’s still very gut wrenching when it happens. Is a 64 bit anywhere on the roadmap? I’ve been using dbForge as a scratch pad to avoid this only only using SSMS when I need to. But, I’d prefer to just stay in SSMS.

    Like

    • I would hope so. I suggested just compiling the exe as Large Address Aware to double the size of virtual address space for the application though I don’t believe that’s something they are considering. Hopefully a fix will be around quickly. If you get these frequently, capturing the process with procdump from sysinternals could be really helpful for the product group.

      Like

  3. I’m using SSMS version 13.0.16100.1 and still frequently get the OutOfMemory error. Very frustrating. When I get the error, I can close every query tab, the object explorer details tab and close every connection in Object Explorer, then open one query tab and run “select 1” and it’ll still get the error. Restarting SSMS is the only solution. I’ve had this problem using SSMS for SQL 2012 through to this SQL 2016 version.

    Like

  4. Still happens to me frequently on the latest version. Very productivity inhibitive to keep having to close and reopen as soon as queries stop running and throwing this error. Also the frequent crashes doesn’t help. At least the older versions didn’t crash. Puleeeeeease make a 64 bit version Microsoft! 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s