THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Squishy Limits in SQL Server Express Edition

It's an old story you've probably heard before.  Provide a free version of your software product with strict limitations on performance or other specific capabilities so that folks can give it a try without risk, while you minimize the chance of cannibalizing sales of your commercial products.  Microsoft has take this strategy with SQL Server Express Edition, not only to increase adoption in the student market but also to counter the threat of open-source (i.e. free) relational databases like MySQL for entry-level applications.

One such limitation of SQL Server Express Edition is that it supports no more than 1GB of RAM for the instance.  Of course, you could have many Express Edition instances on a single Windows server, each with its own 1GB of RAM.

But what does that metric of 1GB of RAM actually mean?  The key thing to remember is that the restriction is for buffer cache.  Since SQL Server has many other caches, even when not counting the plan cache, there are plenty of other caches within SQL Server.  (Run a query against sys.dm_os_memory_clerks if you'd like to see some of the others).  Because only the buffer cache has the strict 1GB limitation, you can actually watch SQL Server Express Edition's memory working set size grow to around 1.4-1.5GB due to the other memory caches at play.

Pawel Potasinski, a SQL Server MVP from Poland (Twitter | Blog), once posted an interesting repro for this behavior:

-- Assess amount of databases resident in buffer cache

 WHEN database_id = 32767 THEN 'mssqlsystemresource'
 ELSE DB_NAME(database_id)
 END AS [Database],
 CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB in buffer cache] 
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id 
-- Assess amount of tables resident in buffer cache
 QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + '.' +
 QUOTENAME(OBJECT_NAME(p.object_id)) AS [Object],
 CONVERT(numeric(38,2),(8.0 / 1024) * COUNT(*)) AS [MB In buffer cache] 
FROM sys.dm_os_buffer_descriptors AS d 
 INNER JOIN sys.allocation_units AS u ON d.allocation_unit_id = u.allocation_unit_id 
 INNER JOIN sys.partitions AS p ON (u.type IN (1,3) AND u.container_id = p.hobt_id) OR (u.type = 2 AND u.container_id = p.partition_id) 
WHERE d.database_id = DB_ID() 
-- Fill up Express Edition's buffer allocation
 DROP TABLE dbo.test;
CREATE TABLE dbo.test (col_a char(8000));
INSERT INTO dbo.test (col_a)
 SELECT REPLICATE('col_a', 8000)
 FROM sys.all_objects 
 WHERE is_ms_shipped = 1;
GO 100

 The bottom line for the hard memory limit of SQL Server Express Edition is "Yes, it's limited.  But it's a squishy limit. Not a hard limit."

Although your mileage may vary, I'd bet a dollar that you'll find more than 1GB in the active working set for your instance of SQL Server Express Edition.  I am curious, however, if you're seeing much variation between versions and even service packs of SQL Server?  Let me know if you try this out on more than one version and/or service pack level of SQL Server.  Did it change much between versions?  Let me know!



-Follow me on Twitter!

Google Author


Published Thursday, March 28, 2013 8:19 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



ALZDBA said:

March 28, 2013 1:51 PM

KKline said:

Thanks Johan! Great addition to the discussion.  -Kev

April 1, 2013 7:44 AM

Dean said:

Almost the same with SQL Server 2014 Express edition - the actual limit is 1,609MB.

BTW, we've recorded the result in a short video:


August 14, 2014 11:35 AM

KKline said:

Nice, Dean! Thanks for sharing. -Kev

August 18, 2014 7:48 AM

afatac said:

We have been using MS SQL Express 2008R2 with a digital asset management software system. Though only metadata and configuration settings are written to database, we are always concern about the 1GB limit.

Is there a way to monitor this usage of buffer cache over a period of time, so that we can decide if we need to upgrade to Standard version?

September 9, 2014 2:28 AM

Leave a Comment


About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS,

This Blog



Privacy Statement