Virtual Memory: Virtual Memory is a feature of an operating system. Virtual memory enables memory address space usage by a process which is independent of other processes running at the same time and uses memory space which is larger than RAM. It relegates temporarily some content that is available in RAM to disk.
Physical Memory: Physical memory is the actual real memory used in RAM. Physical memory is the only memory that is directly accessible to the CPU. CPU reads the instructions stored in the physical memory and executes them continuously
Pagefile: The operating system uses a pagefile to store small pages of virtual address space. This space is accessed when system processes exceed the total amount of physical RAM that is available. The size of the pagefile depends on the total amount of physical RAM that has been installed on the system. The typical size of the pagefile is 1.5 times the amount of memory that is currently installed
How to increase the size of the pagefiles
Click Start, right-click My Computer, and then click Properties.
In the System Properties dialog box, click the Advanced tab.
In the Performance pane, click Settings.
In the Performance Options dialog box, click the Advanced tab.
In the Virtual memory pane, click Change.
Change the Initial size value and the Maximum size value to a higher value, click Set, and then clickOK.
Click OK to close the Performance Options dialog box, and then click OK to close the System Properties dialog box.
Notes
The package will change the Initial size value to 1.5 times of your RAM and change the Maximum size value to 3 times of your RAM if the space is enough.
If you are not on the computer that has the problem, save the Fix it solution to a flash drive or a CD and then run it on the computer that has the problem.
Page tables: Page tables are used to translate the virtual addresses seen by the application into physical addresses used by the hardware to process instructions; such hardware that handles this specific translation is often known as the memory management unit.
Latches Object: Latches object in Microsoft SQL Server provides counters to monitor internal SQL Server resource locks called latches. Monitoring the latches to determine user activity and resource usage can help you to identify performance bottlenecks.
SQL Server Latches counters
What is AWE? Address Windowing Extensions API is commonly known as AWE. AWE is used by SQL Server when it has to support very large amounts of physical memory. AWE feature is only available in SQL Server Enterprise, Standard, and Developer editions with of SQL Server 32 bit version.
Microsoft Windows 2000/2003 server supports maximum of 64GB memory. If we have installed SQL Server 32 bit version which can support maximum of 3 GB memory on Windows 2000/2003, we can enable AWE feature to use available physical memory of server to improve performance of SQL Server. In simple words, AWE provides memory management functions which let windows to allow more than 3GB memory to standard 32 bit application.
What is PAE? PAE (Physical Address Extension) is an Intel-provided memory address extension that enables support of greater than 4 GB of physical memory for most 32-bit (IA-32) Intel Pentium Pro and later platforms.In Windows Server 2008, /PAE change cannot be done directly in boot.ini file. It has to be done using the BCDEDIT /SET command. In order to enable PAE, the following needs to be done.
Open Command Prompt with elevated privileges (Run as Administrator)
Execute the following command
BCDEDIT /SET PAE ForceEnable
Windows 2000 Advanced Server, SQL Server 2000 Enterprise Edition can us up to 8GB of RAM.
If the operating system is Windows 2000 Datacenter Server, SQL Server 2000Enterprisecan use up to 64GB of RAM.
What is LOCK PAGES Memory? The Windows policy Lock Pages in Memory . This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.
What is PAGES and Extents? The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages
What is Extents : Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
What is CHECKPOINT ? Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
"The Checkpoint process scans the Buffer Cache periodically and writes all the Dirty Data Pages for a particular database to disk"
What is DIRTYPAGES ? A page is considered dirty when data modifications have taken place. A dirty page cannot be removed from the SQL Server buffer pool until the associated log records have been written and the page itself written to stable media.
What isLazy Writer? Lazy writer serves two purposes: Ensure that a specified number of buffers are free in the Buffer Pool so they can be allocated for use by the server Monitor the usage of committed memory by the Buffer Pool and adjust it as necessary so that enough physical memory remains free to prevent Windows from paging.