Memory Fundamentals for SQL Server - Virtual Memory, /3GB Switch

Just a few days back I was asked a very basic question related to AWE and /3GB switch.I was surprised being a DBA I was unable to explain the concept and relate the terms by proper choice of words. That's when I realized how important it is to have this cleared prior to troubleshooting SQL server. You will be able to find lots of good books & blogs relating Windows & SQL Server memory management. The topic is quite vast to be covered in just a few blogs. I shall be giving the references of a few resources used in the last module of this series. My sole purpose of writing this blog is provide a gist of how SQL server is affected by the underlying windows memory architecture and what comprises SQL server memory.

Let's get started with the basics.

Difference Between 32-Bit and 64-Bit Systems

The largest integer value a 32-Bit pointer can store is  4,294,967,296= (2^32) addresses. Applications are limited to 4GB Virtual Address Space (VAS) . Of these 2GB addresses are  reserved for kernel mode and the other 2GB are reserved for user mode access.

If we were to have a 64-Bit pointer the largest inter value it could store would be roughly 17 trillion GB =   2^64 addresses. The number of addresses are so huge that they are not implemented in reality in terms of physical memory due to hardware limitations. Currently the processor manufacturers have implemented a 44-bit pointer that can refer to 16TB Virtual Address Space for 64-bit systems. Out of this 8TB is reserved for kernel mode and 8TB for user mode access.

Virtual Memory

The total amount of RAM (Random Access Memory) installed might not always be sufficient to run all the applications on the system. Windows memory management implements and manages what is called Virtual Memory. It’s the method of extending the available physical memory on a computer. PageFile is a file on disk which is used to swap physical memory pages to and from a disk. Virtual Memory comprises of total available physical memory(RAM) on the computer and size of the PageFile on disk.

Virtual Memory Manager

Virtual Memory Manager is responsible for mapping physical memory and virtual address spaces for a process. It keeps track of each mapping for VAS addresses using a page table, and the mapping information itself is stored in a Page Table Entry (PTE). The PTE is 4K in size and shares the same space as other system information and structures of the system.

Most recently referenced pages are located in RAM. Those which aren't recently referenced for a while may be written to the PageFile. This is called swapping or paging out memory.

Thus there is no gaurantee that once a particular page was accessed from certain location within memory, the same page is available at the same location the next time its accessed. The next time it may be loaded from the PageFile on disk so the request will be slower. This  is known as a Page Fault and it occurs without users intervention or knowledge.The process of loading the page back from the PageFile into physical memory, also called swapping or paging in memory.

/3GB switch in 32-bit systems

As explained earlier the primary difference between 32-bit and 64-bit systems is the size of the address pointer and the number of addresses referenced in terms of memory. As we know in 32-bit systems 2GB is reserved for user mode access. We can increase this memory to 3GB for SQL server process address space thus reducing the kernel mode access space to 1GB by implementing the /3GB switch. A /3GB parameter needs to be specified  in the Boot.ini file located under C: drive ideally of Windows 2003 or earlier version. Once you add the parameter and reboot each process will have a 3GB address space.

How to Enable  /3GB switch:

1.      Right-click on the Command Prompt icon in the Accessories program group of the Start menu. Click Run as Administrator.

2.      At the command prompt, enter: bcedit /set IncreaseUserVa 3072

3.      Restart the computer.

You could browse this link to check how the /3GB option is enabled.https://technet.microsoft.com/en-us/library/bb124810(v=exchg.65).aspx

In the next module we will learn about AWE and /PAE switch.

Basic Windows Administration through Powershell

Hi Guys,

In the previous blog I took you through PowerShell basics We know that PowerShell to works with different modules like SQL server, Active Directory, Windows Administration, IIS, etc. In this blog I shall be updating a few scripts I've used. You may modify the same as per your environment.
You will be able to find many such scripts online and under http://powershell.com

1.Deleting Files older than Certain Date

#Folder from which files are to be deleted 

$filepath= "D:\"#Change the path based on your file location

#Accepting the number of days prior to which user wants to delete the files

$no_of_days = read-host "Enter the number of days(integer) prior to which backups should be delete "

$a=get-date

$a= $a.adddays(-$no_of_days)

#Confirming the date with the user

write-host "Do you want to delete files older than"$a"   Y/N"

$response=read-host

If ($response -eq "Y" -or $response -eq "y")

{

write-host " Deleting files from $filepath older than " $a

#Deleting files only

get-childitem $path -recurse | where-object {$_.mode -notmatch "d"} | where-object {$_.lastwritetime.date -lt $a} | ForEach-Object { Write-Host "Deleting "$_.FullName; Remove-Item $_.FullName;}

}

#Confirming shell closure

else

{

write-host "Execution cancelled to execute successfully pass the right parameters & hit Y/y. Any other input will be considered Invalid"

start-sleep -s 7} 

2. Pinging Multiple Servers

#Servername Text File

$filepath = "C:\Test.txt" #Change the path based on your file location

$Sname = Get-Content $filepath

#Running Loop for each servername in text file

foreach ($S in $Sname)

{

     #Checks if the server is pingable

     $StatusFlag =test-Connection -ComputerName $S -Count 1 -Quiet

     if ($StatusFlag) #If server is pingable dispaly source,destination & response time

      

       {

#Sends just one packet to the server and provides the response time 

        Test-Connection $S -count 1|select-object -Property @{Name="Source"; Expression = {$_.pscomputername}},@{Name="Destination"; Expression = {$_.address}},responsetime

       }

      else

                           

        {

         write-host "$S is inaccessible from $env:computername" -ForegroundColor Cyan

        }    

        

} 

3.Multiple Server Entire Directory Structure and File Copy 

# Directory from where the files and folder structure needs to be copied

$directorypath = 'C:\directory\*.*' #Change the path based on your file location

$servercount = read-host "Enter total number of servers for filecopy"

# Accepting servernames and inserting them in an Array

if ($servercount -gt 0)

{

#write-host "Enter the servernames"

$servers = @()

$i=0

 do

{

   $servers += read-host "Enter Server $($i+1)"

   $i++

}   while($i -lt $servercount)

   

   

    #Once servernames are accepted copy the entire direcorypath structure with the files to destination 

    Foreach ($s in $servers){

   

    #write-host "destination is " $s

     Copy-Item $directorypath -Destination "\\$s\C$\directory" -Recurse 

    #Change the Destination as per your requirement

     }

}

else

{

write-host "Total number of servers is 0 script exccution closed"

}

I'll update this with more scripts as and when tested and used. Until then keep learning and exploring...

Getting Started with PowerShell

Windows PowerShell is all about exploring .NET framework capability through the command line.Announced in 2006 it’s an admins paradise with the number of tasks that can be automated through the command line. The below table gives the release dates for various version of PowerShell.

PowerShell Version	Release Date	Default Windows Versions	Available Windows Versions
PowerShell 1.0	November 2006	Windows Server 2008 (*)	Windows XP SP2 Windows XP SP3 Windows Server 2003 SP1 Windows Server 2003 SP2 Windows Server 2003 R2 Windows Vista Windows Vista SP2
PowerShell 2.0	October 2009	Windows 7 Windows Server 2008 R2 (**)	Windows XP SP3 Windows Server 2003 SP2 Windows Vista SP1 Windows Vista SP2 Windows Server 2008 SP1 Windows Server 2008 SP2
PowerShell 3.0	September 2012	Windows 8 Windows Server 2012	Windows 7 SP1 Windows Server 2008 SP2 Windows Server 2008 R2 SP1
PowerShell 4.0	October 2013	Windows 8.1 Windows Server 2012 R2	Windows 7 SP1 Windows Server 2008 R2 SP1 Windows Server 2012
PowerShell 5.0	April 2014	Windows 10	Windows 8.1 Windows Server 2012 R2

1. Why & what is Powershell.

Prior to Powershell VBscripts/unix scripts or DOS commands were encoded in batch files to be executed for performing repetitive task.Should there be a need to change the pattern of design the entire code would have to be re-written.This involved additional work, time and testing. Instead there was a need of a functionality wherein which you could write you own scripts give it a command name similar to that like DIR or MKDIR and have different parameter sets modifiable as per requirements.

PowerShell was invented with the idea of automating Microsoft GUI commands through a command-line interface.If you have any of the above windows version installed you will find the PowerShell under All programs -> Accessories -> Windows Powershell.  

PowerShell is both a command-line shell and scripting language. 

You may choose the ISE environment (x86 if your are running a 32-bit system) once you are familiar with the commands and wish to start scripting programs in PowerShell. PowerShell has its internal variables as well just like any other shell.  

Get-Variable would return the list.

All the commands written for PowerShell are called CMDLETS and they have the common structure of Verb-Noun. The native Windows-DOS and Unix commands work with PowerShell. For ef - dir,cls,ps,etc. Below are a few basic commands

1.      Get- help

2.      Get-command

3.      Get-verb

4.      Get-member

5.      Get-alias

Anything and everything can be learnt I believe with proper use of the above CmdLets .Get-Help  has a similar functionality as man command in Unix. Powershell works with all the wild card characters similar to Unix or DOS …Suppose you need to know the commands associated with process.I would type something like 

Get-Command *Process

and it would return the following output depending on the version.

To know the syntax and semantics of the usage of the command one could use.

Get-help Start-process 

and it would return the following output depending on the version.

As I stated earlier powershell returns object so if one wants to know what kind of object is returned and how to work further with the object  you could try

Get-Process | Get-Member 

and it would return the following output  depending on the version.

Wondering how the native commands works simple.Type the command

Get-Alias 

and you would get the below output depending on the version.

Get-verb 

lists the existing verb supported by PowerShell.

I'd suggest if you really want to learn first run the command Get-Help Get-Help and browse your way through the options. If you’re a keen learner this blog will just help you take your first step to the arena of automation through PowerShell.

Microsoft Virtual Academy provides and 6 hours intensive course by expert Jeffrey Snover, the inventor of PowerShell, and Jason Helmick, Senior Technologist at Concentrated Technolog to get started with powershell. 

https://mva.microsoft.com/en-US/training-courses/getting-started-with-powershell-3-0-jump-start-8276?l=5Rb7mQWy_5704984382

My personal favorite are books by Don Jones. "Learn Powershell in a month of lunches" It helped me get started. I also recommend to go through the below link which provides a summary of PowerShell basics.
https://technet.microsoft.com/en-us/magazine/hh551144.aspx

Powershell has the ability to work with different modules like SQL server, Active Directory, Windows Administration, IIS. Advantages and usage of Powershell are limitless for you to explore.