SQL Server 2014 Express Actual Memory Limit

 In this post I'm gonna share a very intersting discovery That i just realise once working with a new installation for demo of SQL Express 2014.

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 scriptSQL-Test.txt  that we are using in the demo.

Thanks to NetoMeter Blog for their share.