社区
Windows Server
帖子详情
WIN2000 Professional和SQL Server7.0不兼容??
earphone
2000-09-06 12:09:00
在WIN2000 Professional中是否能安装SQL Server7.0,为什么我每次安装都提示
WINDOWN版本不对,是否有支持WIN2000的SQL Server版本。
...全文
168
4
打赏
收藏
WIN2000 Professional和SQL Server7.0不兼容??
在WIN2000 Professional中是否能安装SQL Server7.0,为什么我每次安装都提示 WINDOWN版本不对,是否有支持WIN2000的SQL Server版本。
复制链接
扫一扫
分享
转发到动态
举报
写回复
配置赞助广告
用AI写文章
4 条
回复
切换为时间正序
请发表友善的回复…
发表回复
打赏红包
digboy
2000-10-09
打赏
举报
回复
现在sql server2000可以支持windows2000,我试过了,你也用用吧!
Cloudz
2000-09-19
打赏
举报
回复
和NT4&win2000一样,SQL7有三个版本
桌面版、标准版、企业版分别对应NT4 workstaion&win2000 Professional、NT4 server&win2000 server、NT4 enterprise&win2000 advance server
低的SQL7版本可以装在NT4&win2000高版本上,反之则不行
w102272
2000-09-19
打赏
举报
回复
sql server 2000已经出炉,装这个吧
Qlzeng
2000-09-07
打赏
举报
回复
SQL Server 7.0的Desktop Edition支持Windows 2000 Professional版本!
微软内部资料-
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 c
al
ls requires
pro
duct knowledge, good communication skills, and a
pro
ven troubleshooting methodology. In this module we will discuss Microsoft®
SQL
Server
™ interaction with the operating system and methodology of troubleshooting
server
-based
pro
blems. 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
pro
cessor 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 Technic
al
Reference, Microsoft Press
Win
dows
2000
Resource Kit companion CD-ROM documentation. Chapter 15: Overview of Performance Monitoring Inside Microsoft
Win
dows
2000
, Third Edition, David A. Solomon and Mark E. Russinovich
Win
dows
2000
Server
Operations Guide, Storage, File Systems, and Printing; Chapters: Ev
al
uating Memory and Cache Usage Advanced
Win
dows, 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 follo
win
g definitions will help you understand how
SQL
Server
interacts with the operating system when
al
locating and using memory. Virtu
al
Address Space A set of memory addresses that are mapped to physic
al
memory addresses by the system. In a 32-bit operation system, there is norm
al
ly a linear array of 2^32 addresses representing 4,294,967,269 byte addresses. Physic
al
Memory A series of physic
al
locations, with unique addresses, that can be used to store instructions or data. AWE – Address
Win
do
win
g Extensions A 32-bit
pro
cess is norm
al
ly 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 physic
al
memory available. By leveraging the Address
Win
do
win
g Extensions API, an application can create a fixed-size
win
dow into the addition
al
physic
al
memory. This
al
lows a
pro
cess to access any portion of the physic
al
memory by mapping it into the applications
win
dow. When used in combination with Intel’s Physic
al
Addressing Extensions (PAE) on
Win
dows
2000
, an AWE enabled application can support up to 64 GB of memory Reserved Memory Pages in a
pro
cesses address space are free, reserved or committed. Reserving memory address space is a way to reserve a range of virtu
al
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 physic
al
representation of a memory address. Page Fault (Soft/Hard) A reference to an inv
al
id 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,
al
l of these counters include file system cache activity. For more information, see
al
so…Inside
Win
dows
2000
,Third Edition, pp. 443-451. Private Bytes Private non-shared committed address space Working Set The subset of
pro
cesses virtu
al
pages that is resident in physic
al
memory. For more information, see
al
so… Inside
Win
dows
2000
,Third Edition, p. 455. System Working Set Like a
pro
cess, 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
al
so… Inside
Win
dows
2000
,Third Edition, p. 463. System Cache The
Win
dows
2000
cache manager
pro
vides data caching for both loc
al
and network file system drivers. By caching virtu
al
blocks, the cache manager can reduce disk I/O and
pro
vide intelligent read ahead. Represented by Memory:Cache Resident bytes. For more information, see
al
so… Inside
Win
dows
2000
,Third Edition, pp. 654-659. Non Paged Pool Range of addresses guaranteed to be resident in physic
al
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
al
lowed 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, typic
al
ly between 3-30 megabytes (MB) in size. Note The pool is, in effect, a common area of memory shared by
al
l
pro
cesses. One of the most common uses of non-paged pool is the storage of object handles. For more information regarding “maximums,” see
al
so… Inside
Win
dows
2000
,Third Edition, pp. 403-404 Paged Pool Range of address that can be paged in and out of physic
al
memory. Typic
al
ly 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. Typic
al
ly between 10-30MB + size of Registry. For more information regarding “limits,” see
al
so… Inside
Win
dows
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
pro
gram
pro
cedure or function c
al
l addresses and parameters are temporarily stored. In
Pro
cess To run in the same address space. In-
pro
cess
server
s are loaded in the client’s address space because they are implemented as DLLs. The main advantage of running in-
pro
cess is that the system usu
al
ly does not need to perform a context switch. The disadvantage to running in-
pro
cess is that DLL has access to the
pro
cess address space and can potenti
al
ly cause
pro
blems. Out of
Pro
cess To run outside the c
al
ling
pro
cesses address space. OLEDB
pro
viders can run in-
pro
cess or out of
pro
cess. When running out of
pro
cess, they run under the context of DLLHOST.EXE. Memory Leak To reserve or commit memory and unintention
al
ly not release it when it is no longer being used. A
pro
cess can leak resources such as
pro
cess memory, pool memory, user and GDI objects, handles, threads, and so on. Memory Concepts (X86 Address Space) Per
Pro
cess Address Space Every
pro
cess has its own private virtu
al
address space. For 32-bit
pro
cesses, that address space is 4 GB, based on a 32-bit pointer. Each
pro
cess’s virtu
al
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
Win
dows
2000
. Typic
al
ly, the
pro
cess address space is evenly divided into two 2-GB regions. Each
pro
cess 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, glob
al
variables, per-thread stacks, and DLL code would reside. The system address space is where the kernel, executive, H
AL
, boot drivers, page tables, pool, and system cache reside. For specific information regarding address space layout, refer to Inside Microsoft
Win
dows
2000
Third Edition pages 417-428 by Microsoft Press. Access Modes Each virtu
al
memory address is tagged as to what access mode the
pro
cessor must be running in. System space can only be accessed while in kernel mode, while user space is accessible in user mode. This
pro
tects system space from being tampered with by user mode code. Shared System Space
Al
though every
pro
cess has its own private memory space, kernel mode code and drivers share system space.
Win
dows
2000
does not
pro
vide any
pro
tection 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
Al
though 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 actu
al
ly support greater than 2 GB of physic
al
memory and an application can make use of it This capability
al
lows memory intensive applications running on
Win
dows
2000
Advanced
Server
to use up to 50 percent more virtu
al
memory on Intel-based computers. Application memory tuning
pro
vides more of the computer's virtu
al
memory to applications by
pro
viding less virtu
al
memory to the operating system.
Al
though a system having less than 2 GB of physic
al
memory can be booted using the /3G switch, in most cases this is ill-advised. If you restart with the 3 GB switch,
al
so known as 4-Gig Tuning, the amount of non-paged pool is reduced to 128 MB from 256 MB. For a
pro
cess 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
Pro
f
ession
al
or
Win
dows
2000
Server
with the /3GB boot option, users
pro
cesses 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 follo
win
g Knowledge Base articles: Q171793 Information on Application Use of 4GT RAM Tuning Q126402 PagedPoolSize and NonPagedPoolSize V
al
ues 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 Usu
al
ly, the operation system is limited to 4 GB of physic
al
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
pro
cess norm
al
ly has access to only 2 GB of address space, or 3 GB if the system was booted with the /3-GB option. To
al
low
pro
cesses to
al
locate more physic
al
memory than can be represented in the 2GB of address space, Microsoft created the Address
Win
dows Extensions (AWE). These extensions
al
low for the
al
location and use of up to the amount of physic
al
memory supported by the operating system. By leveraging the Address
Win
do
win
g Extensions API, an application can create a fixed-size
win
dow into the physic
al
memory. This
al
lows a
pro
cess to access any portion of the physic
al
memory by mapping regions of physic
al
memory in and out of the applications
win
dow. The
al
location and use of AWE memory is accomplished by Creating a
win
dow via Virtu
al
Al
loc using the MEM_PHYSIC
AL
option
Al
locating the physic
al
pages through
Al
locateUserPhysic
al
Pages Mapping the RAM pages to the
win
dow using MapUserPhysic
al
Pages Note
SQL
Server
7.0
supports a feature c
al
led extended memory in
Win
dows NT® 4 Enterprise Edition 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 Physic
al
Addressing Extensions using AWE. The AWE mapping feature is much more efficient than the older
pro
cess of coping buffers from extended memory into the
pro
cess 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 restrictions The
pro
cess 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
al
l of the privileges and Registry and file permissions needed for
SQL
Server
. The Service Control Panel does NOT grant
al
l the rights or permissions needed to run
SQL
Server
. Pages are not shareable or page-able Page
pro
tection is limited to read/write The same physic
al
page cannot be mapped into two separate AWE regions, even within the same
pro
cess. 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
al
located to AWE-enabled applications. You must use Memory Manager: Tot
al
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 ap
pro
priate for transferring dump files. With AWE enabled,
SQL
Server
will, by default,
al
locate
al
most
al
l memory during startup, leaving 256 MB or less free. This memory is locked and cannot be paged out. Consuming
al
l 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 physic
al
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 follo
win
g Knowledge Base articles: Q268363 Intel Physic
al
Addressing Extensions (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 physic
al
memory above 4GB Q274750 How to configure
SQL
Server
memory more than 2 GB (Idea) Q266251 Memory dump st
al
ls when PAE option is enabled (Idea) Tip The KB will return more hits if you query on PAE rather than AWE. Virtu
al
Address Space Mapping Virtu
al
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 virtu
al
addresses to physic
al
addresses. Each 32-bit address has three components, as shown below. When a
pro
cess accesses a virtu
al
address the system must first locate the Page Directory for the current
pro
cess via register CR3 (X86). The first 10 bits of the virtu
al
address act as an index into the Page Directory. The Page Directory Entry then points to the Page Frame Number (PFN) of the ap
pro
priate Page Table. The next 10 bits of the virtu
al
address act as an index into the Page Table to locate the ap
pro
priate page. If the page is v
al
id, the PTE contains the PFN of the actu
al
page in memory. If the page is not v
al
id, the memory management fault handler locates the page and attempts to make it v
al
id. The fin
al
12 bits act as a byte offset into the page. Note This multi-step
pro
cess is expensive. This is why systems have translation look aside buffers (TLB) to speed up the
pro
cess. 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 ev
al
uating 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 over
al
l view of the state of memory. The two key counters are Available Bytes and Committed Bytes. If Committed Bytes exceeds the amount of physic
al
memory in the system, you can be assured that there is some level of hard page fault activity happening. The go
al
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
pro
cess including the system cache. Committed Bytes Tot
al
memory, including physic
al
and page file currently committed Commit Limit • Physic
al
memory + page file size • Represents the tot
al
amount of memory that can be committed without expanding the page file. (Assuming page file is
al
lowed to grow) Available Bytes Tot
al
physic
al
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 ap
pro
ximately 4 MB by aggressively trimming the working sets including system cache. If this v
al
ue is constantly between 3-4 MB, it is cause for investigation. One counter you might expect would be for tot
al
physic
al
memory. Unfortunately, there is no specific counter for tot
al
physic
al
memory. There are however many other ways to determine tot
al
physic
al
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 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 ev
al
uating memory performance. As stated previously, no single counter
pro
vides 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 gener
al
, it is desirable to see Available Bytes above 5 MB.
SQL
Server
s go
al
on Intel platforms, running
Win
dows NT, is to assure there is ap
pro
ximately 5+ MB of free memory. After Available Bytes reaches 4 MB, the Working Set Manager will start to aggressively trim the working sets of
pro
cess and, fin
al
ly, the system cache. This is not to say that working set trimming does not happen before 4 MB, but it does become more
pro
nounced as the number of available bytes decreases below 4 MB. Page Faults/sec Page Faults/sec represents the tot
al
number of hard and soft page faults. This v
al
ue includes the System Working Set as well. Keep this in mind when ev
al
uating 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 v
al
ue 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 v
al
ue should be consistently low. In and of itself, a high v
al
ue for this counter does not necessarily indicate a
pro
blem. 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 critic
al
pro
cess 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 norm
al
user mode
pro
cessing. Tip (Memory: Pages/sec) / (Physic
al
Disk: Disk Bytes/sec * 4096) yields the ap
pro
ximate percentage of paging to tot
al
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 (usu
al
ly 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 v
al
ues observed in the last two samples, divided by the duration of the sample interv
al
. Page Writes/sec (Hard Page Fault) Page Writes/sec is the number of times pages were written to disk to free up space in physic
al
memory. Pages are written to disk only if they are changed while in physic
al
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 v
al
ues observed in the last two samples, divided by the duration of the sample interv
al
. *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 v
al
ues observed in the last two samples, divided by the duration of the sample interv
al
. This is one of the key counters to monitor for potenti
al
performance complaints. Because a
pro
cess must wait for a read page fault this counter, read page faults have a direct impact on the perceived performance of a
pro
cess. *Pages Output/sec (Hard Page Fault) Pages Output/sec is the number of pages written to disk to free up space in physic
al
memory. Pages are written back to disk only if they are changed in physic
al
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 physic
al
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 v
al
ues observed in the last two samples, divided by the duration of the sample interv
al
. Like Pages Input/sec, this is one of the key counters to monitor.
Pro
cesses will gener
al
ly not notice write page faults unless the disk I/O begins to interfere with norm
al
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
Win
dows NT.
Win
dows NT maintains a list of zeroed pages to accelerate this
pro
cess. 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 v
al
ues observed in the last two samples, divided by the duration of the sample interv
al
. 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 addition
al
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 v
al
ues observed in the last two samples, divided by the duration of the sample interv
al
. System Working Set System Working Set Like
pro
cesses, 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 actu
al
ly represent the entire system working set. Where noted system cache actu
al
ly represents the entire system working set. Note The counters listed are a subset of the counters you should capture. *Memory: Cache Bytes (Represents Tot
al
System Working Set) Represents the tot
al
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 physic
al
memory.
Win
dows
2000
Cache Manager works with the memory manager to
pro
vide virtu
al
block stream and file data caching. For more information, see
al
so…Inside
Win
dows
2000
,Third Edition, pp. 645-650 and p. 656. Memory: Pool Paged Resident Bytes Represents the physic
al
memory consumed by Paged Pool. This counter should NOT be monitored by itself. You must
al
so 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 physic
al
memory consumed by driver code and data. System Driver Resident Bytes and System Driver Tot
al
Bytes do not include code that must remain in physic
al
memory and cannot be written to disk. Memory: System Code Resident Bytes Represents the physic
al
memory consumed by page-able system code. System Code Resident Bytes and System Code Tot
al
Bytes do not include code that must remain in physic
al
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 tot
al
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
al
one. For more information, see
al
so…Inside
Win
dows
2000
,Third Edition, p. 656. Note You will find that in gener
al
the working set manager will usu
al
ly trim the working sets of norm
al
pro
cesses prior to trimming the system working set. System Cache System Cache The
Win
dows
2000
cache manager
pro
vides a write-back cache with lazy writing and intelligent read-ahead. Files are not written to disk immediately but differed until the cache manager c
al
ls the memory manager to flush the cache. This helps to reduce the tot
al
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 c
al
culate a larger v
al
ue. 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
al
so…Inside
Win
dows
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 unusu
al
to see the system cache consume a large amount of memory. Like other working sets, it is trimmed under pressure but is gener
al
ly 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 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 Violation 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 usu
al
ly 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
pro
cess can use paged pool by leveraging the Ex
Al
locatePool c
al
ls. Paged pool
al
so contains the Registry and file and printing structures. The primary counters for monitoring paged pool is Memory: Pool Paged Bytes. This counter will usu
al
ly be between 10-30MB plus the size of the Registry. To determine how much of paged pool is currently resident in physic
al
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
pro
cess, then it is most likely a pool leak. If the leak is not associated with
SQL
Server
handles, OLDEB
pro
viders, X
PRO
CS or SP_OA c
al
ls then most likely this c
al
l should be pushed to the
Win
dows NT group. For more information, see the follo
win
g Knowledge Base articles: Q265028 (MS) Pool Tags Q258793 (MS) How to Find Memory Leaks by Using Pool Bitmap An
al
ysis 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 V
al
ues 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
al
l drivers and third-party
pro
cesses. This should be done by disabling each service or driver one at a time and monitoring the effect. You can
al
so 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.
Pro
cess Memory Counters
Pro
cess _Tot
al
Limitations
Al
though the rollup of _Tot
al
for
Pro
cess: Private Bytes, Virtu
al
Bytes, Handles and Threads, represent the key resources being used across
al
l
pro
cesses, they can be misleading when ev
al
uating a memory leak. This is because a leak in one
pro
cess may be masked by a decrease in another
pro
cess. Note The counters listed are a subset of the counters you should capture. Tip When an
al
yzing 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
al
l
pro
cess. The primary counter used for leak an
al
ysis is private bytes, but
pro
cesses can leak handles and threads just as easily. After a suspect
pro
cess is located, build a separate chart that includes
al
l the counters for that
pro
cess. Individu
al
Pro
cess Counters When an
al
yzing individu
al
pro
cess for memory leaks you should include the counters listed.
Pro
cess: %
Pro
cessor Time
Pro
cess: Working Set (includes shared pages)
Pro
cess: Virtu
al
Bytes
Pro
cess: Private Bytes
Pro
cess: Page Faults/sec
Pro
cess: Handle Count
Pro
cess: Thread Count
Pro
cess: Pool Paged Bytes
Pro
cess: Pool Nonpaged Bytes Tip
WIN
LOGON, SVCHOST, services, or SPOOLSV are referred to as HELPER
pro
cesses. They
pro
vide core function
al
ity 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
Pro
cesses Helper
Pro
cesses
Win
logon, Services, and Spoolsv and Svchost are examples of what are referred to as HELPER
pro
cesses. They
pro
vide core function
al
ity for many operations and, as such, are often extended by the addition of third-party DLLs. Running every service in its own
pro
cess can waste system resources. Consequently, some services run in their own
pro
cesses while others share a
pro
cess with other services. One
pro
blem with sharing a
pro
cess is that a bug in one service may cause the entire
pro
cess to fail. The resource kit tool, Tlist when used with the –s qu
al
ifier can help you identify what services are running in what
pro
cesses.
WIN
LOGON Used to support GINAs. SPOOLSV SPOOLSV is responsible for printing. You will need to investigate
al
l added printing function
al
ity. Services Service is responsible for system services. Svchost.exe Svchost.exe is a generic host
pro
cess 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 s
ession
can contain a grouping of services, so that separate services can be run depending on how and where Svchost.exe is started. This
al
lows for better control and debugging. The Effect of Memory on Other Components Memory Drives Over
al
l Performance
Pro
cessor, cache, bus speeds, I/O,
al
l of these resources play a roll in over
al
l 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, potenti
al
ly 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,
al
l of the above events will become more
pro
nounced and have a larger impact on disk activity. Effect on CPU The most effective use of a
pro
cessor from a
pro
cess perspective is to spend as much time possible executing user mode code. Kernel mode represents
pro
cessor time associated with doing work, directly or indirectly, on beh
al
f of a thread. This includes items such as synchronization, scheduling, I/O, memory management, and so on.
Al
though this work is essenti
al
, it takes
pro
cessor cycles and the cost, in cycles, to transition between user and kernel mode is expensive. Because
al
l 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. An
al
yzing Memory Look for Trends and Trend Relationships Troubleshooting performance is about an
al
yzing 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 individu
al
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 relationship. Look at Physic
al
Memory First The first item to look at is physic
al
memory. You need to know how much physic
al
and page file space the system has to work with. You should then ev
al
uate 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 go
al
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 ev
al
uating physic
al
memory, you should be able to answer the follo
win
g questions: How much physic
al
memory do I have? What is the commit limit? Of that physic
al
memory, how much has the operating system committed? Is the operating system over committing physic
al
memory? What was the peak commit charge? How much available physic
al
memory is there? What is the trend associated with committed and available? Review System Cache and Pool Contribution After you understand the individu
al
pro
cess memory usage, you need to ev
al
uate the System Cache and Pool usage. These can and often represent a significant portion of physic
al
memory. Be aware that System Cache can grow significantly on a file
server
. This is usu
al
ly norm
al
. 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
al
so important to consider. An ever-increasing pool should be an indicator for further research. Non-paged pool growth is usu
al
ly a driver issue, while paged pool could be driver-related or
pro
cess-related. If paged pool is steadily gro
win
g, you should investigate each
pro
cess to see if there is a specific
pro
cess relationship. If not you will have to use tools such as poolmon to investigate further. Review
Pro
cess Memory Usage After you understand the physic
al
memory limitations and cache and pool contribution you need to determine what components or
pro
cesses are creating the pressure on memory, if any. Be careful if you opt to chart the _Tot
al
Private Byte’s rollup for
al
l
pro
cesses. This v
al
ue can be misleading in that it includes shared pages and can therefore exceed the actu
al
amount of memory being used by the
pro
cesses. The _Tot
al
rollup can
al
so mask
pro
cesses that are leaking memory because other
pro
cesses may be freeing memory thus creating a b
al
ance between leaked and freed memory. Identify
pro
cesses that expand their working set over time for further an
al
ysis.
Al
so, review handles and threads because both use resources and potenti
al
ly can be mismanaged. After ev
al
uating the
pro
cess resource usage, you should be able to answer the follo
win
g: Are any of the
pro
cesses increasing their private bytes over time? Are any
pro
cesses gro
win
g their working set over time? Are any
pro
cesses increasing the number of threads or handles over time? Are any
pro
cesses increasing their use of pool over time? Is there a direct relationship between the above named resources and tot
al
committed memory or available memory? If there is a relationship, is this norm
al
behavior for the
pro
cess 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
pro
cess 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 X
PRO
CS, SP_OA* c
al
ls or OLDB
pro
viders. 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
pro
cess 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.
Al
l of these features leverage the ability of the system to fault in pages as needed The greatest impact of paging on a
pro
cess is when the
pro
cess 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 ev
al
uating page fault activity, you should be able to answer the follo
win
g 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
pro
cessor or I/O resource usage? Don’t Prematurely Jump to Any Conclusions An
al
yzing memory pressure takes time and patience. An individu
al
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
pro
cess’s working set has been swapped out/in), hard page faults to resolve reads, are the most expensive in terms its effect on a
pro
cesses perceived performance. In gener
al
, page writes associated with page faults do not directly affect a
pro
cess’s perceived performance, unless that
pro
cess is waiting on a free page to be made available. Page file activity can become a
pro
blem 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 An
al
yzing System Memory Using System Monitor Exercise 1 – Troubleshooting the Cardin
al
1.log File Students will ev
al
uate an existing System Monitor log and determine if there is a
pro
blem and what the
pro
blem is. Students should be able to isolate the issue as a memory
pro
blem, locate the offending
pro
cess, 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 –
Pro
cess Swap Due To Minimizing of the Cmd
Win
dow Students will start
SQL
from command line while vie
win
g
SQL
pro
cess 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 functions 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
Pro
vided LeakyApp.exe (Resource Kit) Estimated time to complete this lab: 45 minutes Exercise 1 Troubleshooting the Cardin
al
1.log File In this exercise, you will an
al
yze a log file from an actu
al
system that was having performance
pro
blems. Like an actu
al
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
pro
blem. However, unlike the re
al
world, you have an instructor available to give you hints should you become stuck. Go
al
Review the Cardin
al
1.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
pro
blems. Your go
al
should be to isolate the
pro
blem to a major area such as pool, virtu
al
address space etc, and begin to isolate the
pro
blem to a specific
pro
cess or thread. This lab requires access to the log file Cardin
al
1.log located in C:\LABS\M3\LAB1\EX1 To an
al
yze 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 Cardin
al
1.log file and open it. 4. Begin examining counters to find what might be causing the performance
pro
blems. 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 sc
al
e on these. This can be done by right-clicking the rightmost pane and selecting
Pro
perties. Select the Data tab. Select the counter that you wish to modify. Under the Sc
al
e option, change the sc
al
e v
al
ue, which makes the counter data visible on the chart. You may need to experiment with different sc
al
e v
al
ues before finding the ide
al
v
al
ue.
Al
so, it may sometimes be benefici
al
to adjust the vertic
al
sc
al
e for the entire chart. Selecting the Graph tab on the
Pro
perties page can do this. In the Vertic
al
sc
al
e area, adjust the Maximum and Minimum v
al
ues 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. Go
al
During this lab, your go
al
is to observe the system behavior when memory starts to become a limited resource. Specific
al
ly you will want to monitor committed memory, available memory, the system working set including the file system cache and each
pro
cesses working set. At the end of the lab, you should be able to
pro
vide 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
pro
gram. 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
pro
blematic
server
and which
server
is to be used for monitoring purposes. 2. On the
pro
blematic
server
, start the leakyapp
pro
gram. 3. On the monitoring system, create a counter that logs
al
l necessary counters need to troubleshoot a memory
pro
blem. This should include physic
al
disk counters if you think paging is a
pro
blem. Because it is likely that you will only need to capture less than five minutes of activity, the suggested interv
al
for capturing is five seconds. 4. After the counters have been started, start the leaky application
pro
gram 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
pro
cess has not given back its memory, yet. After ap
pro
ximately one minute, exit. Lab 3.1, Exercise 2: Questions After an
al
yzing 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
pro
cess counter looked very similar to the over
al
l 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
al
l user
pro
cesses? <5 MB Free 5. Was the System Working Set trimmed before or after the working sets of other
pro
cesses? After 6. What counter showed this? Memory:Cache Bytes 7. At what point was the File System Cache trimmed? After the first pass through
al
l other working sets 8. What was the effect on
al
l the
pro
cesses working set when the application quit leaking? None 9. What was the effect on
al
l the working sets when the application exited? Nothing, initi
al
ly; but
al
l 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, initi
al
ly; however, as other applications 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. Go
al
During this lab, your go
al
is to observe the behavior of
Win
dows
2000
when a command
win
dow becomes minimized. Specific
al
ly, you will want to monitor private bytes, virtu
al
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
pro
vide an answer to the listed questions. 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
al
l necessary counters needed to troubleshoot a memory
pro
blem. Because it is likely that you will only need to capture less than five minutes of activity, the suggested capturing interv
al
is five seconds. 2. After the counters have been started, start a Command
Pro
mpt
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
Pro
mpt
win
dow. 5. Wait ap
pro
ximately two minutes, and then Restore the
win
dow. 6. Wait ap
pro
ximately two minutes, and then stop the counter log. Lab 3.1, Exercise 3: Questions After an
al
yzing the counter logs you should be able to answer the follo
win
g questions: 1. What was the effect on
SQL
Server
s private bytes, virtu
al
bytes, and working set when the
win
dow was minimized? Private Bytes and Virtu
al
Bytes remained the same, while Working Set went to 0 2. What was the effect on
SQL
Server
s private bytes, virtu
al
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 conditions using both System Monitor counters and
SQL
Server
tools.
SQL
Server
Memory Management Go
al
s 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
Server
s primary go
al
s for management of its memory are the follo
win
g: Be able to dynamic
al
ly adjust for varying amounts of available memory. Be able to respond to outside memory pressure from other applications. Be able to adjust memory dynamic
al
ly for intern
al
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 Gener
al
Troubleshooting
Pro
cess Memory Myths and Tips
SQL
Server
Memory Definitions
SQL
Server
Memory Definitions Pool A group of resources, objects, or logic
al
components that can service a resource
al
location request Cache The management of a pool or resource, the primary go
al
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 extern
al
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
al
so be a
pro
vider 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 parti
al
list of their categories Connection – Responsible for PSS and ODS memory
al
locations Gener
al
– Resource structures, parse headers, lock manager objects Utilities – Recovery, Log Manager Optimizer – Query Optimization Query Plan – Query Plan Storage Advanced Consumer
Al
ong with the five consumers, there are two advanced consumers. They are Ccache –
Pro
cedure 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
Pro
ducing the physic
al
resource
Al
location The act of
pro
viding the resource to a consumer Stolen The act of getting a buffer from the Bpool is referred to as ste
al
ing 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
pro
cedure cache and SRV_
PRO
C 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 v
al
ues and current available memory as reported by the operating system. Actu
al
target c
al
culation is operating system specific. Memory to Leave (Set Aside) The virtu
al
address space set aside to ensure there is sufficient address space for thread stacks, X
PRO
CS, COM objects etc. Hashed Page A page in pool that represents a database page.
SQL
Server
Memory Layout Virtu
al
Address Space When
SQL
Server
is started the minimum of physic
al
ram or virtu
al
address space supported by the OS is ev
al
uated. There are many possible combinations of OS versions and memory configurations. 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 ap
pro
priate memory level is ev
al
uated for each configuration. Important Utilities can inject a DLL into the
pro
cess address space by using HKEY_LOC
AL
_MACHINE\Software\Microsoft\
Win
dows NT\CurrentVersion\
Win
dows\AppInit_DLLs When the USER32.dll library is mapped into the
pro
cess 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
al
so use a tool such as Depends from Microsoft or HandelEx from http://ww.sysintern
al
s.com. Memory to Leave As stated earlier there are many possible configurations of physic
al
memory and address space. It is possible for physic
al
memory to be greater than virtu
al
address space. To ensure that some virtu
al
address space is
al
ways available for things such as thread stacks and extern
al
needs such as X
PRO
CS,
SQL
Server
reserves a sm
al
l portion of virtu
al
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 v
al
ue for extern
al
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
pro
cess. However, the committed space varies as necessary to
pro
vide dynamic sc
al
ing. Remember only the committed memory effects the over
al
l memory usage on the machine. This ensures that the max memory configuration setting can be dynamic
al
ly changed with minim
al
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 follo
win
g pseudo code represents the
pro
cess
SQL
Server
goes through on startup. Warning This example does not represent a completely accurate portray
al
of the steps
SQL
Server
takes when initi
al
izing the buffer pool. Sever
al
details have been left out or glossed over. The intent of this example is to help you understand the gener
al
pro
cess, not the specific details. Determine the size of cmbAddressSave (-g) Determine Tot
al
Physic
al
Memory Determine Available Physic
al
Memory Determine Tot
al
Virtu
al
Memory C
al
culate 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) > Virtu
al
Memory • Supports Read Scatter •
SQL
Server
not started with -f • AWE Enabled via sp_configure • Enterprise Edition • Lock Pages In Memory user right enabled C
al
culate Virtu
al
Address Limit VA Limit = Min(Physic
al
Memory, Virtu
al
Memory – MemtoLeave) C
al
culate the number of physic
al
and virtu
al
buffers that can be supported AWE Present Physic
al
Buffers = (RAM / (PAGESIZE + Physic
al
Overhead)) Virtu
al
Buffers = (VA Limit / (PAGESIZE + Virtu
al
Overhead)) AWE Not Present Physic
al
Buffers = Virtu
al
Buffers = VA Limit / (PAGESIZE + Physic
al
Overhead + Virtu
al
Overhead) Make sure we have the minimum number of buffers Physic
al
Buffers = Max(Physic
al
Buffers, MIN_BUFFERS)
Al
locate 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 physic
al
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 portray
al
of the steps
SQL
Server
takes when initi
al
izing the buffer pool. Sever
al
details have been left out or glossed over. The intent of this example is to help you understand the gener
al
pro
cess, not the specific details. Determine the size of cmbAddressSave (No –g so 256MB) Determine Tot
al
Physic
al
Memory (384) Determine Available Physic
al
Memory (384) Determine Tot
al
Virtu
al
Memory (2GB) C
al
culate 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) C
al
culate Virtu
al
Address Limit VA Limit = Min(Physic
al
Memory, Virtu
al
Memory – MemtoLeave) 384MB = Min(384MB, 2GB – 384MB) C
al
culate the number of physic
al
and virtu
al
buffers that can be supported AWE Not Present 48664 (ap
pro
x) = 384 MB / (8 KB + Overhead) Make sure we have the minimum number of buffers Physic
al
Buffers = Max(Physic
al
Buffers, MIN_BUFFERS) 48664 = Max(48664,1024)
Al
locate 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
al
locations 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 an
al
yzing
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: Tot
al
Server
memory (KB) - Represents
al
l of
SQL
usage Buffer Manager: Tot
al
Pages - Represents tot
al
bpool usage To determine how much of Tot
al
Server
Memory (KB) represents MemToLeave space; subtract Buffer Manager: Tot
al
Pages. The result can be verified against DBCC MEMORYSTATUS, specific
al
ly Dynamic Memory Manager: OS In Use. It should however be noted that this v
al
ue 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,
al
though 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 v
al
ue is larger than committed, the buffer pool is gro
win
g. Buffer Counts: Committed (Buffer Manager: Tot
al
Pages) The tot
al
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 f
al
ls below this v
al
ue, the buffer pool will attempt to populate it by discarding old pages from the data or
pro
cedure cache. Buffer Distribution: Free (Buffer Manager / Buffer Partition: Free Pages) This v
al
ue represents the buffers currently not in use. These are available for data or may be requested by other components and mar
在存储过程中调用外部的动态连接库(MS
SQL
Server
7.0
/
2000
环境)
问题的提出: 一般我们要根据数据库的纪录变化时,进行某种操作。我们习惯的操作方式是在程序中不停的查询表,判断是否有新纪录。这样耗费的资源就很高,如何提高这种效 率,我想在表中创建触发器,在触发器中调用外部动态连接库通过消息或事件通知应用程序就可实现。而master的存储过程中最好能调用外部的动态连接库, 我们在触发器中调用master的存
SQL
Server
2000
服务器安装剖析
/*标题:
SQL
Server
2000
服务器安装剖析作者:爱新觉罗.毓华 时间:2008-05-05地点:广东深圳*/一、情况说明
sql
server
2000
以前的版本,例如
7.0
一般不存在多个版本,只有标准版跟桌面版,用户如果不清楚该装什么版本的话,可按安装上的安装先决条件指示安装,一般在
WIN
2000
服务器版上装标准版,其他的系统装桌面版的就可以;而
SQL
Server
200
Sql
Server
和Oracle
SQL
语句可以用来执行各种各样的操作,例如更新数据库中的数据,从数据库中提取数据等。目前,绝大多数流行的关系型数据库管理系统,如Oracle, Sybase, Microsoft
SQL
Server
, Access等都采用了
SQL
语言标准。虽然很多数据库都对
SQL
语句进行了再开发和扩展,但是包括Select, Insert, Update, Delete, Create,以及Drop在内的标准的...
SQL
Server
服务器安装剖析
SQL
Server
服务器安装剖析
sql
server
2000
以前的版本,例如
7.0
一般不存在多个版本,只有标准版跟桌面版,用户如果不清楚该装什么版本的话,可按安装上的安装先决条件指示安装,一般在
WIN
2000
服务器版上装标准版,其他的系统装桌面版的就可以;而
SQL
Server
2000
安装问题就比较大,时常见问题有如下: (1)配置服务器时中断. (2)注册 ActiveX 时中断. (...
Windows Server
6,847
社区成员
178,035
社区内容
发帖
与我相关
我的任务
Windows Server
Windows 2016/2012/2008/2003/2000/NT
复制链接
扫一扫
分享
社区描述
Windows 2016/2012/2008/2003/2000/NT
社区管理员
加入社区
获取链接或二维码
近7日
近30日
至今
加载中
查看更多榜单
社区公告
暂无公告
试试用AI创作助手写篇文章吧
+ 用AI写文章