I discovered this issue while working in SSMS on my Surface and I’m wondering if it’s just my Surface or if others have the same issue.

Here’s the problem.

1. Load a query plan from a .SQLPLAN file
2. Scroll the plan with the touch screen
3. SSMS crashes within a few seconds.

I’m interested to hear if anyone else has run into this so please feel free to comment and reach out to me on LinkedIn or twitter.

Also please you have this issue be sure to add to the  connect issue here: https://connect.microsoft.com/SQLServer/feedback/details/3130021/ssms-2016-crashes-with-touch-screen-scrolling-every-time

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”:

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

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.

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.

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

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.

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

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

Some bugs are earth shattering others are an annoyance and some just make you chuckle. Luckily this simple bug is not earth shattering or annoying.

Have you ever scripted an object from SQL Server Management Studio (SSMS)? It does a really good job. You get nice cleanly formatted scripts that start with USE statements to select the database. They even have some simple comments?

Have you ever written a Sequence? Turns out if you script one you’ll notice that Microsoft left you an extra surprise. Double USE statements. Does it matter much? No. Should they fix it? Yes. I noticed this behavior when sequences were first released and it still exists in the latest version of SSMS for SQL 2016 (13.0.1500.23) as of this posting.

To script an object, navigate to it in Object Explorer. Right click and choose any option in the “Script object as” submenu.

Here’s a sample object scripted:

USE [Billing]
GO

/****** Object:  Table [dbo].[appConfig]    Script Date: 9/27/2016 9:47:45 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[appConfig](
[ConfigType] [varchar](50) NULL,
[ConfigValue] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Notice the single USE statement at the top?

Now let’s look at a sequence.

USE [Billing]
GO

USE [Billing]
GO

/****** Object:  Sequence [dbo].[SeqCommodityID]    Script Date: 9/27/2016 9:37:37 PM ******/
CREATE SEQUENCE [dbo].[SeqCommodityID]
AS [bigint]
START WITH 17
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9223372036854775807
CACHE
GO

Two USE statements. Again, this isn’t doing any harm so I can imagine it’s really low on the priority; though, it does seem like an issue that they could fix in about 10 minutes. After two versions, I wonder if they will.