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

Thanks to NetoMeter Blog for their share.

Today I m gonna share with you, dear readers a new trick even generally tought as a basic one:)

What may seem obvious is actually more difficult to determine than you’d expect. Finding your version will depend on what operating system SharePoint is installed on and what build you are running, plus they all store the version information in separate places.

There are three places you can check for the version number: Control Panel Program and Features or Add and Remove Programs, Central Administration, or PowerShell. Not all of these will work depending on the SharePoint build, but one will certainly work for your environment.

Most of these examples will get you a build number and that build number will have to be looked up. At the bottom of the article is a list of build numbers and what version and patch level they relate to.

What version of Microsoft SharePoint 2007 you are running? MOSS or WSS?

Microsoft SharePoint 2007 comes in two main version MOSS or WSS3.0 (Microsoft Office SharePoint Server or Windows SharePoint Services 3.0), and there are two versions of MOSS: SharePoint Standard and SharePoint Enterprise.

Using Windows Server 2008 Programs and Features: On Windows Server 2008, go to Control Panel and click Programs and Features. On the left side, select View Installed Updates.

Using Windows Server 2003 Add/Remove Programs: On Windows Server 2003, go to Control Panel then click Add or Remove Programs and check Show Updates.

Using Central Administration to Find Your SharePoint Version

Go to Central Administration and click Site Actions then click Site Settings. In the Site Information box is the version, take that and look up what patch level you are at (found at the bottom of this article).

SharePoint Central Administration Site Settings

Are You Using the Standard or Enterprise version of MOSS?

Go to the Central Administration and click Operations then click Enable Enterprise Features. For Enterprise edition, the radio button will be disabled, otherwise it is Standard edition. This version pictured below is Enterprise. This is another roundabout way of telling if you have MOSS or WSS3.0 because WSS3.0 will nothave the Enable Enterprise Feature under the Operations category.

SharePoint Central Administration Enable Enterprise features

Are You Running SharePoint 2010 Version or SharePoint 2013 Version?

Microsoft SharePoint comes in two main flavors: Microsoft SharePoint Server and Microsoft SharePoint Foundation both have similar ways of determining what version you’re running. The easiest way to determine what version you have is to access to the server. From the server, you can use Central Administration’s Check Product and Patch Installation Status or the PowerShell.

Using the Central Administration Method:

Go to Central Administration and click Upgrade and Migration then click Check Product and Patch installation Status.

Microsoft SharePoint Server 2010

Microsoft SharePoint Foundation 2010

Microsoft SharePoint Server 2013

Microsoft SharePoint Foundation 2013

Using the PowerShell Method:

This command works in both SharePoint 2013 and 2010. You will need to run this in the SharePoint Management PowerShell:
(get-spfarm).BuildVersion

You will need to take the value it returns and look up what it returns and match it with the numbers found below (I recommend just hitting CTL F and copy/pasting your number in to find it faster):

WSS 3.0 and MOSS 2007

12.0.0.6679 WSS 3.0 or MOSS 2007 SP3 + June 2013 cumulative Update
12.0.0.6676 WSS 3.0 or MOSS 2007 SP3 + April 2013 cumulative Update
12.0.0.6673 WSS 3.0 or MOSS 2007 SP3 + February 2013 cumulative Update
12.0.0.6670 WSS 3.0 or MOSS 2007 SP3 + December 2012 cumulative Update
12.0.0.6668 WSS 3.0 or MOSS 2007 SP3 + October 2012 cumulative Update
12.0.0.6665 WSS 3.0 or MOSS 2007 SP3 + August 2012 cumulative Update
12.0.0.6662 WSS 3.0 or MOSS 2007 SP3 + June 2012 cumulative Update
12.0.0.6661 WSS 3.0 or MOSS 2007 SP3 + April 2012 cumulative Update
12.0.0.6658 WSS 3.0 or MOSS 2007 SP3 + February 2012 cumulative Update
12.0.0.6656 WSS 3.0 or MOSS 2007 SP3 + December 11 cumulative Update
12.0.0.6654 WSS 3.0 or MOSS 2007 SP3 + October 11 cumulative Update
12.0.0.6606 WSS 3.0 or MOSS 2007 SP3
12.0.0.6565 WSS 3.0 or MOSS 2007 SP2 + August 11 cumulative Update
12.0.0.6562 WSS 3.0 or MOSS 2007 SP2 + June 11 cumulative Update
12.0.0.6557 WSS 3.0 or MOSS 2007 SP2 + Apr 11 cumulative Update
12.0.0.6554 WSS 3.0 or MOSS 2007 SP2 + Feb 11 cumulative Update
12.0.0.6550 WSS 3.0 or MOSS 2007 SP2 + Dec 10 cumulative Update
12.0.0.6548 WSS 3.0 or MOSS 2007 SP2 + Oct 10 cumulative Update
12.0.0.6545 WSS 3.0 or MOSS 2007 SP2 + Aug 10 cumulative Update
12.0.0.6539 WSS 3.0 or MOSS 2007 SP2 + June 10 cumulative Update
12.0.0.6535 WSS 3.0 or MOSS 2007 SP2 + April 10 cumulative Update
12.0.0.6529 WSS 3.0 or MOSS 2007 SP2 + February 10 cumulative Update
12.0.0.6524 WSS 3.0 or MOSS 2007 SP2 + December 09 cumulative Update
12.0.0.6520 WSS 3.0 or MOSS 2007 SP2 + October 09 cumulative Update
12.0.0.6514 WSS 3.0 or MOSS 2007 SP2 + August 09 cumulative Update
12.0.0.6510 WSS 3.0 or MOSS 2007 SP2 + June 09 cumulative Update
12.0.0.6504 WSS 3.0 or MOSS 2007 SP2 + April 09 cumulative Update

SharePoint Foundation 2010

RTM 14.0.4762.1000
June 2010 CU 14.0.5114.5003
June 2010 CU 14.0.5114.5000
Aug 2010 CU 14.0.5123.5000
Oct 2010 CU 14.0.5128.5000
Dec 2010 CU 14.0.5130.5002
Feb 2011 CU 14.0.5136.5002
April 2011 CU 14.0.5138.5001
Service Pack 1 14.0.6029.1000
June 2011 CU 14.0.6106.5000
June 2011 CU 14.0.6106.5002
Aug 2011 CU 14.0.6109.5002
Oct 2011 CU 14.0.6112.5000
Dec 2011 CU 14.0.6114.5000
Feb 2012 CU 14.0.6117.5002
April 2012 CU 14.0.6120.5000
April 2012 CU 14.0.6120.5006
June 2012 CU 14.0.6123.5002
August 2012 CU 14.0.6126.5000
October 2012 CU 14.0.6129.5000
December 2012 CU 14.0.6131.5001

Microsoft SharePoint Server 2010

RTM 14.0.4762.1000
June 2010 CU 14.0.5114.5003
Aug 2010 CU 14.0.5123.5000
Oct 2010 CU 14.0.5128.2003
Dec 2010 CU 14.0.5130.5002
Feb 2011 CU 14.0.5136.5002
April 2011 CU 14.0.5138.5001
Service Pack 1 14.0.6029.1000
June 2011 CU 14.0.6106.5000
June 2011 CU 14.0.6106.5002
Aug 2011 CU 14.0.6109.5002
Oct 2011 CU 14.0.6112.5000
Dec 2011 CU 14.0.6114.5000
Feb 2012 CU 14.0.6117.5002
April 2012 CU 14.0.6120.5000
April 2012 CU 14.0.6120.5006
June 2012 CU 14.0.6123.5002
August 2012 CU 14.0.6126.5000
October 2012 CU 14.0.6129.5003
December 2012 CU 14.0.6131.5003

SharePoint Server and Foundation 2013

15.0.4517 June 2013 CU
15.0.4505 April 2013 CU
15.0.4481 March Update
15.0.4420 RTM

Orginal author: Matt Tucker

Share the passion for Azure, Office 365, Sharepoint...

Tuesday, November 11, 2014