社区
客服专区
帖子详情
win2000与sql server2000 enterprise edition之间的关系
chenyunbiao
2002-11-28 07:12:21
大家好请问各位高手 我在使用WIN2000操作系统,按装了Sql server2000,
在按装完成后,不能与本机的用户名进行注册,
换一台机器也是WIN2000操作系统正常,请问大家这里是不是有什么秘密呀
...全文
19
1
打赏
收藏
win2000与sql server2000 enterprise edition之间的关系
大家好请问各位高手 我在使用WIN2000操作系统,按装了Sql server2000, 在按装完成后,不能与本机的用户名进行注册, 换一台机器也是WIN2000操作系统正常,请问大家这里是不是有什么秘密呀
复制链接
扫一扫
分享
转发到动态
举报
写回复
配置赞助广告
用AI写文章
1 条
回复
切换为时间正序
请发表友善的回复…
发表回复
打赏红包
某鸟
2002-11-28
打赏
举报
回复
安装方式有区别。
SQL
Server
2005数据库安装简体中文企业完整版
自从
Server
2005去年11月上市以来,已经在全球92个国家安家落户,获得客户以及合作伙伴的好评及反馈,这些反馈对
SQL
Server
2005首个SP开发提供了巨大贡献。 微软
SQL
Server
2005 SP1加入数据库镜像功能,为
SQL
Server
2005 Express
Edit
ion
提供新管理工具,并且加强了SAP NetWeaver智能商务系统的报告反馈支持功能。 微软今天发布了
SQL
Server
2005 SP3的简体中文版,而这也将是该软件的最后一次升级服务。
SQL
Server
2005 SP3版本号9.00.4035,作为一个累计升级包集成了此前所有的更新补丁,能全方位提升
SQL
Server
2005的各种服务等级,适用于企业版、企业评估版、开发者版、标准版、工作组版等所有商业版本
微软内部资料-
SQL
性能优化2
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 communicat
ion
skills, and a proven troubleshooting methodology. In this module we will discuss Microsoft®
SQL
Server
™ interact
ion
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
Win
dows® 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 informat
ion
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 configurat
ion
opt
ion
s that affect memory. Describe how configurat
ion
opt
ion
s 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
Win
dows
2000
Resource Kit compan
ion
CD-ROM documentat
ion
. Chapter 15: Overview of Performance Monitoring Inside Microsoft
Win
dows
2000
, Third
Edit
ion
, David A. Solomon and Mark E. Russinovich
Win
dows
2000
Server
Operat
ion
s Guide, Storage, File Systems, and Printing; Chapters: Evaluating Memory and Cache Usage Advanced
Win
dows, 4th
Edit
ion
, Jeffrey Richter, Microsoft Press Related Web Sites http://ntperformance/ Memory Definit
ion
s Memory Definit
ion
s 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 follo
win
g definit
ion
s 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 operat
ion
system, there is normally a linear array of 2^32 addresses representing 4,294,967,269 byte addresses. Physical Memory A series of physical locat
ion
s, with unique addresses, that can be used to store instruct
ion
s or data. AWE – Address
Win
do
win
g Extens
ion
s 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
Win
do
win
g Extens
ion
s API, an applicat
ion
can create a fixed-size
win
dow into the addit
ion
al physical memory. This allows a process to access any port
ion
of the physical memory by mapping it into the applicat
ion
s
win
dow. When used in combinat
ion
with Intel’s Physical Addressing Extens
ion
s (PAE) on
Win
dows
2000
, an AWE enabled applicat
ion
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 violat
ion
. 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 representat
ion
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 violat
ion
, 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 transit
ion
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 informat
ion
, see also…Inside
Win
dows
2000
,Third
Edit
ion
, 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 informat
ion
, see also… Inside
Win
dows
2000
,Third
Edit
ion
, 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 informat
ion
, see also… Inside
Win
dows
2000
,Third
Edit
ion
, p. 463. System Cache The
Win
dows
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 informat
ion
, see also… Inside
Win
dows
2000
,Third
Edit
ion
, 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 informat
ion
regarding “maximums,” see also… Inside
Win
dows
2000
,Third
Edit
ion
, 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 informat
ion
regarding “limits,” see also… Inside
Win
dows
2000
,Third
Edit
ion
, 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 funct
ion
call addresses and parameters are temporarily stored. In Process To run in the same address space. In-process
server
s 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 unintent
ion
ally 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 partit
ion
s based on the underlying operating system. The diagram included at the top represents the address partit
ion
ing for the 32-bit vers
ion
of
Win
dows
2000
. Typically, the process address space is evenly divided into two 2-GB reg
ion
s. 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 applicat
ion
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 informat
ion
regarding address space layout, refer to Inside Microsoft
Win
dows
2000
Third
Edit
ion
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.
Win
dows
2000
does not provide any protect
ion
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, applicat
ion
such as
SQL
Server
could leverage more memory if it were available. The boot.ini opt
ion
/3GB was created for those cases where systems actually support greater than 2 GB of physical memory and an applicat
ion
can make use of it This capability allows memory intensive applicat
ion
s running on
Win
dows
2000
Advanced
Server
to use up to 50 percent more virtual memory on Intel-based computers. Applicat
ion
memory tuning provides more of the computer's virtual memory to applicat
ion
s 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
Win
dows
2000
Profess
ion
al or
Win
dows
2000
Server
with the /3GB boot opt
ion
, 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 opt
ion
on these system is the reduct
ion
in the amount of address space available to the system (ISW2K Pg. 418). Important If you use /3GB in conjunct
ion
with AWE/PAE you are limited to 16 GB of memory. For more informat
ion
, see the follo
win
g Knowledge Base articles: Q171793 Informat
ion
on Applicat
ion
Use of 4GT RAM Tuning Q126402 PagedPoolSize and NonPagedPoolSize Values in
Win
dows 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 operat
ion
system is limited to 4 GB of physical memory. However, by leveraging PAE,
Win
dows
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 opt
ion
. To allow processes to allocate more physical memory than can be represented in the 2GB of address space, Microsoft created the Address
Win
dows Extens
ion
s (AWE). These extens
ion
s allow for the allocat
ion
and use of up to the amount of physical memory supported by the operating system. By leveraging the Address
Win
do
win
g Extens
ion
s API, an applicat
ion
can create a fixed-size
win
dow into the physical memory. This allows a process to access any port
ion
of the physical memory by mapping reg
ion
s of physical memory in and out of the applicat
ion
s
win
dow. The allocat
ion
and use of AWE memory is accomplished by Creating a
win
dow via VirtualAlloc using the MEM_PHYSICAL opt
ion
Allocating the physical pages through AllocateUserPhysicalPages Mapping the RAM pages to the
win
dow using MapUserPhysicalPages Note
SQL
Server
7.0 supports a feature called extended memory in
Win
dows NT® 4
Enterprise
Edit
ion
by using a PSE36 driver. Currently there are no PSE drivers for
Win
dows
2000
. The preferred method of accessing extended memory is via the Physical Addressing Extens
ion
s 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
Win
dows
2000
this means
SQL
Server
7.0 cannot support more than 3GB of memory on
Win
dows
2000
. Refer to KB article Q278466. AWE restrict
ion
s 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 permiss
ion
s needed for
SQL
Server
. The Service Control Panel does NOT grant all the rights or permiss
ion
s needed to run
SQL
Server
. Pages are not shareable or page-able Page protect
ion
is limited to read/write The same physical page cannot be mapped into two separate AWE reg
ion
s, even within the same process. The use of AWE/PAE in conjunct
ion
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 applicat
ion
s. 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 determinat
ion
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 applicat
ion
s 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. Caut
ion
It is highly recommended that you use the “max
server
memory” opt
ion
in combinat
ion
with “awe enabled” to ensure some memory headroom exists for other applicat
ion
s or instances of
SQL
Server
, because AWE memory cannot be shared or paged. For more informat
ion
, see the follo
win
g Knowledge Base articles: Q268363 Intel Physical Addressing Extens
ion
s (PAE) in
Win
dows
2000
Q241046 Cannot Create a dump File on Computers with over 4 GB RAM Q255600
Win
dows
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 opt
ion
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
Win
dows
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 translat
ion
look aside buffers (TLB) to speed up the process. One of the reasons context switching is so expensive is the translat
ion
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.
Win
dows
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 investigat
ion
. 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 vie
win
g 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 indicat
ion
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
Server
s goal on Intel platforms, running
Win
dows 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 convers
ion
. 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 applicat
ion
s) and in non-cached memory mapped files. This counter counts numbers of read operat
ion
s, without regard to the numbers of pages retrieved by each operat
ion
. This counter displays the difference between the values observed in the last two samples, divided by the durat
ion
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 operat
ion
s, without regard to the number of pages written in each operat
ion
. This counter displays the difference between the values observed in the last two samples, divided by the durat
ion
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 convers
ion
. This counter displays the difference between the values observed in the last two samples, divided by the durat
ion
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.
Win
dows 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 convers
ion
. This counter displays the difference between the values observed in the last two samples, divided by the durat
ion
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 operat
ion
s. 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
Win
dows NT.
Win
dows 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 durat
ion
of the sample interval. Transit
ion
Faults/Sec (Soft Page Fault) Transit
ion
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 addit
ion
al disk activity. Transit
ion
faults are counted in numbers of faults, without regard for the number of pages faulted in each operat
ion
. This counter displays the difference between the values observed in the last two samples, divided by the durat
ion
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 port
ion
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 follo
win
g 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.
Win
dows
2000
Cache Manager works with the memory manager to provide virtual block stream and file data caching. For more informat
ion
, see also…Inside
Win
dows
2000
,Third
Edit
ion
, 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 informat
ion
, see also…Inside
Win
dows
2000
,Third
Edit
ion
, 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
Win
dows
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 informat
ion
regarding mapped views, see also…Inside
Win
dows
2000
,Third
Edit
ion
, p. 669. For those applicat
ion
s that would like to leverage system cache but cannot tolerate write delays, the cache manager supports write through operat
ion
s via the FILE_FLAG_WRITE_THROUGH. On the other hand, an applicat
ion
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 operat
ion
. 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 informat
ion
, see the follo
win
g 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 Violat
ion
on
Win
2000
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
Win
dows NT group. For more informat
ion
, see the follo
win
g Knowledge Base articles: Q265028 (MS) Pool Tags Q258793 (MS) How to Find Memory Leaks by Using Pool Bitmap Analysis Q115280 (MS) Finding
Win
dows NT Kernel Mode Memory Leaks Q177415 (MS) How to Use Poolmon to Troubleshoot Kernel Mode Memory Leaks Q126402 PagedPoolSize and NonPagedPoolSize Values in
Win
dows 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 Limitat
ion
s 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 sho
win
g 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
WIN
LOGON, SVCHOST, services, or SPOOLSV are referred to as HELPER processes. They provide core funct
ion
ality for many operat
ion
s and as such are often extended by the addit
ion
of third-party DLLs. Tlist –s may help identify what services are running under a particular helper. Helper Processes Helper Processes
Win
logon, Services, and Spoolsv and Svchost are examples of what are referred to as HELPER processes. They provide core funct
ion
ality for many operat
ion
s and, as such, are often extended by the addit
ion
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.
WIN
LOGON Used to support GINAs. SPOOLSV SPOOLSV is responsible for printing. You will need to investigate all added printing funct
ion
ality. 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 sess
ion
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 considerat
ion
s. 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 synchronizat
ion
, scheduling, I/O, memory management, and so on. Although this work is essential, it takes processor cycles and the cost, in cycles, to transit
ion
between user and kernel mode is expensive. Because all memory management and I/O funct
ion
s 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 Relat
ion
ships Troubleshooting performance is about analyzing trends and trend relat
ion
ships. 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
Server
s working set, or the disks
SQL
is using there may be little or no cause/affect relat
ion
ship. 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 combinat
ion
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 relat
ion
ships. After evaluating physical memory, you should be able to answer the follo
win
g quest
ion
s: 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 Contribut
ion
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 port
ion
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 gro
win
g, you should investigate each process to see if there is a specific process relat
ion
ship. If not you will have to use tools such as poolmon to investigate further. Review Process Memory Usage After you understand the physical memory limitat
ion
s and cache and pool contribut
ion
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 follo
win
g: Are any of the processes increasing their private bytes over time? Are any processes gro
win
g 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 relat
ion
ship between the above named resources and total committed memory or available memory? If there is a relat
ion
ship, is this normal behavior for the process in quest
ion
? 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 indicat
ion
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 port
ion
of the disk activity on the disk the applicat
ion
is actively using. After evaluating page fault activity, you should be able to answer the follo
win
g quest
ion
s: What is the relat
ion
ship between PageFaults/sec and Page Input/sec + Page Output/Sec? What is the relat
ion
ship if any between hard page faults and available memory? Does paging activity represent a significant port
ion
of processor or I/O resource usage? Don’t Prematurely Jump to Any Conclus
ion
s 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 relat
ion
ships between cause and effect that you can begin to understand the impact of a particular counter. The key thoughts to remember are: With the except
ion
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 applicat
ion
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
Win
dow Students will start
SQL
from command line while vie
win
g
SQL
process performance counters. Students will then minimize the
win
dow 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 funct
ion
s within System Monitor. Troubleshoot one or more common performance scenarios. Before You Begin Prerequisites To complete this lab, you need the follo
win
g:
Win
dows
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 informat
ion
from which to draw conclus
ion
s. 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
Win
dows NT 4.0 Performance Monitor, which
Win
dows
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 opt
ion
, 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 quest
ion
s. 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 opt
ion
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 applicat
ion
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: Quest
ion
s After analyzing the counter logs you should be able to answer the follo
win
g: 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
Win
dows
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 applicat
ion
quit leaking? None 9. What was the effect on all the working sets when the applicat
ion
exited? Nothing, initially; but all grew fairly quickly based on use 10. When the
server
was running low on memory, which was
Win
dows spending more time doing, paging to disk or in-paging? Paging to disk, initially; however, as other applicat
ion
s began to run, in-paging increased Exercise 3 Minimizing a Command
Win
dow In this exercise, you will have an opportunity to observe the behavior of
Win
dows
2000
when a command
win
dow is minimized. Goal During this lab, your goal is to observe the behavior of
Win
dows
2000
when a command
win
dow becomes minimized. Specifically, you will want to monitor private bytes, virtual bytes, and working set of
SQL
Server
when the command
win
dow is minimized. At the end of the lab, you should be able to provide an answer to the listed quest
ion
s. To monitor a command
win
dow’s working set as the
win
dow 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
win
dow on the target system. 3. In the command
win
dow, start
SQL
Server
from the command line. Example:
SQL
Servr.exe –c –sINSTANCE1 4. After
SQL
Server
has successfully started, Minimize the Command Prompt
win
dow. 5. Wait approximately two minutes, and then Restore the
win
dow. 6. Wait approximately two minutes, and then stop the counter log. Lab 3.1, Exercise 3: Quest
ion
s After analyzing the counter logs you should be able to answer the follo
win
g quest
ion
s: 1. What was the effect on
SQL
Server
s private bytes, virtual bytes, and working set when the
win
dow was minimized? Private Bytes and Virtual Bytes remained the same, while Working Set went to 0 2. What was the effect on
SQL
Server
s private bytes, virtual bytes, and working set when the
win
dow 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
Win
dows
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
Server
s use of memory under varying condit
ion
s using both System Monitor counters and
SQL
Server
tools.
SQL
Server
Memory Management Goals Because
SQL
Server
has in-depth knowledge about the relat
ion
ships between data and the pages they reside on, it is in a better posit
ion
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
Server
s primary goals for management of its memory are the follo
win
g: Be able to dynamically adjust for varying amounts of available memory. Be able to respond to outside memory pressure from other applicat
ion
s. Be able to adjust memory dynamically for internal components. Items Covered
SQL
Server
Memory Definit
ion
s
SQL
Server
Memory Layout
SQL
Server
Memory Counters Memory Configurat
ion
s Opt
ion
s Buffer Pool Performance and Counters Set Aside Memory and Counters General Troubleshooting Process Memory Myths and Tips
SQL
Server
Memory Definit
ion
s
SQL
Server
Memory Definit
ion
s Pool A group of resources, objects, or logical components that can service a resource allocat
ion
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 definit
ion
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 follo
win
g list represents the consumers and a partial list of their categories Connect
ion
– Responsible for PSS and ODS memory allocat
ion
s General – Resource structures, parse headers, lock manager objects Utilities – Recovery, Log Manager Optimizer – Query Optimizat
ion
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. Reservat
ion
Requesting the future use of a resource. A reservat
ion
is a reasonable guarantee that the resource will be available in the future. Committed Producing the physical resource Allocat
ion
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
configurat
ion
values and current available memory as reported by the operating system. Actual target calculat
ion
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 combinat
ion
s of OS vers
ion
s and memory configurat
ion
s. For example: you could be running Microsoft
Win
dows
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 configurat
ion
. Important Utilities can inject a DLL into the process address space by using HKEY_LOCAL_MACHINE\Software\Microsoft\
Win
dows NT\CurrentVers
ion
\
Win
dows\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 configurat
ion
s 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 port
ion
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 reservat
ion
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 considerat
ion
. 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 configurat
ion
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 configurat
ion
. Only the committed buffers need to be limited to maintain a specified max
server
memory (MB) setting.
SQL
Server
Startup Pseudo Code The follo
win
g 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
Edit
ion
• 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 follo
win
g 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 allocat
ion
s 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 informat
ion
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 gro
win
g. 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 Distribut
ion
: Free (Buffer Manager / Buffer Partit
ion
: 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
java软件工程师英文简历模板.doc
java软件工程师英文简历模板.doc
oracle培训材料.ppt
oracle培训材料.ppt
牛新庄:DB2使用经验
该文档包含了作者多年来的使用DB2的经验和学习方法,是提高使用DB2水平的良好文档。
客服专区
562
社区成员
48,791
社区内容
发帖
与我相关
我的任务
客服专区
客服专区
复制链接
扫一扫
分享
社区描述
客服专区
其他
技术论坛(原bbs)
社区管理员
加入社区
获取链接或二维码
近7日
近30日
至今
加载中
查看更多榜单
社区公告
暂无公告
试试用AI创作助手写篇文章吧
+ 用AI写文章