Contents
Module Overview 1
Lesson 1: Memory 3
Lesson 2: I/O 73
Lesson 3: CPU 111
Module 3: Troubleshooting Server Performance
Module Overview
Troubleshooting server performance-based support calls requires product knowledge, good communication skills, and a proven troubleshooting methodology. In this module we will discuss Microsoft® SQL Server™ interaction with the operating system and methodology of troubleshooting server-based problems.
At the end of this module, you will be able to:
Define the common terms associated the memory, I/O, and CPU subsystems.
Describe how SQL Server leverages the Microsoft Windows® operating system facilities including memory, I/O, and threading.
Define common SQL Server memory, I/O, and processor terms.
Generate a hypothesis based on performance counters captured by System Monitor.
For each hypothesis generated, identify at least two other non-System Monitor pieces of information that would help to confirm or reject your hypothesis.
Identify at least five counters for each subsystem that are key to understanding the performance of that subsystem.
Identify three common myths associated with the memory, I/O, or CPU subsystems.
Lesson 1: Memory
What You Will Learn
After completing this lesson, you will be able to:
Define common terms used when describing memory.
Give examples of each memory concept and how it applies to SQL Server.
Describe how SQL Server user and manages its memory.
List the primary configuration options that affect memory.
Describe how configuration options affect memory usage.
Describe the effect on the I/O subsystem when memory runs low.
List at least two memory myths and why they are not true.
Recommended Reading
SQL Server 7.0 Performance Tuning Technical Reference, Microsoft Press
Windows 2000 Resource Kit companion CD-ROM documentation. Chapter 15: Overview of Performance Monitoring
Inside Microsoft Windows 2000, Third Edition, David A. Solomon and Mark E. Russinovich
Windows 2000 Server Operations Guide, Storage, File Systems, and Printing; Chapters: Evaluating Memory and Cache Usage
Advanced Windows, 4th Edition, Jeffrey Richter, Microsoft Press
Related Web Sites
http://ntperformance/
Memory Definitions
Memory Definitions
Before we look at how SQL Server uses and manages its memory, we need to ensure a full understanding of the more common memory related terms. The following definitions will help you understand how SQL Server interacts with the operating system when allocating and using memory.
Virtual Address Space
A set of memory addresses that are mapped to physical memory addresses by the system. In a 32-bit operation system, there is normally a linear array of 2^32 addresses representing 4,294,967,269 byte addresses.
Physical Memory
A series of physical locations, with unique addresses, that can be used to store instructions or data.
AWE – Address Windowing Extensions
A 32-bit process is normally limited to addressing 2 gigabytes (GB) of memory, or 3 GB if the system was booted using the /3G boot switch even if there is more physical memory available. By leveraging the Address Windowing Extensions API, an application can create a fixed-size window into the additional physical memory. This allows a process to access any portion of the physical memory by mapping it into the applications window. When used in combination with Intel’s Physical Addressing Extensions (PAE) on Windows 2000, an AWE enabled application can support up to 64 GB of memory
Reserved Memory
Pages in a processes address space are free, reserved or committed. Reserving memory address space is a way to reserve a range of virtual addresses for later use. If you attempt to access a reserved address that has not yet been committed (backed by memory or disk) you will cause an access violation.
Committed Memory
Committed pages are those pages that when accessed in the end translate to pages in memory. Those pages may however have to be faulted in from a page file or memory mapped file.
Backing Store
Backing store is the physical representation of a memory address.
Page Fault (Soft/Hard)
A reference to an invalid page (a page that is not in your working set) is referred to as a page fault. Assuming the page reference does not result in an access violation, a page fault can be either hard or soft. A hard page fault results in a read from disk, either a page file or memory-mapped file. A soft page fault is resolved from one of the modified, standby, free or zero page transition lists. Paging is represented by a number of counters including page faults/sec, page input/sec and page output/sec. Page faults/sec include soft and hard page faults where as the page input/output counters represent hard page faults. Unfortunately, all of these counters include file system cache activity.
For more information, see also…Inside Windows 2000,Third Edition, pp. 443-451.
Private Bytes
Private non-shared committed address space
Working Set
The subset of processes virtual pages that is resident in physical memory.
For more information, see also… Inside Windows 2000,Third Edition, p. 455.
System Working Set
Like a process, the system has a working set. Five different types of pages represent the system’s working set: system cache; paged pool; pageable code and data in the kernel; page-able code and data in device drivers; and system mapped views. The system working set is represented by the counter Memory: cache bytes. System working set paging activity can be viewed by monitoring the Memory: Cache Faults/sec counter.
For more information, see also… Inside Windows 2000,Third Edition, p. 463.
System Cache
The Windows 2000 cache manager provides data caching for both local and network file system drivers. By caching virtual blocks, the cache manager can reduce disk I/O and provide intelligent read ahead. Represented by Memory:Cache Resident bytes.
For more information, see also… Inside Windows 2000,Third Edition, pp. 654-659.
Non Paged Pool
Range of addresses guaranteed to be resident in physical memory. As such, non-paged pool can be accessed at any time without incurring a page fault. Because device drivers operate at DPC/dispatch level (covered in lesson 2), and page faults are not allowed at this level or above, most device drivers use non-paged pool to assure that they do not incur a page fault. Represented by Memory: Pool Nonpaged Bytes, typically between 3-30 megabytes (MB) in size.
Note
The pool is, in effect, a common area of memory shared by all processes. One of the most common uses of non-paged pool is the storage of object handles.
For more information regarding “maximums,” see also… Inside Windows 2000,Third Edition, pp. 403-404
Paged Pool
Range of address that can be paged in and out of physical memory. Typically used by drivers who need memory but do not need to access that memory from DPC/dispatch of above interrupt level. Represented by Memory: Pool Paged Bytes and Memory:Pool Paged Resident Bytes. Typically between 10-30MB + size of Registry.
For more information regarding “limits,” see also… Inside Windows 2000,Third Edition, pp. 403-404.
Stack
Each thread has two stacks, one for kernel mode and one for user mode. A stack is an area of memory in which program procedure or function call addresses and parameters are temporarily stored.
In Process
To run in the same address space. In-process servers are loaded in the client’s address space because they are implemented as DLLs. The main advantage of running in-process is that the system usually does not need to perform a context switch. The disadvantage to running in-process is that DLL has access to the process address space and can potentially cause problems.
Out of Process
To run outside the calling processes address space. OLEDB providers can run in-process or out of process. When running out of process, they run under the context of DLLHOST.EXE.
Memory Leak
To reserve or commit memory and unintentionally not release it when it is no longer being used. A process can leak resources such as process memory, pool memory, user and GDI objects, handles, threads, and so on.
Memory Concepts (X86 Address Space)
Per Process Address Space
Every process has its own private virtual address space. For 32-bit processes, that address space is 4 GB, based on a 32-bit pointer. Each process’s virtual address space is split into user and system partitions based on the underlying operating system. The diagram included at the top represents the address partitioning for the 32-bit version of Windows 2000.
Typically, the process address space is evenly divided into two 2-GB regions. Each process has access to 2 GB of the 4 GB address space. The upper 2 GB of address space is reserved for the system. The user address space is where application code, global variables, per-thread stacks, and DLL code would reside.
The system address space is where the kernel, executive, HAL, boot drivers, page tables, pool, and system cache reside.
For specific information regarding address space layout, refer to Inside Microsoft Windows 2000 Third Edition pages 417-428 by Microsoft Press.
Access Modes
Each virtual memory address is tagged as to what access mode the processor must be running in. System space can only be accessed while in kernel mode, while user space is accessible in user mode. This protects system space from being tampered with by user mode code.
Shared System Space
Although every process has its own private memory space, kernel mode code and drivers share system space. Windows 2000 does not provide any protection to private memory being use by components running in kernel mode. As such, it is very important to ensure components running in kernel mode are thoroughly tested.
3-GB Address Space
3-GB Address Space
Although 2 GB of address space may seem like a large amount of memory, application such as SQL Server could leverage more memory if it were available. The boot.ini option /3GB was created for those cases where systems actually support greater than 2 GB of physical memory and an application can make use of it This capability allows memory intensive applications running on Windows 2000 Advanced Server to use up to 50 percent more virtual memory on Intel-based computers. Application memory tuning provides more of the computer's virtual memory to applications by providing less virtual memory to the operating system.
Although a system having less than 2 GB of physical memory can be booted using the /3G switch, in most cases this is ill-advised. If you restart with the 3 GB switch, also known as 4-Gig Tuning, the amount of non-paged pool is reduced to 128 MB from 256 MB.
For a process to access 3 GB of address space, the executable image must have been linked with the /LARGEADDRESSAWARE flag or modified using Imagecfg.exe. It should be pointed out that SQL Server was linked using the /LAREGEADDRESSAWARE flag and can leverage 3 GB when enabled.
Note
Even though you can boot Windows 2000 Professional or Windows 2000 Server with the /3GB boot option, users processes are still limited to 2 GB of address space even if the IMAGE_FILE_LARGE_ADDRESS_AWARE flag is set in the image. The only thing accomplished by using the /3G option on these system is the reduction in the amount of address space available to the system (ISW2K Pg. 418).
Important
If you use /3GB in conjunction with AWE/PAE you are limited to 16 GB of memory.
For more information, see the following Knowledge Base articles:
Q171793 Information on Application Use of 4GT RAM Tuning
Q126402 PagedPoolSize and NonPagedPoolSize Values in Windows NT
Q247904 How to Configure Paged Pool and System PTE Memory Areas
Q274598 W2K Does Not Enable Complete Memory Dumps Between 2 & 4 GB
AWE Memory Layout
AWE Memory
Usually, the operation system is limited to 4 GB of physical memory. However, by leveraging PAE, Windows 2000 Advanced Server can support up to 8 GB of memory, and Data Center 64 GB of memory. However, as stated previously, each 32-bit process normally has access to only 2 GB of address space, or 3 GB if the system was booted with the /3-GB option. To allow processes to allocate more physical memory than can be represented in the 2GB of address space, Microsoft created the Address Windows Extensions (AWE). These extensions allow for the allocation and use of up to the amount of physical memory supported by the operating system.
By leveraging the Address Windowing Extensions API, an application can create a fixed-size window into the physical memory. This allows a process to access any portion of the physical memory by mapping regions of physical memory in and out of the applications window.
The allocation and use of AWE memory is accomplished by
Creating a window via VirtualAlloc using the MEM_PHYSICAL option
Allocating the physical pages through AllocateUserPhysicalPages
Mapping the RAM pages to the window using MapUserPhysicalPages
Note
SQL Server 7.0 supports a feature called extended memory in Windows NT® 4 Enterprise Edition by using a PSE36 driver. Currently there are no PSE drivers for Windows 2000. The preferred method of accessing extended memory is via the Physical Addressing Extensions using AWE. The AWE mapping feature is much more efficient than the older process of coping buffers from extended memory into the process address space.
Unfortunately, SQL Server 7.0 cannot leverage PAE/AWE. Because there are currently no PSE36 drivers for Windows 2000 this means SQL Server 7.0 cannot support more than 3GB of memory on Windows 2000. Refer to KB article Q278466.
AWE restrictions
The process must have Lock Pages In Memory user rights to use AWE
Important
It is important that you use Enterprise Manager or DMO to change the service account. Enterprise Manager and DMO will grant all of the privileges and Registry and file permissions needed for SQL Server. The Service Control Panel does NOT grant all the rights or permissions needed to run SQL Server.
Pages are not shareable or page-able
Page protection is limited to read/write
The same physical page cannot be mapped into two separate AWE regions, even within the same process.
The use of AWE/PAE in conjunction with /3GB will limit the maximum amount of supported memory to between 12-16 GB of memory.
Task manager does not show the correct amount of memory allocated to AWE-enabled applications. You must use Memory Manager: Total Server Memory. It should, however, be noted that this only shows memory in use by the buffer pool.
Machines that have PAE enabled will not dump user mode memory. If an event occurs in User Mode Memory that causes a blue screen and root cause determination is absolutely necessary, the machine must be booted with the /NOPAE switch, and with /MAXMEM set to a number appropriate for transferring dump files.
With AWE enabled, SQL Server will, by default, allocate almost all memory during startup, leaving 256 MB or less free. This memory is locked and cannot be paged out. Consuming all available memory may prevent other applications or SQL Server instances from starting.
Note
PAE is not required to leverage AWE. However, if you have more than 4GB of physical memory you will not be able to access it unless you enable PAE.
Caution
It is highly recommended that you use the “max server memory” option in combination with “awe enabled” to ensure some memory headroom exists for other applications or instances of SQL Server, because AWE memory cannot be shared or paged.
For more information, see the following Knowledge Base articles:
Q268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
Q241046 Cannot Create a dump File on Computers with over 4 GB RAM
Q255600 Windows 2000 utilities do not display physical memory above 4GB
Q274750 How to configure SQL Server memory more than 2 GB (Idea)
Q266251 Memory dump stalls when PAE option is enabled (Idea)
Tip
The KB will return more hits if you query on PAE rather than AWE.
Virtual Address Space Mapping
Virtual Address Space Mapping
By default Windows 2000 (on an X86 platform) uses a two-level (three-level when PAE is enabled) page table structure to translate virtual addresses to physical addresses. Each 32-bit address has three components, as shown below.
When a process accesses a virtual address the system must first locate the Page Directory for the current process via register CR3 (X86). The first 10 bits of the virtual address act as an index into the Page Directory. The Page Directory Entry then points to the Page Frame Number (PFN) of the appropriate Page Table. The next 10 bits of the virtual address act as an index into the Page Table to locate the appropriate page. If the page is valid, the PTE contains the PFN of the actual page in memory. If the page is not valid, the memory management fault handler locates the page and attempts to make it valid. The final 12 bits act as a byte offset into the page.
Note
This multi-step process is expensive. This is why systems have translation look aside buffers (TLB) to speed up the process. One of the reasons context switching is so expensive is the translation buffers must be dumped. Thus, the first few lookups are very expensive. Refer to ISW2K pages 439-440.
Core System Memory Related Counters
Core System Memory Related Counters
When evaluating memory performance you are looking at a wide variety of counters. The counters listed here are a few of the core counters that give you quick overall view of the state of memory. The two key counters are Available Bytes and Committed Bytes. If Committed Bytes exceeds the amount of physical memory in the system, you can be assured that there is some level of hard page fault activity happening. The goal of a well-tuned system is to have as little hard paging as possible. If Available Bytes is below 5 MB, you should investigate why. If Available Bytes is below 4 MB, the Working Set Manager will start to aggressively trim the working sets of process including the system cache.
Committed Bytes
Total memory, including physical and page file currently committed
Commit Limit
• Physical memory + page file size
• Represents the total amount of memory that can be committed without expanding the page file. (Assuming page file is allowed to grow)
Available Bytes
Total physical memory currently available
Note
Available Bytes is a key indicator of the amount of memory pressure. Windows 2000 will attempt to keep this above approximately 4 MB by aggressively trimming the working sets including system cache. If this value is constantly between 3-4 MB, it is cause for investigation.
One counter you might expect would be for total physical memory. Unfortunately, there is no specific counter for total physical memory. There are however many other ways to determine total physical memory. One of the most common is by viewing the Performance tab of Task Manager.
Page File Usage
The only counters that show current page file space usage are Page File:% Usage and Page File:% Peak Usage. These two counters will give you an indication of the amount of space currently used in the page file.
Memory Performance
Memory Counters
There are a number of counters that you need to investigate when evaluating memory performance. As stated previously, no single counter provides the entire picture. You will need to consider many different counters to begin to understand the true state of memory.
Note
The counters listed are a subset of the counters you should capture.
*Available Bytes
In general, it is desirable to see Available Bytes above 5 MB. SQL Servers goal on Intel platforms, running Windows NT, is to assure there is approximately 5+ MB of free memory. After Available Bytes reaches 4 MB, the Working Set Manager will start to aggressively trim the working sets of process and, finally, the system cache. This is not to say that working set trimming does not happen before 4 MB, but it does become more pronounced as the number of available bytes decreases below 4 MB.
Page Faults/sec
Page Faults/sec represents the total number of hard and soft page faults. This value includes the System Working Set as well. Keep this in mind when evaluating the amount of paging activity in the system. Because this counter includes paging associated with the System Cache, a server acting as a file server may have a much higher value than a dedicated SQL Server may have. The System Working Set is covered in depth on the next slide. Because Page Faults/sec includes soft faults, this counter is not as useful as Pages/sec, which represents hard page faults. Because of the associated I/O, hard page faults tend to be much more expensive.
*Pages/sec
Pages/sec represent the number of pages written/read from disk because of hard page faults. It is the sum of Memory: Pages Input/sec and Memory: Pages Output/sec. Because it is counted in numbers of pages, it can be compared to other counts of pages, such as Memory: Page Faults/sec, without conversion.
On a well-tuned system, this value should be consistently low. In and of itself, a high value for this counter does not necessarily indicate a problem. You will need to isolate the paging activity to determine if it is associated with in-paging, out-paging, memory mapped file activity or system cache. Any one of these activities will contribute to this counter.
Note
Paging in and of itself is not necessarily a bad thing. Paging is only “bad” when a critical process must wait for it’s pages to be in-paged, or when the amount of read/write paging is causing excessive kernel time or disk I/O, thus interfering with normal user mode processing.
Tip
(Memory: Pages/sec) / (PhysicalDisk: Disk Bytes/sec * 4096) yields the approximate percentage of paging to total disk I/O. Note, this is only relevant on X86 platforms with a 4 KB page size.
Page Reads/sec (Hard Page Fault)
Page Reads/sec is the number of times the disk was accessed to resolve hard page faults. It includes reads to satisfy faults in the file system cache (usually requested by applications) and in non-cached memory mapped files. This counter counts numbers of read operations, without regard to the numbers of pages retrieved by each operation. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.
Page Writes/sec (Hard Page Fault)
Page Writes/sec is the number of times pages were written to disk to free up space in physical memory. Pages are written to disk only if they are changed while in physical memory, so they are likely to hold data, not code. This counter counts write operations, without regard to the number of pages written in each operation. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.
*Pages Input/sec (Hard Page Fault)
Pages Input/sec is the number of pages read from disk to resolve hard page faults. It includes pages retrieved to satisfy faults in the file system cache and in non-cached memory mapped files. This counter counts numbers of pages, and can be compared to other counts of pages, such as Memory:Page Faults/sec, without conversion. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval. This is one of the key counters to monitor for potential performance complaints. Because a process must wait for a read page fault this counter, read page faults have a direct impact on the perceived performance of a process.
*Pages Output/sec (Hard Page Fault)
Pages Output/sec is the number of pages written to disk to free up space in physical memory. Pages are written back to disk only if they are changed in physical memory, so they are likely to hold data, not code. A high rate of pages output might indicate a memory shortage. Windows NT writes more pages back to disk to free up space when physical memory is in short supply. This counter counts numbers of pages, and can be compared to other counts of pages, without conversion. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval. Like Pages Input/sec, this is one of the key counters to monitor. Processes will generally not notice write page faults unless the disk I/O begins to interfere with normal data operations.
Demand Zero Faults/Sec (Soft Page Fault)
Demand Zero Faults/sec is the number of page faults that require a zeroed page to satisfy the fault. Zeroed pages, pages emptied of previously stored data and filled with zeros, are a security feature of Windows NT. Windows NT maintains a list of zeroed pages to accelerate this process. This counter counts numbers of faults, without regard to the numbers of pages retrieved to satisfy the fault. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.
Transition Faults/Sec (Soft Page Fault)
Transition Faults/sec is the number of page faults resolved by recovering pages that were on the modified page list, on the standby list, or being written to disk at the time of the page fault. The pages were recovered without additional disk activity. Transition faults are counted in numbers of faults, without regard for the number of pages faulted in each operation. This counter displays the difference between the values observed in the last two samples, divided by the duration of the sample interval.
System Working Set
System Working Set
Like processes, the system page-able code and data are managed by a working set. For the purpose of this course, that working set is referred to as the System Working Set. This is done to differentiate the system cache portion of the working set from the entire working set. There are five different types of pages that make up the System Working Set. They are: system cache; paged pool; page-able code and data in ntoskrnl.exe; page-able code, and data in device drivers and system-mapped views.
Unfortunately, some of the counters that appear to represent the system cache actually represent the entire system working set. Where noted system cache actually represents the entire system working set.
Note
The counters listed are a subset of the counters you should capture.
*Memory: Cache Bytes (Represents Total System Working Set)
Represents the total size of the System Working Set including: system cache; paged pool; pageable code and data in ntoskrnl.exe; pageable code and data in device drivers; and system-mapped views. Cache Bytes is the sum of the following counters: System Cache Resident Bytes, System Driver Resident Bytes, System Code Resident Bytes, and Pool Paged Resident Bytes.
Memory: System Cache Resident Bytes (System Cache)
System Cache Resident Bytes is the number of bytes from the file system cache that are resident in physical memory. Windows 2000 Cache Manager works with the memory manager to provide virtual block stream and file data caching.
For more information, see also…Inside Windows 2000,Third Edition, pp. 645-650 and p. 656.
Memory: Pool Paged Resident Bytes
Represents the physical memory consumed by Paged Pool. This counter should NOT be monitored by itself. You must also monitor Memory: Paged Pool. A leak in the pool may not show up in Pool paged Resident Bytes.
Memory: System Driver Resident Bytes
Represents the physical memory consumed by driver code and data. System Driver Resident Bytes and System Driver Total Bytes do not include code that must remain in physical memory and cannot be written to disk.
Memory: System Code Resident Bytes
Represents the physical memory consumed by page-able system code. System Code Resident Bytes and System Code Total Bytes do not include code that must remain in physical memory and cannot be written to disk.
Working Set Performance Counter
You can measure the number of page faults in the System Working Set by monitoring the Memory: Cache Faults/sec counter. Contrary to the “Explain” shown in System Monitor, this counter measures the total amount of page faults/sec in the System Working Set, not only the System Cache. You cannot measure the performance of the System Cache using this counter alone.
For more information, see also…Inside Windows 2000,Third Edition, p. 656.
Note
You will find that in general the working set manager will usually trim the working sets of normal processes prior to trimming the system working set.
System Cache
System Cache
The Windows 2000 cache manager provides a write-back cache with lazy writing and intelligent read-ahead. Files are not written to disk immediately but differed until the cache manager calls the memory manager to flush the cache. This helps to reduce the total number of I/Os.
Once per second, the lazy writer thread queues one-eighth of the dirty pages in the system cache to be written to disk. If this is not sufficient to meet the needs, the lazy writer will calculate a larger value. If the dirty page threshold is exceeded prior to lazy writer waking, the cache manager will wake the lazy writer.
Important
It should be pointed out that mapped files or files opened with FILE_FLAG_NO_BUFFERING, do not participate in the System Cache.
For more information regarding mapped views, see also…Inside Windows 2000,Third Edition, p. 669.
For those applications that would like to leverage system cache but cannot tolerate write delays, the cache manager supports write through operations via the FILE_FLAG_WRITE_THROUGH. On the other hand, an application can disable lazy writing by using the FILE_ATTRIBUTE_TEMPORARY. If this flag is enabled, the lazy writer will not write the pages to disk unless there is a shortage of memory or the file is closed.
Important
Microsoft SQL Server uses both FILE_FLAG_NO_BUFFERING and FILE_FLAG_WRITE_THROUGH
Tip
The file system cache is not represented by a static amount of memory. The system cache can and will grow. It is not unusual to see the system cache consume a large amount of memory. Like other working sets, it is trimmed under pressure but is generally the last thing to be trimmed.
System Cache Performance Counters
The counters listed are a subset of the counters you should capture.
Cache: Data Flushes/sec
Data Flushes/sec is the rate at which the file system cache has flushed its contents to disk as the result of a request to flush or to satisfy a write-through file write request. More than one page can be transferred on each flush operation.
Cache: Data Flush Pages/sec
Data Flush Pages/sec is the number of pages the file system cache has flushed to disk as a result of a request to flush or to satisfy a write-through file write request.
Cache: Lazy Write Flushes/sec
Represents the rate of lazy writes to flush the system cache per second. More than one page can be transferred per second.
Cache: Lazy Write Pages/sec
Lazy Write Pages/sec is the rate at which the Lazy Writer thread has written to disk.
Note
When looking at Memory:Cache Faults/sec, you can remove cache write activity by subtracting (Cache: Data Flush Pages/sec + Cache: Lazy Write Pages/sec). This will give you a better idea of how much other page faulting activity is associated with the other components of the System Working Set. However, you should note that there is no easy way to remove the page faults associated with file cache read activity.
For more information, see the following Knowledge Base articles:
Q145952 (NT4) Event ID 26 Appears If Large File Transfer Fails
Q163401 (NT4) How to Disable Network Redirector File Caching
Q181073 (SQL 6.5) DUMP May Cause Access Violation on Win2000
System Pool
System Pool
As documented earlier, there are two types of shared pool memory: non-paged pool and paged pool. Like private memory, pool memory is susceptible to a leak.
Nonpaged Pool
Miscellaneous kernel code and structures, and drivers that need working memory while at or above DPC/dispatch level use non-paged pool. The primary counter for non-paged pool is Memory: Pool Nonpaged Bytes. This counter will usually between 3 and 30 MB.
Paged Pool
Drivers that do not need to access memory above DPC/Dispatch level are one of the primary users of paged pool, however any process can use paged pool by leveraging the ExAllocatePool calls. Paged pool also contains the Registry and file and printing structures. The primary counters for monitoring paged pool is Memory: Pool Paged Bytes. This counter will usually be between 10-30MB plus the size of the Registry. To determine how much of paged pool is currently resident in physical memory, monitor Memory: Pool Paged Resident Bytes.
Note
The paged and non-paged pools are two of the components of the System Working Set. If a suspected leak is clearly visible in the overview and not associated with a process, then it is most likely a pool leak. If the leak is not associated with SQL Server handles, OLDEB providers, XPROCS or SP_OA calls then most likely this call should be pushed to the Windows NT group.
For more information, see the following Knowledge Base articles:
Q265028 (MS) Pool Tags
Q258793 (MS) How to Find Memory Leaks by Using Pool Bitmap Analysis
Q115280 (MS) Finding Windows NT Kernel Mode Memory Leaks
Q177415 (MS) How to Use Poolmon to Troubleshoot Kernel Mode Memory Leaks
Q126402 PagedPoolSize and NonPagedPoolSize Values in Windows NT
Q247904 How to Configure Paged Pool and System PTE Memory Areas
Tip
To isolate pool leaks you will need to isolate all drivers and third-party processes. This should be done by disabling each service or driver one at a time and monitoring the effect. You can also monitor paged and non-paged pool through poolmon. If pool tagging has been enabled via GFLAGS, you may be able to associate the leak to a particular tag. If you suspect a particular tag, you should involve the platform support group.
Process Memory Counters
Process _Total Limitations
Although the rollup of _Total for Process: Private Bytes, Virtual Bytes, Handles and Threads, represent the key resources being used across all processes, they can be misleading when evaluating a memory leak. This is because a leak in one process may be masked by a decrease in another process.
Note
The counters listed are a subset of the counters you should capture.
Tip
When analyzing memory leaks, it is often easier to a build either a separate chart or report showing only one or two key counters for all process. The primary counter used for leak analysis is private bytes, but processes can leak handles and threads just as easily. After a suspect process is located, build a separate chart that includes all the counters for that process.
Individual Process Counters
When analyzing individual process for memory leaks you should include the counters listed.
Process: % Processor Time
Process: Working Set (includes shared pages)
Process: Virtual Bytes
Process: Private Bytes
Process: Page Faults/sec
Process: Handle Count
Process: Thread Count
Process: Pool Paged Bytes
Process: Pool Nonpaged Bytes
Tip
WINLOGON, SVCHOST, services, or SPOOLSV are referred to as HELPER processes. They provide core functionality for many operations and as such are often extended by the addition of third-party DLLs. Tlist –s may help identify what services are running under a particular helper.
Helper Processes
Helper Processes
Winlogon, Services, and Spoolsv and Svchost are examples of what are referred to as HELPER processes. They provide core functionality for many operations and, as such, are often extended by the addition of third-party DLLs.
Running every service in its own process can waste system resources. Consequently, some services run in their own processes while others share a process with other services. One problem with sharing a process is that a bug in one service may cause the entire process to fail. The resource kit tool, Tlist when used with the –s qualifier can help you identify what services are running in what processes.
WINLOGON
Used to support GINAs.
SPOOLSV
SPOOLSV is responsible for printing. You will need to investigate all added printing functionality.
Services
Service is responsible for system services.
Svchost.exe
Svchost.exe is a generic host process name for services that are run from dynamic-link libraries (DLLs). There can be multiple instances of Svchost.exe running at the same time. Each Svchost.exe session can contain a grouping of services, so that separate services can be run depending on how and where Svchost.exe is started. This allows for better control and debugging.
The Effect of Memory on Other Components
Memory Drives Overall Performance
Processor, cache, bus speeds, I/O, all of these resources play a roll in overall perceived performance. Without minimizing the impact of these components, it is important to point out that a shortage of memory can often have a larger perceived impact on performance than a shortage of some other resource. On the other hand, an abundance of memory can often be leveraged to mask bottlenecks. For instance, in certain environments, file system cache can significantly reduce the amount of disk I/O, potentially masking a slow I/O subsystem.
Effect on I/O
I/O can be driven by a number of memory considerations. Page read/faults will cause a read I/O when a page is not in memory. If the modified page list becomes too long the Modified Page Writer and Mapped Page Writer will need to start flushing pages causing disk writes. However, the one event that can have the greatest impact is running low on available memory. In this case, all of the above events will become more pronounced and have a larger impact on disk activity.
Effect on CPU
The most effective use of a processor from a process perspective is to spend as much time possible executing user mode code. Kernel mode represents processor time associated with doing work, directly or indirectly, on behalf of a thread. This includes items such as synchronization, scheduling, I/O, memory management, and so on. Although this work is essential, it takes processor cycles and the cost, in cycles, to transition between user and kernel mode is expensive.
Because all memory management and I/O functions must be done in kernel mode, it follows that the fewer the memory resources the more cycles are going to be spent managing those resources. A direct result of low memory is that the Working Set Manager, Modified Page Writer and Mapped Page Writer will have to use more cycles attempting to free memory.
Analyzing Memory
Look for Trends and Trend Relationships
Troubleshooting performance is about analyzing trends and trend relationships. Establishing that some event happened is not enough. You must establish the effect of the event. For example, you note that paging activity is high at the same time that SQL Server becomes slow. These two individual facts may or may not be related. If the paging is not associated with SQL Servers working set, or the disks SQL is using there may be little or no cause/affect relationship.
Look at Physical Memory First
The first item to look at is physical memory. You need to know how much physical and page file space the system has to work with. You should then evaluate how much available memory there is. Just because the system has free memory does not mean that there is not any memory pressure. Available Bytes in combination with Pages Input/sec and Pages Output/sec can be a good indicator as to the amount of pressure. The goal in a perfect world is to have as little hard paging activity as possible with available memory greater than 5 MB. This is not to say that paging is bad. On the contrary, paging is a very effective way to manage a limited resource. Again, we are looking for trends that we can use to establish relationships. After evaluating physical memory, you should be able to answer the following questions:
How much physical memory do I have?
What is the commit limit?
Of that physical memory, how much has the operating system committed?
Is the operating system over committing physical memory?
What was the peak commit charge?
How much available physical memory is there?
What is the trend associated with committed and available?
Review System Cache and Pool Contribution
After you understand the individual process memory usage, you need to evaluate the System Cache and Pool usage. These can and often represent a significant portion of physical memory. Be aware that System Cache can grow significantly on a file server. This is usually normal. One thing to consider is that the file system cache tends to be the last thing trimmed when memory becomes low. If you see abrupt decreases in System Cache Resident Bytes when Available Bytes is below 5 MB you can be assured that the system is experiencing excessive memory pressure.
Paged and non-paged pool size is also important to consider. An ever-increasing pool should be an indicator for further research. Non-paged pool growth is usually a driver issue, while paged pool could be driver-related or process-related. If paged pool is steadily growing, you should investigate each process to see if there is a specific process relationship. If not you will have to use tools such as poolmon to investigate further.
Review Process Memory Usage
After you understand the physical memory limitations and cache and pool contribution you need to determine what components or processes are creating the pressure on memory, if any. Be careful if you opt to chart the _Total Private Byte’s rollup for all processes. This value can be misleading in that it includes shared pages and can therefore exceed the actual amount of memory being used by the processes. The _Total rollup can also mask processes that are leaking memory because other processes may be freeing memory thus creating a balance between leaked and freed memory.
Identify processes that expand their working set over time for further analysis. Also, review handles and threads because both use resources and potentially can be mismanaged.
After evaluating the process resource usage, you should be able to answer the following:
Are any of the processes increasing their private bytes over time?
Are any processes growing their working set over time?
Are any processes increasing the number of threads or handles over time?
Are any processes increasing their use of pool over time?
Is there a direct relationship between the above named resources and total committed memory or available memory?
If there is a relationship, is this normal behavior for the process in question? For example, SQL does not commit ‘min memory’ on startup; these pages are faulted in into the working set as needed. This is not necessarily an indication of a memory leak.
If there is clearly a leak in the overview and is not identifiable in the process counters it is most likely in the pool.
If the leak in pool is not associated with SQL Server handles, then more often than not, it is not a SQL Server issue. There is however the possibility that the leak could be associated with third party XPROCS, SP_OA* calls or OLDB providers.
Review Paging Activity and Its Impact on CPU and I/O
As stated earlier, paging is not in and of itself a bad thing. When starting a process the system faults in the pages of an executable, as they are needed. This is preferable to loading the entire image at startup. The same can be said for memory mapped files and file system cache. All of these features leverage the ability of the system to fault in pages as needed The greatest impact of paging on a process is when the process must wait for an in-page fault or when page file activity represents a significant portion of the disk activity on the disk the application is actively using. After evaluating page fault activity, you should be able to answer the following questions:
What is the relationship between PageFaults/sec and Page Input/sec + Page Output/Sec?
What is the relationship if any between hard page faults and available memory?
Does paging activity represent a significant portion of processor or I/O resource usage?
Don’t Prematurely Jump to Any Conclusions
Analyzing memory pressure takes time and patience. An individual counter in and of it self means little. It is only when you start to explore relationships between cause and effect that you can begin to understand the impact of a particular counter. The key thoughts to remember are:
With the exception of a swap (when the entire process’s working set has been swapped out/in), hard page faults to resolve reads, are the most expensive in terms its effect on a processes perceived performance.
In general, page writes associated with page faults do not directly affect a process’s perceived performance, unless that process is waiting on a free page to be made available. Page file activity can become a problem if that activity competes for a significant percentage of the disk throughput in a heavy I/O orientated environment. That assumes of course that the page file resides on the same disk the application is using.
Lab 3.1 System Memory
Lab 3.1 Analyzing System Memory Using System Monitor
Exercise 1 – Troubleshooting the Cardinal1.log File
Students will evaluate an existing System Monitor log and determine if there is a problem and what the problem is. Students should be able to isolate the issue as a memory problem, locate the offending process, and determine whether or not this is a pool issue.
Exercise 2 – Leakyapp Behavior
Students will start leaky app and monitor memory, page file and cache counters to better understand the dynamics of these counters.
Exercise 3 – Process Swap Due To Minimizing of the Cmd Window
Students will start SQL from command line while viewing SQL process performance counters. Students will then minimize the window and note the effect on the working set.
Overview
What You Will Learn
After completing this lab, you will be able to:
Use some of the basic functions within System Monitor.
Troubleshoot one or more common performance scenarios.
Before You Begin
Prerequisites
To complete this lab, you need the following:
Windows 2000
SQL Server 2000
Lab Files Provided
LeakyApp.exe (Resource Kit)
Estimated time to complete this lab: 45 minutes
Exercise 1
Troubleshooting the Cardinal1.log File
In this exercise, you will analyze a log file from an actual system that was having performance problems. Like an actual support engineer, you will not have much information from which to draw conclusions. The customer has sent you this log file and it is up to you to find the cause of the problem. However, unlike the real world, you have an instructor available to give you hints should you become stuck.
Goal
Review the Cardinal1.log file (this file is from Windows NT 4.0 Performance Monitor, which Windows 2000 can read). Chart the log file and begin to investigate the counters to determine what is causing the performance problems. Your goal should be to isolate the problem to a major area such as pool, virtual address space etc, and begin to isolate the problem to a specific process or thread.
This lab requires access to the log file Cardinal1.log located in C:\LABS\M3\LAB1\EX1
To analyze the log file
1. Using the Performance MMC, select the System Monitor snap-in, and click the View Log File Data button (icon looks like a disk).
2. Under Files of type, choose PERFMON Log Files (*.log)
3. Navigate to the folder containing Cardinal1.log file and open it.
4. Begin examining counters to find what might be causing the performance problems.
When examining some of these counters, you may notice that some of them go off the top of the chart. It may be necessary to adjust the scale on these. This can be done by right-clicking the rightmost pane and selecting Properties. Select the Data tab. Select the counter that you wish to modify. Under the Scale option, change the scale value, which makes the counter data visible on the chart. You may need to experiment with different scale values before finding the ideal value. Also, it may sometimes be beneficial to adjust the vertical scale for the entire chart. Selecting the Graph tab on the Properties page can do this. In the Vertical scale area, adjust the Maximum and Minimum values to best fit the data on the chart.
Lab 3.1, Exercise 1: Results
Exercise 2
LeakyApp Behavior
In this lab, you will have an opportunity to work with a partner to monitor a live system, which is suffering from a simulated memory leak.
Goal
During this lab, your goal is to observe the system behavior when memory starts to become a limited resource. Specifically you will want to monitor committed memory, available memory, the system working set including the file system cache and each processes working set. At the end of the lab, you should be able to provide an answer to the listed questions.
To monitor a live system with a memory leak
1. Choose one of the two systems as a victim on which to run the leakyapp.exe program. It is recommended that you boot using the \MAXMEM=128 option so that this lab goes a little faster. You and your partner should decide which server will play the role of the problematic server and which server is to be used for monitoring purposes.
2. On the problematic server, start the leakyapp program.
3. On the monitoring system, create a counter that logs all necessary counters need to troubleshoot a memory problem. This should include physicaldisk counters if you think paging is a problem. Because it is likely that you will only need to capture less than five minutes of activity, the suggested interval for capturing is five seconds.
4. After the counters have been started, start the leaky application program
5. Click Start Leaking. The button will now change to Stop Leaking, which indicates that the system is now leaking memory.
6. After leakyapp shows the page file is 50 percent full, click Stop leaking. Note that the process has not given back its memory, yet. After approximately one minute, exit.
Lab 3.1, Exercise 2: Questions
After analyzing the counter logs you should be able to answer the following:
1. Under which system memory counter does the leak show up clearly?
Memory:Committed Bytes
2. What process counter looked very similar to the overall system counter that showed the leak?
Private Bytes
3. Is the leak in Paged Pool, Non-paged pool, or elsewhere?
Elsewhere
4. At what point did Windows 2000 start to aggressively trim the working sets of all user processes?
<5 MB Free
5. Was the System Working Set trimmed before or after the working sets of other processes?
After
6. What counter showed this?
Memory:Cache Bytes
7. At what point was the File System Cache trimmed?
After the first pass through all other working sets
8. What was the effect on all the processes working set when the application quit leaking?
None
9. What was the effect on all the working sets when the application exited?
Nothing, initially; but all grew fairly quickly based on use
10. When the server was running low on memory, which was Windows spending more time doing, paging to disk or in-paging?
Paging to disk, initially; however, as other applications began to run, in-paging increased
Exercise 3
Minimizing a Command Window
In this exercise, you will have an opportunity to observe the behavior of Windows 2000 when a command window is minimized.
Goal
During this lab, your goal is to observe the behavior of Windows 2000 when a command window becomes minimized. Specifically, you will want to monitor private bytes, virtual bytes, and working set of SQL Server when the command window is minimized. At the end of the lab, you should be able to provide an answer to the listed questions.
To monitor a command window’s working set as the window is minimized
1. Using System Monitor, create a counter list that logs all necessary counters needed to troubleshoot a memory problem. Because it is likely that you will only need to capture less than five minutes of activity, the suggested capturing interval is five seconds.
2. After the counters have been started, start a Command Prompt window on the target system.
3. In the command window, start SQL Server from the command line. Example: SQL Servr.exe –c –sINSTANCE1
4. After SQL Server has successfully started, Minimize the Command Prompt window.
5. Wait approximately two minutes, and then Restore the window.
6. Wait approximately two minutes, and then stop the counter log.
Lab 3.1, Exercise 3: Questions
After analyzing the counter logs you should be able to answer the following questions:
1. What was the effect on SQL Servers private bytes, virtual bytes, and working set when the window was minimized?
Private Bytes and Virtual Bytes remained the same, while Working Set went to 0
2. What was the effect on SQL Servers private bytes, virtual bytes, and working set when the window was restored?
None; the Working Set did not grow until SQL accessed the pages and faulted them back in on an as-needed basis
SQL Server Memory Overview
SQL Server Memory Overview
Now that you have a better understanding of how Windows 2000 manages memory resources, you can take a closer look at how SQL Server 2000 manages its memory. During the course of the lecture and labs you will have the opportunity to monitor SQL Servers use of memory under varying conditions using both System Monitor counters and SQL Server tools.
SQL Server Memory Management Goals
Because SQL Server has in-depth knowledge about the relationships between data and the pages they reside on, it is in a better position to judge when and what pages should be brought into memory, how many pages should be brought in at a time, and how long they should be resident.
SQL Servers primary goals for management of its memory are the following:
Be able to dynamically adjust for varying amounts of available memory.
Be able to respond to outside memory pressure from other applications.
Be able to adjust memory dynamically for internal components.
Items Covered
SQL Server Memory Definitions
SQL Server Memory Layout
SQL Server Memory Counters
Memory Configurations Options
Buffer Pool Performance and Counters
Set Aside Memory and Counters
General Troubleshooting Process
Memory Myths and Tips
SQL Server Memory Definitions
SQL Server Memory Definitions
Pool
A group of resources, objects, or logical components that can service a resource allocation request
Cache
The management of a pool or resource, the primary goal of which is to increase performance.
Bpool
The Bpool (Buffer Pool) is a single static class instance. The Bpool is made up of 8-KB buffers and can be used to handle data pages or external memory requests. There are three basic types or categories of committed memory in the Bpool.
Hashed Data Pages
Committed Buffers on the Free List
Buffers known by their owners (Refer to definition of Stolen)
Consumer
A consumer is a subsystem that uses the Bpool. A consumer can also be a provider to other consumers. There are five consumers and two advanced consumers who are responsible for the different categories of memory. The following list represents the consumers and a partial list of their categories
Connection – Responsible for PSS and ODS memory allocations
General – Resource structures, parse headers, lock manager objects
Utilities – Recovery, Log Manager
Optimizer – Query Optimization
Query Plan – Query Plan Storage
Advanced Consumer
Along with the five consumers, there are two advanced consumers. They are
Ccache – Procedure cache. Accepts plans from the Optimizer and Query Plan consumers. Is responsible for managing that memory and determines when to release the memory back to the Bpool.
Log Cache – Managed by the LogMgr, which uses the Utility consumer to coordinate memory requests with the Bpool.
Reservation
Requesting the future use of a resource. A reservation is a reasonable guarantee that the resource will be available in the future.
Committed
Producing the physical resource
Allocation
The act of providing the resource to a consumer
Stolen
The act of getting a buffer from the Bpool is referred to as stealing a buffer. If the buffer is stolen and hashed for a data page, it is referred to as, and counted as, a Hashed buffer, not a stolen buffer. Stolen buffers on the other hand are buffers used for things such as procedure cache and SRV_PROC structures.
Target
Target memory is the amount of memory SQL Server would like to maintain as committed memory. Target memory is based on the min and max server configuration values and current available memory as reported by the operating system. Actual target calculation is operating system specific.
Memory to Leave (Set Aside)
The virtual address space set aside to ensure there is sufficient address space for thread stacks, XPROCS, COM objects etc.
Hashed Page
A page in pool that represents a database page.
SQL Server Memory Layout
Virtual Address Space
When SQL Server is started the minimum of physical ram or virtual address space supported by the OS is evaluated. There are many possible combinations of OS versions and memory configurations. For example: you could be running Microsoft Windows 2000 Advanced Server with 2 GB or possibly 4 GB of memory. To avoid page file use, the appropriate memory level is evaluated for each configuration.
Important
Utilities can inject a DLL into the process address space by using
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Windows\AppInit_DLLs
When the USER32.dll library is mapped into the process space, so, too, are the DLLs listed in the Registry key. To determine what DLL’s are running in SQL Server address space you can use tlist.exe. You can also use a tool such as Depends from Microsoft or HandelEx from http://ww.sysinternals.com.
Memory to Leave
As stated earlier there are many possible configurations of physical memory and address space. It is possible for physical memory to be greater than virtual address space. To ensure that some virtual address space is always available for things such as thread stacks and external needs such as XPROCS, SQL Server reserves a small portion of virtual address space prior to determining the size of the buffer pool. This address space is referred to as Memory To Leave. Its size is based on the number of anticipated tread stacks and a default value for external needs referred to as cmbAddressSave. After reserving the buffer pool space, the Memory To Leave reservation is released.
Buffer Pool Space
During Startup, SQL Server must determine the maximum size of the buffer pool so that the BUF, BUFHASH and COMMIT BITMAP structures that are used to manage the Bpool can be created. It is important to understand that SQL Server does not take ‘max memory’ or existing memory pressure into consideration.
The reserved address space of the buffer pool remains static for the life of SQL Server process. However, the committed space varies as necessary to provide dynamic scaling. Remember only the committed memory effects the overall memory usage on the machine. This ensures that the max memory configuration setting can be dynamically changed with minimal changes needed to the Bpool. The reserved space does not need to be adjusted and is maximized for the current machine configuration. Only the committed buffers need to be limited to maintain a specified max server memory (MB) setting.
SQL Server Startup Pseudo Code
The following pseudo code represents the process SQL Server goes through on startup.
Warning
This example does not represent a completely accurate portrayal of the steps SQL Server takes when initializing the buffer pool. Several details have been left out or glossed over. The intent of this example is to help you understand the general process, not the specific details.
Determine the size of cmbAddressSave (-g)
Determine Total Physical Memory
Determine Available Physical Memory
Determine Total Virtual Memory
Calculate MemToLeave
maxworkterthreads * (stacksize=512 KB) + (cmbAddressSave = 256 MB)
Reserve MemToLeave and set PAGE_NOACCESS
Check for AWE, test to see if it makes sense to use it and log the results
• Min(Available Memory, Max Server Memory) > Virtual Memory
• Supports Read Scatter
• SQL Server not started with -f
• AWE Enabled via sp_configure
• Enterprise Edition
• Lock Pages In Memory user right enabled
Calculate Virtual Address Limit
VA Limit = Min(Physical Memory, Virtual Memory – MemtoLeave)
Calculate the number of physical and virtual buffers that can be supported
AWE Present
Physical Buffers = (RAM / (PAGESIZE + Physical Overhead))
Virtual Buffers = (VA Limit / (PAGESIZE + Virtual Overhead))
AWE Not Present
Physical Buffers = Virtual Buffers =
VA Limit / (PAGESIZE + Physical Overhead + Virtual Overhead)
Make sure we have the minimum number of buffers
Physical Buffers = Max(Physical Buffers, MIN_BUFFERS)
Allocate and commit the buffer management structures
Reserve the address space required to support the Bpool buffers
Release the MemToLeave
SQL Server Startup Pseudo Code Example
The following is an example based on the pseudo code represented on the previous page. This example is based on a machine with 384 MB of physical memory, not using AWE or /3GB.
Note
CmbAddressSave was changed between SQL Server 7.0 and SQL Server 2000. For SQL Server 7.0, cmbAddressSave was 128.
Warning
This example does not represent a completely accurate portrayal of the steps SQL Server takes when initializing the buffer pool. Several details have been left out or glossed over. The intent of this example is to help you understand the general process, not the specific details.
Determine the size of cmbAddressSave (No –g so 256MB)
Determine Total Physical Memory (384)
Determine Available Physical Memory (384)
Determine Total Virtual Memory (2GB)
Calculate MemToLeave
maxworkterthreads * (stacksize=512 KB) + (cmbAddressSave = 256 MB)
(255 * .5MB + 256MB = 384MB)
Reserve MemToLeave and set PAGE_NOACCESS
Check for AWE, test to see if it makes sense to use it and log the results
(AWE Not Enabled)
Calculate Virtual Address Limit
VA Limit = Min(Physical Memory, Virtual Memory – MemtoLeave)
384MB = Min(384MB, 2GB – 384MB)
Calculate the number of physical and virtual buffers that can be supported
AWE Not Present
48664 (approx) = 384 MB / (8 KB + Overhead)
Make sure we have the minimum number of buffers
Physical Buffers = Max(Physical Buffers, MIN_BUFFERS)
48664 = Max(48664,1024)
Allocate and commit the buffer management structures
Reserve the address space required to support the Bpool buffers
Release the MemToLeave
Tip
Trace Flag 1604 can be used to view memory allocations on startup. The cmbAddressSave can be adjusted using the –g XXX startup parameter.
SQL Server Memory Counters
SQL Server Memory Counters
The two primary tools for monitoring and analyzing SQL Server memory usage are System Monitor and DBCC MEMORYSTATUS. For detailed information on DBCC MEMORYSTATUS refer to Q271624 Interpreting the Output of the DBCC MEMORYSTAUS Command.
Important
Represents SQL Server 2000 Counters. The counters presented are not the same as the counters for SQL Server 7.0. The SQL Server 7.0 counters are listed in the appendix.
Determining Memory Usage for OS and BPOOL
Memory Manager: Total Server memory (KB) - Represents all of SQL usage
Buffer Manager: Total Pages - Represents total bpool usage
To determine how much of Total Server Memory (KB) represents MemToLeave space; subtract Buffer Manager: Total Pages. The result can be verified against DBCC MEMORYSTATUS, specifically Dynamic Memory Manager: OS In Use. It should however be noted that this value only represents requests that went thru the bpool. Memory reserved outside of the bpool by components such as COM objects will not show up here, although they will count against SQL Server private byte count.
Buffer Counts: Target (Buffer Manager: Target Pages)
The size the buffer pool would like to be. If this value is larger than committed, the buffer pool is growing.
Buffer Counts: Committed (Buffer Manager: Total Pages)
The total number of buffers committed in the OS. This is the current size of the buffer pool.
Buffer Counts: Min Free
This is the number of pages that the buffer pool tries to keep on the free list. If the free list falls below this value, the buffer pool will attempt to populate it by discarding old pages from the data or procedure cache.
Buffer Distribution: Free (Buffer Manager / Buffer Partition: Free Pages)
This value represents the buffers currently not in use. These are available for data or may be requested by other components and mar
817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
