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.