Video: SQL Server 2014 Express Actual Memory Limit

The official SQL Server 2014 Express edition memory limit is 1GB per instance – that is, strictly speaking, the buffer cache restriction. In 2010, the SQL Server MVP Pawel Potasinski confirmed that SQL Server 2008 R2 Express edition, which has the same 1GB memory limit, can actually use about 1400MB of memory. His post is in Polish, so you might have to use Google translate, but the script that he uses and the results are easy to interpret.

In a similar way the SQL Server Pro Kevin Kline confirmed that SQL Server 2012 Express Edition memory working set size can grow around 1.4-1.5GB.

Naturally, with the release of SQL Server 2014 Express edition, it is interesting to check what is the actual memory limit. We’ve used the same script that Kevin Kline posted on his blog:

SELECT
CASE
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
ORDER BY 2 DESC;
GO

— Assess amount of tables resident in buffer cache
SELECT
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()
GROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + ‘.’ + QUOTENAME(OBJECT_NAME(p.object_id))
ORDER BY [Object] DESC;
GO

— Fill up Express Edition’s buffer allocation
IF OBJECT_ID(N’dbo.test’, N’U’) IS NOT NULL
DROP TABLE dbo.test;
GO

CREATE TABLE dbo.test (col_a char(8000));
GO

INSERT INTO dbo.test (col_a)
SELECT REPLICATE(‘col_a’, 8000)
FROM sys.all_objects
WHERE is_ms_shipped = 1;

CHECKPOINT;
GO 100

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status=’VISIBLE ONLINE’

select cpu_count from sys.dm_os_sys_info

You can download the [tip label=”script” style=”1″ href=”https://www.netometer.com/downloads/SQL-Test.txt”]SQL-Test.txt [/tip] that we are using in the demo.

[raw]In the following short video, we demonstrate that the actual SQL Server 2014 Express memory limit is over 1600MB. In addition, you will see how the “1 Socket/4 Cores” restriction is applied when running SQL Server 2014 Express on a Virtual Machine.[/raw]

• How to Install SharePoint Foundation 2013 with SQL Server 2014 Express
• How to Install and Configure Remote Blob Storage in SharePoint 2013 and SQL Server 2014.
• How to Start and Configure SharePoint Foundation 2010 Search
• How to Install and Configure Search Server 2010 Express

Tweet #SQL2014 Follow @netometer