SQL Server – Windows 32 bit – SQL Server v2014

Background

Reviewing Memory Utilization on one of our boxes.

 

Troubleshooting

Windows Version

Control Panel

System

Image

Explanation
  1. Windows Server 2008 Enterprise

Performance Monitor

Metrics

Metrics – [ SQL Server Instance ] – Memory Manager

Image

Textual

  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

Metadata

@@version

Image
Textual

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)

 

Explanation
  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
        Link
    • X86
      • 32 bit OS

Engineering

In terms of Engineering, can we do more.

Microsoft

AWE

The “awe enabled” SQL Server feature is deprecated.
Link

Summary

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

Closing

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
#http://rosettacode.org/wiki/IsNumeric#PowerShell

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
#http://theessentialexchange.com/blogs/michael/archive/2008/11/25/getting-our-computername-in-a-powershell-script.aspx
$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];
}
else
{
	$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];

	}
	else
	{

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

		Write-Host $strLog;		

		return
	}
}

$numberofIterations = $numberofIterationsDefault;

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

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

	if ($isNumber)
	{

		$numberofIterations = $args[2];

	}
	else
	{

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

		Write-Host $strLog;		

		return
	}
}

#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
    http://technet.microsoft.com/en-us/magazine/ee872428.aspx
  2. PowerShell – Check Value for null
    http://www.thomasmaurer.ch/2010/07/powershell-check-variable-for-null/
  3. Determine if a number is Numeric
    http://rosettacode.org/wiki/IsNumeric#PowerShell
  4. Getting computer name in PowerShell
    http://theessentialexchange.com/blogs/michael/archive/2008/11/25/getting-our-computername-in-a-powershell-script.aspx
     

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.