NetoMeter - ScreenCasts and Video How To's
NetoMeter BlogFree Screencasts SharePoint 2010Video: SQL Server 2014 Express Actual Memory Limit

Video: SQL Server 2014 Express Actual Memory Limit

 

Free Video :Notes from the Field:
Don’t be surprised if your SQL Server 2014 Express instance uses more than 1600MB memory

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 SQl Server 2014 Express Memory Limit that we are using in the demo.

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.


Comments are closed.

RSS Latest Blog Posts

Netometer@Twitter