SQL Server – Windows 32 bit – SQL Server v2014


Reviewing Memory Utilization on one of our boxes.



Windows Version

Control Panel



  1. Windows Server 2008 Enterprise

Performance Monitor


Metrics – [ SQL Server Instance ] – Memory Manager



  1. Stolen Server Manager ( KB ) :- 448,400.000
  2. Target Server Memory ( KB ) :- 1,531,904.00
  3. Total Server Memory ( KB ) :- 1,516,648.00


SQL Server




Microsoft SQL Server 2014 (SP2-CU6) (KB4019094) – 12.0.5553.0 (Intel X86)
Jun 28 2017 14:05:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing on Windows NT 6.0 <X86> (Build 6002: Service Pack 2) (VM)


  1. We are running MS SQL Server 2014 / SP2 ( CU-6 )
  2. Enterprise Edition: Core-based Licensing on Windows NT 6.0 <X86>
    • Windows NT 6.0
      • Windows Server 2008
    • X86
      • 32 bit OS


In terms of Engineering, can we do more.



The “awe enabled” SQL Server feature is deprecated.


In Microsoft SQL Server 2008 R2, the configuration option, awe enabled, is deprecated. In the next version, Microsoft SQL Server 2012, this configuration option and the feature that uses this configuration option are removed from the product.

Therefore, when you upgrade from SQL Server 2008 R2 to the next version of SQL Server, you cannot use more memory than what the virtual address space limits in 32-bit instances of SQL Server. If you must have more memory for this instance of SQL Server, then you have to migrate to a 64-bit instance of SQL Server.

Operating System & SQL Server v2012+ Max Memory

SQL Server and operating system settings Maximum amount of memory used by SQL Server
32-bit SQL Server on 32-bit OS 2 GB
32-bit SQL Server on 32-bit OS with /3G boot option 3 GB
32-bit SQL Server on 64-bit OS 4 GB


As always keep in mind what your end goal is and what engineering constraints are holding you back.

Microsoft – Performance Counters \ Task Manager (Using PowerShell)

Occasionally QA Tester calls in and says I need to get CPU utilization, memory utilization, etc for the next hour or so.

What to do, act a schmuck:

  1. Connect (remote desktop) to said server
  2. Launch Task Manager
  3. Read %CPU utilization for the next hour or so

Or put in a bit of effort

  1. Write a script that remotely reads the number
  2. Or Launch Performance Manager, collect the number into a flat file or database, and review & share the numbers later on

Here is sample script (written in PowerShell):

Nothing original as bits and pieces are stitched together from various web sacrifices:

#Determine if a string is numeric

function isNumeric($value)

	$x2 = 0;
	$isNumber = [System.Int32]::TryParse($value, [ref] ($x2));

	return ($isNumber);


$NumberofCommandLineArguments = 0;
$computerNameDefault = "";
$computerName = "";

$sampleIntervalDefault = 60;
$sampleInterval = 0;

$numberofIterationsDefault = 60;
$numberofIterations =5;

$IsNumber = 0;

$strLog = "";

#set defaults
$sampleInterval = $sampleIntervalDefault;

#Get Number of Command Line arguments
#The command line arguments are exposed via $args
#So get the number of such arguments, get the length of $args
$NumberofCommandLineArguments = $args.Length;

#Display the nunber of arguments
Write-Host "Num Args:" $NumberofCommandLineArguments;

#Iterate the argument list

foreach ($arg in $args)
  Write-Host "Arg: $arg";

#Getting Our Computername in a PowerShell Script
$computerNameDefault = get-content env:computername

#if at least one argument passed in, then use 
#argument 1 as computer name
if ($NumberofCommandLineArguments >0)
	$computerName = $args[0];
	$computerName = $computerNameDefault;

#if at least two arguments passed in, then use 
#argument 2 (args[1]) as sample interval
$sampleInterval = $sampleIntervalDefault;

#parsing for sample interval
if ($NumberofCommandLineArguments -gt 1)

	$IsNumber = isNumeric($args[1]);

	if ($isNumber)

		$sampleInterval = $args[1];


		$strLog = "Argument 2 (" + $args[1]
                           + ") is not numeric - Exiting App!";

		Write-Host $strLog;		


$numberofIterations = $numberofIterationsDefault;

#parsing for number of Iterations
if ($NumberofCommandLineArguments -gt 2)

	$IsNumber = isNumeric($args[2]);

	if ($isNumber)

		$numberofIterations = $args[2];


		$strLog = "Argument 3 (" + $args[2]
                     + ") is not numeric - Exiting App!";

		Write-Host $strLog;		


#say computer name
Write-Host "Computer Name: $computerName";

#say sample interval
Write-Host "Sample Interval: $sampleInterval";

#say sample interval
Write-Host "Number of Iterations: $numberofIterations";

#get performance data \Processor(_Total)\% Processor Time
Get-counter -ComputerName $computerName -Counter "\Processor(_Total)\% Processor Time" -SampleInterval $sampleInterval -MaxSamples $numberofIterations;

Recommended Reading:

  1. Use Windows PowerShell to Monitor System Performance
  2. PowerShell – Check Value for null
  3. Determine if a number is Numeric
  4. Getting computer name in PowerShell

BTW, get yourself a copy of PowerGUI Script Editor  (http://powergui.org/downloads.jspa).  If you took the time to read a bit about PowerShell you will get that time back.  The PowerGUI:

  1.  is free
  2.  comes with its own lightweight debugger
  3. has code highlighter.