Memory + CPU support for all Windows Server versions and editions

Some weeks ago I outlined my Memory + CPU support compact brief for SQL Server, and now it is time for Windows Server. Undoubtedly, when we are sizing a database server not only take a look at the hardware support for SQL Server but also for Windows Server. Here is the info you might need to have it at hand and make a good use of it. If any of you want to get closer to related topics you can check out Windows Server – Sockets, Logical Processors, Symmetric Multi Threading and Windows Server scalability and more!

Physical Memory Limits: Windows Server 2016

The following table specifies the limits on physical memory for Windows Server 2016. 

Version	Memory	CPU
Windows Server 2016 Datacenter	24 TB	64 CPU. Unlimited cores
Windows Server 2016 Standard	24 TB	64 CPU. Unlimited cores

Physical Memory Limits: Windows Server 2012 (R2)
The following table specifies the limits on physical memory for Windows Server 2012. Windows Server 2012 is available only in X64 editions.

Version	Memory	CPU
Windows Server 2012 Datacenter	4 TB	64 CPU or 640 logical processors (or 320 with Hyper-V Role)
Windows Server 2012 Standard	4 TB	64 CPU or 640 logical processors (or 320 with Hyper-V Role)
Windows Server 2012 Essentials	64 GB	2 CPU
Windows Server 2012 Foundation	32 GB	1 CPU
Windows Storage Server 2012 Workgroup	32 GB
Windows Storage Server 2012 Standard	4 TB
Hyper-V Server 2012	4 TB

Physical Memory Limits: Windows Server 2008 R2

The following table specifies the limits on physical memory for Windows Server 2008 R2. Windows Server 2008 R2 is available only in 64-bit editions. Windows Server 2008 R2 for Itanium-Based Systems support up to 2TB.

Version	Memory	CPU
Windows Server 2008 R2 Datacenter	2 TB	64 CPU or 256 logical processors (or 64 with Hyper-V Role )
Windows Server 2008 R2 Enterprise	2 TB	8 CPU or 256 logical processors (or 64 with Hyper-V Role )
Windows Server 2008 R2 Foundation	8 GB
Windows Server 2008 R2 Standard	32 GB	4 CPÛ or 256 logical processors (or 64 with Hyper-V Role )
Windows HPC Server 2008 R2	128 GB
Windows Web Server 2008 R2	32 GB	4 CPÛ or 256 logical processors (or 64 with Hyper-V Role )

Physical Memory Limits: Windows Server 2008

The following table specifies the limits on physical memory for Windows Server 2008. Limits greater than 4 GB for 32-bit Windows assume that PAE is enabled.

	Memory		CPU
Version	X86	X64	x86 (SP2)	x64 (SP2)
Windows Server 2008 Datacenter	64 GB	1 TB	32 CPU or 32 logical processors	32 CPU or 64 logical processors (or 24 with Hyper-V)
Windows Server 2008 Enterprise	64 GB	1 TB	8 CPU or 32 logical processors	8 CPU or 64 logical processors(or 24 with Hyper-V)
Windows Server 2008 HPC Edition		128GB
Windows Server 2008 Standard	4 GB	32 GB	4 CPU or 32 logical processors	4 CPU or 64 logical processors(or 24 with Hyper-V)
Windows Small Business Server 2008	4 GB	32 GB
Windows Web Server 2008	4 GB	32 GB

Windows Server 2008 for Itanium-Based Systems support up to 2TB
SP1+ Hyper-V Enabled support up to 16 logical processors
SP1+ Hyper-V Enabled + KB956710 installed support up to 24 logical processors

Physical Memory Limits: Windows Home Server

Windows Home Server is available only in a 32-bit edition. The physical memory limit is 4 GB.

Physical Memory Limits: Windows Server 2003 R2

The following table specifies the limits on physical memory for Windows Server 2003 R2. Limits over 4 GB for 32-bit Windows assume that PAE is enabled.

Version	Limit on X86	Limit on X64
Windows Server 2003 R2 Datacenter Edition	64 GB (16 GB with 4GT)	1 TB
Windows Server 2003 R2 Enterprise Edition	64 GB (16 GB with 4GT)	1 TB
Windows Server 2003 R2 Standard Edition	4 GB	32 GB

Physical Memory Limits: Windows Server 2003 with Service Pack 2 (SP2)

The following table specifies the limits on physical memory for Windows Server 2003 with Service Pack 2 (SP2). Limits over 4 GB for 32-bit Windows assume that PAE is enabled.

Version	X86	X64	IA64
Windows Server 2003 with Service Pack 2 (SP2), Datacenter Edition	64 GB(16 GB with 4GT)	1 TB	2 TB
Windows Server 2003 with Service Pack 2 (SP2), Enterprise Edition	64 GB(16 GB with 4GT)	1 TB	2 TB
Windows Server 2003 with Service Pack 2 (SP2), Standard Edition	4 GB	32 GB

Physical Memory Limits: Windows Server 2003 with Service Pack 1 (SP1)

The following table specifies the limits on physical memory for Windows Server 2003 with Service Pack 1 (SP1). Limits over 4 GB for 32-bit Windows assume that PAE is enabled.

Version	X86	X64	IA64
Windows Server 2003 with Service Pack 1 (SP1), Datacenter Edition	64 GB(16 GB with 4GT)	1 TB	1 TB
Windows Server 2003 with Service Pack 1 (SP1), Enterprise Edition	64 GB(16 GB with 4GT)	1 TB	1 TB
Windows Server 2003 with Service Pack 1 (SP1), Standard Edition	4 GB	32 GB

Physical Memory Limits: Windows Server 2003

The following table specifies the limits on physical memory for Windows Server 2003. Limits over 4 GB for 32-bit Windows assume that PAE is enabled.

Version	X86	IA64
Windows Server 2003, Datacenter Edition	64 GB(16 GB with 4GT)	512 GB
Windows Server 2003, Enterprise Edition	64 GB(16 GB with 4GT)	512 GB
Windows Server 2003, Standard Edition	4 GB
Windows Server 2003, Web Edition	2 GB
Windows Small Business Server 2003	4 GB
Windows Compute Cluster Server 2003		32 GB
Windows Storage Server 2003, Enterprise Edition	8 GB
Windows Storage Server 2003	4 GB

 

Windows Server 2003: The number of processors and the amount of physical RAM that are supported

The following table compares the number of processors and the amount of physical RAM that are supported by the x64-based versions of Windows Server 2003 and by Windows XP Professional x64 Edition to those that are supported by the 32-bit versions.

Operating system	Number of processors	Physical RAM
Microsoft Windows Server 2003, Standard Edition	4	4 gigabytes (GB)
Microsoft Windows Server 2003, Standard x64 Edition	4	32 GB
Microsoft Windows Server 2003, Enterprise Edition	8	64 GB
Microsoft Windows Server 2003, Enterprise x64 Edition	8	1 terabyte
Microsoft Windows Server 2003, Datacenter Edition	32	64 GB
Microsoft Windows Server 2003, Datacenter x64 Edition	64	1 terabyte
Microsoft Windows XP Professional	2	4 GB
Microsoft Windows XP Professional x64 Edition	2	128 GB

Notes 

·         x86-based versions of Windows Server 2003 that are running on a computer that uses a multicore processor or a hyper-threading processor support a maximum number of 32 logical processors.

·         x64-based versions of Windows Server 2003 that are running on a computer that uses a multicore processor or a hyper-threading processor support a maximum number of 64 logical processors.

Memory allocation settings

The following table compares the memory allocation settings that are supported by the x64-based versions of Windows Server 2003 and Windows XP Professional x64 Edition to those that are supported by the 32-bit versions. Collapse this tableExpand this table

Memory allocation settings	32-bit versions	x64-based versions
Total amount of virtual address space	4 GB	16 terabytes
Amount of virtual address space per 32-bit process	2 GB (3 GB if the /3GB switch is added to the Boot.ini file)	2 GB (4 GB if the /LARGEADDRESSAWARE option is used)
Amount of virtual address space for the 64-bit processes	Not applicable	8 terabytes
Amount of paged pool memory	470 megabytes (MB)	128 GB
Amount of non-paged pool memory	256 MB	128 GB
Size of system cache	1 GB	1 terabyte

Physical Memory Limits: Windows 8

The following table specifies the limits on physical memory for Windows 8.

Version	Limit on X86	Limit on X64
Windows 8 Enterprise	4 GB	512 GB
Windows 8 Professional	4 GB	512 GB
Windows 8	4 GB	128 GB

Physical Memory Limits: Windows 7

The following table specifies the limits on physical memory for Windows 7.

Version	Limit on X86	Limit on X64
Windows 7 Ultimate	4 GB	192 GB
Windows 7 Enterprise	4 GB	192 GB
Windows 7 Professional	4 GB	192 GB
Windows 7 Home Premium	4 GB	16 GB
Windows 7 Home Basic	4 GB	8 GB
Windows 7 Starter	2 GB	N/A

Physical Memory Limits: Windows Vista

The following table specifies the limits on physical memory for Windows Vista.

Version	Limit on X86	Limit on X64
Windows Vista Ultimate	4 GB	128 GB
Windows Vista Enterprise	4 GB	128 GB
Windows Vista Business	4 GB	128 GB
Windows Vista Home Premium	4 GB	16 GB
Windows Vista Home Basic	4 GB	8 GB
Windows Vista Starter	1 GB

Physical Memory Limits: Windows XP

The following table specifies the limits on physical memory for Windows XP.

Version	Limit on X86	Limit on X64	Limit on IA64
Windows XP	4 GB	128 GB	128 GB (not supported)
Windows XP Starter Edition	512 MB	N/A	N/A

Always On: Trying out Read-Only intent connections to secondary replicas

SQL Server Always On Availability Group enables us to connect Read-Only workloads to secondary replicas automatically by using differents ways. For instance, to connect .NET applications to secondary replicas, we must also include "ApplicationIntent=ReadOnly" besides the AG Listener Virtual Name in the connection string. More specifically, after configuring the Read-Only Routing list, this would be the connection string if we had an Availability Group with a Listener called "AGLISTENER" (in these examples it is used Windows Authentication).

Server=tcp:AGLISTENER,1433;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True

But that is not all, it is also possible to establish connection to secondary replicas via SQLCMD tool by using the new parameter -K (and optionally -M for faster detection of and connection to the active server). If -K is not specified, the sqlcmd utility will not support connectivity to a secondary replica. So, this would be a simple example of how to use SQLCMD with -K and -M parameters. I couldn't have been simpler.

Sqlcmd -S AGLISTENER  -E  -K ReadOnly -M

And last but not least, we can also do it by using SSMS Connection Dialog window by using “ApplicationIntent=ReadOnly” in the "Additional Connection Parameter" tab. I also wrote another post about it with more details, take a look at it.

To sum up, these are the basic ways to try out Read-Only intent connections to secondary replicas, the first way is ideal for developers whereas the second and third if you are working as a DBA.
That is all for now. Let me know any remarks you may have. Stay tuned.

Implementing dynamic PIVOT in SQL Server

It is well known that PIVOT is one of the new features included in SQL Server 2005 which allows to convert rows into columns. But what happens if we wanted to make it dynamic going beyond limits respect to the number of columns? Today in this post I am going to show you how to do it (applies to SQL Server 2005 through SQL Server 2017). To begin with, we are going to use the following example to analyse some data, which will be pivoted shortly after.

USE AdventureWorks 
GO
SELECT CustomerID,YEAR(DueDate) [Year], TotalDue 
FROM Sales.SalesOrderHeader
ORDER BY CustomerID

According to the result set, there are many sales per customer between the years 2001 and 2004.



In order to pivot the 'TotalDue' per 'Year' we do need to indicate each year in the PIVOT clause. In this case we do also need to know the years which will be taken into account. For instance, this query will pivot 'TotalDue' for the years 2001, 2002, 2003, and 2004.

SELECT CustomerID, [2001] AS '2001', [2002] AS '2002', [2003] AS '2003', [2004]  AS '2004'
FROM (
      SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader
     ) pvt
PIVOT (SUM(TotalDue) FOR [Year] IN ([2001],[2002],[2003],[2004])) AS Child
ORDER BY CustomerID

Having successfully executed the query, we will get the following nice result:


Up to now, everything seems to be perfect. Nevertheless, what's going on if we wanted to pivot for many more years? Obviously, we would have to deal with a big limitation at first glance since we will need to add the years manually inside the query, but it does not make sense for a real business case. As a result, we can say that PIVOT is not scalable, I mean that PIVOT is not 'dynamic' by design. Luckily, the purpose of this post is to show how to implement an algorithm to simulate a dynamic PIVOT in SQL Server by using the native PIVOT clause and sp_executesql.

The algorithm is quite simple and does not deserve major explanation, however, I am going to say that this will only create a query dynamically to pivot 'TotalDue' by adding all years inside, and finally the output code will be executed via sp_executesql.

DECLARE @TableYears AS TABLE([Year] INT NOT NULL)
DECLARE @Year INT, @YearsPVT NVARCHAR(MAX) 
INSERT INTO @TableYears  SELECT DISTINCT YEAR(DueDate) AS [Year] FROM  Sales.SalesOrderHeader 
SET @Year = (SELECT MIN([Year]) FROM @TableYears)
SET @YearsPVT=N''
WHILE @Year IS NOT NULL
BEGIN
  SET @YearsPVT = @YearsPVT + N',['+ CONVERT(NVARCHAR(10),@Year) + N']'
  SET @Year = (SELECT MIN([Year]) FROM @TableYears WHERE [Year]>@Year)
END
SET @YearsPVT = SUBSTRING(@YearsPVT,2,LEN(@YearsPVT))
PRINT @YearsPVT
DECLARE @SQL NVARCHAR(MAX)
 SET @SQL = N'SELECT *
            FROM ( 
                SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader
                ) pvt
            PIVOT (SUM(TotalDue) FOR [Year] IN (' + @YearsPVT + ')) AS Child
            ORDER by CustomerID'
 
EXECUTE sp_executesql @SQL

Now I am going to illustrate another example by using data from Northwind database.

SELECT P.ProductID, C.CategoryName, OD.UnitPrice * OD.Quantity AS TotalAmount  
FROM Products P 
    INNER JOIN dbo.[Order Details] OD
ON P.ProductID=OD.ProductID
   INNER JOIN Categories C
ON C.CategoryID=P.CategoryID

Here the simple result without PIVOT.

Using PIVOT in its old-fashioned way:

SELECT ProductID, [Beverages], [Condiments], [Confections], [Dairy Products], 
       [Grains/Cereals], [Meat/Poultry],[Produce],[Seafood] 
FROM 
(
    SELECT P.ProductID, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto
    FROM Products P 
        INNER JOIN dbo.[Order Details] OD
            ON P.ProductID=OD.ProductID
        INNER JOIN Categories C
            on C.CategoryID=P.CategoryID
) PIV
PIVOT (SUM(Monto) FOR  CategoryName IN ([Beverages], [Condiments], [Confections], [Dairy Products], 
       [Grains/Cereals], [Meat/Poultry],[Produce],[Seafood])) AS Child

Having executed the code above, we will get this result pivoted.



Finally, using dynamic PIVOT the result will be the same.

DECLARE @CatPVT AS NVARCHAR(MAX), @Categorias AS VARCHAR(20)
DECLARE @CatID INT 
SET @CatID=(SELECT MIN(CategoryID) FROM Categories)
SET @Categorias = ( SELECT CategoryName FROM Categories WHERE CategoryID = @CatID)
SET @CatPVT = N''
WHILE @Categorias IS NOT NULL
BEGIN
  SET @CatPVT = @CatPVT + N',['+ @Categorias +N']'
  SET @Categorias = (SELECT TOP(1) CategoryName 
                     FROM Categories WHERE CategoryID > @CatID 
                     ORDER BY CategoryID ASC)
  SET @CatID=(SELECT MIN(CategoryID) FROM Categories WHERE Categoryname=@Categorias)
END
print @CatPVT
SET @CatPVT = SUBSTRING(@CatPVT, 2, LEN(@CatPVT))
 
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = N'SELECT *  
            FROM (SELECT P.ProductID, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto
                  FROM Products P 
                    INNER JOIN dbo.[Order Details] OD
                        ON P.ProductID=OD.ProductID
                    INNER JOIN Categories C
                    ON C.CategoryID=P.CategoryID
            ) PIV
            PIVOT (SUM(Monto) FOR  CategoryName IN ('+ @CatPVT  + ')) AS Child'
 
EXEC sp_executesql @sql

As you have seen, dynamic PIVOT is truly useful for a real business case. Therefore, I hope you make the most out of this algorithm. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.

Memory + CPU support for all SQL Server versions and editions

It is by no means uncommon to think that every DBA should have a list like this following one in order to have clear some aspects before installing/implementing new database environments. It is a compact brief of what different versions and editions of SQL Server support in terms of memory and CPU resources. Consequently, I share with you my resume, and I hope you find it useful. Let me know any remarks you may have.

SQL Server 2016/2017				SQL Server 2014
SQL Server edition	Maximum memory supported	Maximum Compute Capacity		SQL Server edition	Maximum memory supported	Maximum Compute Capacity
Enterprise	OS max	OS max		Enterprise	OS max	OS max
Standard	128 GB	4  CPU or 24 cores		Business Intelligence	128 GB	4 CPU or 16 cores
Web	64 GB	4 CPU or 16 cores		Standard	128 GB	4  CPU or 16 cores
Express	1410 MB	1 CPU or 4 cores		Web	64 GB	4 CPU or 16 cores
Express with Advanced Services	1410 MB	1 CPU or 4 cores		Express	1 GB	1 CPU or 4 cores
				Express with Tools	1 GB	1 CPU or 4 cores
SQL Server 2012				Express with Advanced Services	1 GB	1 CPU or 4 cores
SQL Server edition	Maximum memory supported	Maximum Compute Capacity
Enterprise	OS max	OS max		SQL Server 2008 R2
Business Intelligence	64 GB	4 CPU or 16 cores		SQL Server edition	Maximum memory supported	Maximum Compute Capacity
Standard	64 GB	4  CPU or 16 cores		Datacenter	OS max	OS max (64 CPU or 256 logical)
Web	64 GB	4 CPU or 16 cores		Enterprise	2 TB	8 ( or 256 Logical CPU)
Express	1 GB	1 CPU or 4 cores		Developer	OS max	OS max
Express with Tools	1 GB	1 CPU or 4 cores		Standard	64 GB	4
Express with Advanced Services	1 GB	1 CPU or 4 cores		Web	64 GB	4
				Workgroup	4 GB (64-bit), OS max (32-bit)	2
SQL Server 2008				Express	1 GB	1
SQL Server edition	Maximum memory supported	Maximum Compute Capacity		Express with Tools	1 GB	1
Enterprise	OS max	OS max (8 CPU or 64 Logical CPU)		Express with Advanced Services	1 GB (4 GB for RS)	1
Developer	OS max	OS max
Standard	OS max	4		SQL Server 2005
Web	OS max	4		SQL Server edition	Maximum memory supported	Maximum Compute Capacity
Workgroup	4 GB (64-bit), OS max (32-bit)	2		Enterprise Edition	OS max	OS max
Express	1 GB	1		Developer Edition	OS max (32-bit), 32 TB (64-bit)	OS max
Express with Tools	1 GB	1		Standard Edition	OS max, 32 TB (64-bit)	4
Express with Advanced Services	1 GB	1		Workgroup Edition	3 GB(32-bit)	2 (32-bit)
				Express Edition	1 GB(32-bit)	1 (32-bit)
				Evaluation Edition	OS max	OS max

SQLServer Buffer Manager Buffer cache hit ratio, Page Life,Lazy Write/sec, Checkpoint Pages/sec

Without any shadow of a doubt, one of the procedures to follow in order to monitor and diagnose performance issues of a database server is to analyse meaningful performance counters related to SQL Server. As a Database Administrator, we need to get used to dealing with this sorts of problems so it truly helpful to know how to analyse and interpret them by contrasting with other windows performance counters.

To begin with, we can analyse SQLServer Buffer cache hit ratio performance counter which gives us the percentage of cache memory usage. This percentage should mostly be above 99% for OLTP database servers. Therefore, nowadays 4GB or 8GB of RAM is not good enough for the vast majority of them. The knock-on effect of this lack of RAM is that there will be a huge increase on the I/O disk activity as a result of using much more paging (data pages are moving in and out from memory very frequently because there is not enough space to locate new ones). This will also cause a huge disk queue which will keep disk subsystem busy, consequently, it will impact directly on the database performance detrimentally. In this context, we also need to look into other SQL Server performance counters like SQLServer: Buffer Manager: Page Life Expectancy, SQLServer: Buffer Manager: Lazy Write/sec, and SQLServer: Buffer Manager: Checkpoint Pages/sec. 

As I mentioned before, there are other useful performance counters to diagnose and tune cache memory issues like Buffer Manager: Page Life Expectancy which indicates the time in seconds that a page has been inside of memory. This time should mostly be above 300 seconds, that is, 5 minutes. So, values below it should be considered as an alert, which means that SQL Server is under high memory pressure because bad-written queries may be using a lot. Clearly, the final solution is not to add more memory but identify and optimise those problematic queries. Only after that may you opt to add memory.

The other performance counter to check is SQLServer: Buffer Manager: Lazy Write/sec which gives the quantity of pages moved out per second from memory. Not only could it mean a lack of memory, but also there are copious amounts of Checkpoints which are not good at all because it will cause recompilation of stored procedures at the same time.The value of this counter should mostly be below 20. So, if you notice this value above 20, you may need to check SQLServer: Buffer Manager: Checkpoint Pages/sec counter as well. Checkpoints move out all pages from memory to disk.

Finally, keep in mind that it really important to do diagnostic tasks regularly which allow to take preventive actions so as to improve the whole performance over time. I do hope you find this post helpful. Let me know any remarks you may have. Thanks for reading. Stay tuned.