{"id":1451,"date":"2014-08-12T07:58:04","date_gmt":"2014-08-12T14:58:04","guid":{"rendered":"https:\/\/www.netometer.com\/blog\/?p=1451"},"modified":"2025-10-21T17:46:03","modified_gmt":"2025-10-22T00:46:03","slug":"video-sql-server-2014-express-actual-memory-limit","status":"publish","type":"post","link":"https:\/\/www.netometer.com\/blog\/?p=1451","title":{"rendered":"Video: SQL Server 2014 Express Actual Memory Limit"},"content":{"rendered":"

 <\/p>\n

Free Video :<\/strong>Notes from the Field:
\nDon’t be surprised if your SQL Server 2014 Express instance uses more than 1600MB memory
\n<\/strong><\/div>\n

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<\/a> 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. <\/p>\n

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

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:<\/p>\n

SELECT
\n CASE
\n WHEN database_id = 32767 THEN ‘mssqlsystemresource’
\n ELSE DB_NAME(database_id)
\n END AS [Database],
\n CONVERT(numeric(38,2),(8.0 \/ 1024) * COUNT(*)) AS [MB in buffer cache]
\nFROM sys.dm_os_buffer_descriptors
\nGROUP BY database_id
\nORDER BY 2 DESC;
\nGO<\/p>\n

— Assess amount of tables resident in buffer cache
\nSELECT
\n QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + ‘.’ +
\n QUOTENAME(OBJECT_NAME(p.object_id)) AS [Object],
\n CONVERT(numeric(38,2),(8.0 \/ 1024) * COUNT(*)) AS [MB In buffer cache]
\nFROM sys.dm_os_buffer_descriptors AS d
\n INNER JOIN sys.allocation_units AS u ON d.allocation_unit_id = u.allocation_unit_id
\n 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)
\nWHERE d.database_id = DB_ID()
\nGROUP BY QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + ‘.’ + QUOTENAME(OBJECT_NAME(p.object_id))
\nORDER BY [Object] DESC;
\nGO<\/p>\n

— Fill up Express Edition’s buffer allocation
\nIF OBJECT_ID(N’dbo.test’, N’U’) IS NOT NULL
\n DROP TABLE dbo.test;
\nGO<\/p>\n

CREATE TABLE dbo.test (col_a char(8000));
\nGO<\/p>\n

INSERT INTO dbo.test (col_a)
\n SELECT REPLICATE(‘col_a’, 8000)
\n FROM sys.all_objects
\n WHERE is_ms_shipped = 1;<\/p>\n

CHECKPOINT;
\nGO 100<\/p>\n

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status=’VISIBLE ONLINE’<\/p>\n

select cpu_count from sys.dm_os_sys_info<\/p>\n

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

[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]<\/p>\n

\n\t\t\t
\n\t\t\t\t
\n\t\t\t\t<\/span>\n\t\t\t\tSQL 2014 Express Memory Limit More than 1GB<\/span>\n<\/div>\n\t\t<\/div>\n