第11章 SQL
Server内存分配理念和常见内存问题 454
·
SQL Server为什么占用那么多的内存 –
缓存数据，以便提高性能。
11.1.
从操作系统层面看SQL Server内存分配 457
11.1.1.
Windows的一些内存术语 457
·
Virtual address space – 一个应用程序能够申请访问的最大地址空间。不总是在内存中，Windows will decide when to use
physical memory or page file to store the data.
·
SQL Server内存有几个概念（not exclusive, not inclusive）
o
Physical memory – 内存芯片提高的物理存储空间
o
Reserved memory – 在Virtual address
space预留的一部分地址空间，以供将来使用, 还不是真正的物理空间（需要commit后才行）。If you visit a space in reserved
memory, you will get the Access Violation error.
o
Committed memory: commit the reserved memory, after
commitment, the committed pages become effective pages in physical memory. The
key idea is to reduce the use of physical memory by delaying the commitment of
some pages.
o
Shared memory – visible to multiple threads, not too many in SQL
Server
o
Private bytes – the non-shared part in the committed memory
o
Working set – the part stored in the physical memory.
·
Page Fault（页面访问错误 - when
a program accesses a page that is mapped in
the virtual address
space, but not loaded in physical memory，换句话说，访问一个存在于虚拟地址空间，但不存在物理内存中的页面时，就会发生Page Fault。可能有三种情况，一是Access Violation, 即你想访问一个保留内存。二是目标页面还在硬盘中，这时会产生硬盘读写，成为Hard Fault。最后一种情况是目标页面存在于物理内存中，但还没有直接放在这个进程的working set下。需要Windows重定向，这种不会导致硬盘操作，成为soft fault，一般soft带来的性能影响可以忽略，只需关心hard fault。
·
System working set - Windows system uses physical memory and
working set as well, including
o
System Cache（系统高速缓存）：用于映射在系统高速缓存中打开的文件页面， 以提高磁盘I/O速度，可以用性能监视器：Memory：cache resident bytes监控。
o
Paged pool（页交换区）：系统空间中可以调入或调出系统进程工作集（Working set）的虚拟内存区域。可以通过Memory：Pool Paged Bytes和Memory：Pool paged resident bytes监控。
o
Non Paged Pool （非页交换区）：包含一定范围内的系统虚拟地址的内存交换区，可以保证在任何时候都驻留在物理内存中，可以通过Memory: Pool
Nonpaged Bytes来监控。这一块缓存可以被所有的经常共享，一个最常见的用途是存放所有对象的指针（Object Handles）
·
Stack（栈）：每个线程有两个Stacks，一个给内核模式（kernel mode），一个给用户模式（user mode）。每个Stack是一块内存空间，存放线程运行的过程或函数的调用地址，以及所有参数的值。
·
In Process: 运行在同一个进程的地址空间里。
·
Out of Process：运行在不同的进程地址空间里。
·
Memory Leak（内存泄漏）：一直不断地 Reserve或Commit，但就是不释放给其它用户重用。共有2种：一种是SQL Server作为进程，不断地向Windows申请内存资源直到整个Windows内存耗尽。另一种是SQL Server 内部，某个SQL Server组件不断地申请内存，直到把能申请到的内存耗尽，使得其它组件不能正常运行。前一种情况非常少见。后一种常由客服端操作引起。
11.1.2.
32位下Windows的地址空间及awe 460
·
SQL Server can have only a maximum of 2G in 32 bit
·
Default: Total 4G in 32-bit Windows, 2G for Kernel Mode, 2G for
User Mode (SQL Server runs in user mode)
·
Enhancement 1: You can change boot.ini to change kernel part to
1GB, and increase the user part to 3GB.
·
Enhancement 2: use Address Windowing Extension (AWE) to increase
the memory to 64GB for SQL Server.
·
SQL Server 2012 does not support AWE
anymore. So do not install it on a 32-bit machine
11.1.3.
Windows层面上的内存使用检查 463 – 查看performance counters
·
Overall usage analysis
o
COMMITTED BYTES – Total memory used by all threads, including data
in the physical memory and file paging.
o
Commit Limit – the maximum memory available. If committed bytes is
close to commit limit and the paging file cannot be automatically grown, the
system cannot provide additional memory anymore.
o
Available Mbytes – free physical memory. SQL Server often leaves
little to the operating system. The higher, the better.
o
Page file: %usage and page file: % peak usage – the higher, the
worse.
o
Pages/sec: the total number of pages (read and write) from the
disk for Windows and SQL Server and other applications. The lower, the better
·
Memory usage by Windows itself
o
一般几百M on a 32-bit machine, 1-2 GB on a
64-bit machine.
o
Counters - Memory: Cache Bytes = the working set for the Windows
system， 是以下的Sum:
ü
Memory: system cache resident bytes
ü
Memory: system driver resident bytes
ü
Memory: system code resident bytes
ü
Memory: pool paged resident bytes
·
System pool (系统空间中可以调入或调出Working Set的内存虚拟区域)- Check two counters:
o
Memory: Pool Paged Bytes
o
Memory: Pool Paged Resident Bytes
·
单个process的使用情况
o
Need to check many counters under the process object for each process, especially the private bytes （非共享）and working set, to find out which process uses the largest
memory, why the memory usage keep growing, or when the sudden changes happen.
11.1.4.
内存使用和其它系统资源的关系 468
·
Memory and disk: a busy diskècaused by memory or by disk itself?
·
Memory and CPU: high CPU time under the user mode indicates no
bottleneck on memory or disk, if high CPU time in the kernel mode, it often
means there is memory bottleneck, but the overall CPU usage is often is
<50%.
·
Recommendation on analyzing memory-related problems
o
32-bit o 64-bit for the OS and SQL Server?
o
Counters are related, do not make decisions based on individual
counters
o
Always start with analysis on memory
o
Do not forget the memory used by the OS itself
o
Tracking the counters for each process, do you see
ü
Private bytes keep growing?
ü
Working set keep growing?
ü
Handle leak or thread leak?
ü
Does the growth make the memory hard for other threads?
ü
Does the growth related to other problems?
o
Think memory, CPU, and disk together
11.1.5.
SQL内存使用和Windows之间的关系 470 – SQL Server会动态使用系统内存
·
How SQL Server shares memory with Windows and other programs？
o
First, as an application program, SQL Server is constrained by the
virtual address space (e.g., 2G on a 32-bit machine)
o
Second, SQL Server likes to occupy as much as memory possible
o
Third, SQL Server manages memory dynamically by changing the max
server memory and target server memory。这里牵涉到几个容易混淆的概念。
ü
Total Server Memory – Specifies the
amount of memory the server has committed using the memory manager， 即服务器实际上已使用的内存量。SQL Server Total Server Memory不能超过Max
Server Memory。另外，注意，Total Server Memory不是SQL Server使用的总内存，只是Buffer Pool的大小。SQL Server内存使用还包括了MemToLeave（for Non-SQL objects）。所以，有时候，SQL Server的Virtual Bytes（The Process: Virtual Bytes counter indicates the current size of
the Virtual Address Space that the process is using.）或Working Set大于你设置的Max Server Memory也是很正常的(因为MemToLeave)，并非内存泄漏。
ü
Target Server Memory –
SQL Server在理论上能够使用的最多的内存数目。SQL Server启动时，会根据以下几个设置，取最小值作为Target
Server Memory： AWE开启否？Max Server Memory的设置是多少？当前服务器可用的物理内存？
ü
Examples to figure out
the Target Server Memory
Ø
32-bit, 2GB物理内存，可用物理内存1.5GB，Max Server Memory的值是默认值 (即2,
147,483,647 MB or 2.1 petabytes!)
。Answer：Target Server Memory应该就是可用物理内存1.5GB。Total Server Memory不知道，用多少就是多少。
Ø
32-bit, 4GB物理内存，可用物理内存3.5GB，Max Server Memory的值是默认值。Answer：由于AWE未开启，所以Target Server Memory是2GB。
Ø
64-bit, 8GB物理内存，可用物理内存7GB，Max Server Memory的值是默认值。Answer：Target Server Memory是7GB。
Ø
32-bit, 8GB物理内存，AWE开启，可用物理内存7GB，Max Server Memory是6GB。Answer：Target Server Memory是6GB。
ü
（1）当Total Server Memory（实际已用的）小于Target Server Memory（理论上可用的）时，说明SQL
Server还没有用足系统能够给与SQL
Server的所有内存。SQL
Server会不断的缓存新的数据页和执行计划，而不会对这2部分缓存做清理，这样SQL
Server的内存使用量会逐渐增加。
（2）当Total Server Memory等于Target Server Memory时，SQL Server知道自己已经用足了能够给与的内存空间。如果需要缓存新的数据，它不会再去分配新的内存空间。而是现有的内存空间里做清理动作，腾出空间来给新的数据用。
（3）当Target Server Memory因为系统内存压力而变小时，它可能会小于Total Server Memory。那么SQL Server会清理缓存，释放内存，从而降低内存使用量即Total Server Memory。
（2）当Total Server Memory等于Target Server Memory时，SQL Server知道自己已经用足了能够给与的内存空间。如果需要缓存新的数据，它不会再去分配新的内存空间。而是现有的内存空间里做清理动作，腾出空间来给新的数据用。
（3）当Target Server Memory因为系统内存压力而变小时，它可能会小于Total Server Memory。那么SQL Server会清理缓存，释放内存，从而降低内存使用量即Total Server Memory。
11.1.6.
SQL内存使用量陡然下降现象 472
·
Windows Server 2000和以前，SQL Server 会用到系统只剩下4-10MB, Windows自己不会缺内存，但其它Applications就不幸了。
·
Windows Server 2003+ 使用了QueryMemoryResourceNotification，SQL
Server不再那么霸道。但新的问题出现了：SQL Server的内存使用量在短时间内被压缩了许多。
·
可能的原因 – 都不是SQL Server自己引起的
o
Windows申请了太多的Kernel Mode内存，导致User Mode内存受压缩。
o
有些硬件驱动程序申请了太多的Kernel Mode内存，也占用了太多的物理内存。
o
某些Applications突然成功地申请了大量物理内存，使得Windows上内存紧张。
·
方法
o
赋予SQL
Server启动账号有Lock
page in Memory的权限。但OS有内存压力时，这个设置不起作用，User
Mode总是抢不过Kernel
Mode。
o
Set Max Server Memory使SQL Server不占用太多内存，从而避免Windows感受到内存压力。
o
使用Windows
Server 2008可避免部分问题（新的内存管理机制）
o
升级硬件驱动程序
11.1.7.
案例分析 474
·
A nice one (SQL Server每天凌晨特定时间变慢 。通过对Perfmon有关的计数器分析发现是由于Windows每天的系统维护导致的，尽管Lock page in Memory已开启。但OS有内存压力时，这个设置不起作用。SQL SERVER抢不过WINDOWS。这是Windows Server 2003的一个bug。升级到2008就不存在该问题了。)
· 解决方法：把Windows Server 2003升级到Windows Server 2008就行了。
11.1.8.
如何“合理”配置SQL Server内存 476
·
两条原则：
o
Windows 系统和其它关键应用服务要有足够的内存，不要在运行过程中因为内存不足，而抢SQL SERVER已经申请的内存。
o
在满足第一点的前提下，SQL SERVER使用尽可能多的内存，并保证内存使用数量的稳定性。
·
方法：
o
尽量使用64位的OS和SQL Server，突破2GB的限制。如果一定要用32位，开启AWE，但不要再使用/3GB开关
o
专用服务器,不要将其它服务安装在同一台机器上。
o
设置SQL Server Max
Server Memory （扣除Windows和其它关键服务应用）
o
给SQL Server 启动账号赋予Lock Pages in memory权限。
o
“Set working set size”不要使用。
11.2.
SQL Server内部独特的内存管理模式 477
SQL Server崇尚自我调节，所以内存调节的接口不多：
·
Min Server Memory (sp_configure) ：最终由Windows确定，不保证SQL Server使用最小物理内存数。
·
Max Server Memory (sp_configure) ：数据放在物理内存还是缓冲文件中，由Windows决定。
·
Set Working Set Size (sp_configure) ：不要使用它，因为现在的Windows版本不再理睬这个设置。
·
AWE Enabled（sp_configure）：对32位系统有意义。
·
Lock Pages in memory （企业版会自动开启）：有一定机会确保SQL Server的物理内存数。
11.2.1.
内存使用分类 478
·
按用途分
o
Database Cache：存放数据页的缓冲区。
o
各类Consumer：
ü
Connection:：默认4K
ü
General：包含语句的编译、范式化、每个锁数据结构、事务上下文、表格和索引的元数据等。默认8K。
ü
Query Plan：默认8k，
ü
Optimizer：默认8k，
ü
Utilities：像BCP、Log Manager、Parallel Queries、Backup的特殊操作。默认8k，
o
线程内存：进程内的每个线程分配0.5MB内存。存放线程的数据结构和相关信息。默认512K
o
第三方代码申请的内存（COM,XP， CLR， Linked Server...）
·
按申请方式分类：
o
有些内存申请方式是：预留Reserve一大块内存，然后使用的时候一小块一小块commit。 For Database Cache。
o
而另外一些内存申请直接从地址空间Commit，这种叫Stolen。除Database Cache之外的内存使用基本上都是直接Commit。这也是正常的内存使用，不是内存泄露。AWE 只对Database Cache有用、对Stolen无效。
·
按申请大小分类：
o
数据页面都是存在8K buffer pool 中
o
Multi-page allocation (以前叫 MemToLeave) for 大于8K的内存申请。这种内存的使用基本上不是SQL Server控本身控制，而是由加载在SQL Server的DLL控制（如第三方代码申请的内存），线程（512K）和一些特别长的语句编译、query plan等也使用Multi-page allocation。在64位上，SQL Server不再控制Multi-page 这段空间的大小，所以MemToLeave现在退出了舞台。
o
大页面内存（large
page） - Trace flag 834 causes SQL Server to use Windows
large-page allocations for the memory that is allocated for the buffer pool.
It improves performance by increasing
the efficiency of the translation look-aside buffer (TLB) in the CPU。
ü
The service account has the authority
Lock Pages in Memory
ü
Need to turn on TF 834。
======================延伸阅读on Large Page====================
Source
1 on Large Pages - http://www.SQLdbadiaries.com/2011/03/04/configuring-SQL-server-to-use-Windows-large-page-allocations/
Page allocations
The Virtual Address Space is
made up of a number of pages. These pages are of two sizes. Large and Small. The Small pages are 4 KB in size (8 KB on IA64
systems) and the Large pages are 2 MB in size for x64 systems
(16 MB for IA64 systems). The details regarding Virtual Addresses is maintained
in a structure called Page Tables. Hence
each Virtual Address has a corresponding Page Table Entry in the Page Table. The hardware translates these entries into a format readable
by it. Whenever an address translation occurs, the entries in the Page
tables has to be
searched. In order to speed up the lookup process,
CPU maintains a cache called Translation Look-Aside Buffer (TLB). TLB
works similar to the procedure cache, such that an entry once translated need
not be translated again.
With this information it will be easy to understand how
Large and Small page allocations make a difference to
the performance. When a Virtual Address is making use of Small pages, the number of
entries in the Page Table increases, which in turn increases the
number of entries in the TLB. For example for few Virtual Addresses to be
cached, using Small pages it would require more entries in TLB.
More entries means whenever a new translation request is received,
more number of cached entries in the TLB needs to be
recycled. Hence allocating Virtual
Addresses by means of Large
pages has a definite
performance benefit.
Page allocations and SQL
Server
Like I mentioned earlier using Trace Flag 834 would force SQL
Server to make use of Large Pages for Buffer Pool. This trace flag is
only applicable to 64-bit of SQL Server and this also requires Lock
Pages in Memory privilege
for the SQL Server service account.
When SQL Server is making use of Large Page allocations, entries similar to the
one below are logged in the SQL Server Error Log when the service starts.
Large Page Extensions enabled.Large Page
Granularity: 2097152Large Page Allocated:
32MBUsing large pages for
buffer pool.10208 MB of large
page memory allocated.
If the SQL Server service account does not have the Lock
Pages in Memory privilege, this
error message is logged.
Cannot use Large Page Extensions: lock
memory privilege was not granted.
This article very nicely explains
the Large Page allocations and
also the fact that SQL Server Enterprise Edition (64-bit) will make use of Large
Page allocations
without the Trace Flag 834.
Source 2 on Trace Flag 834: http://SQLdbadiaries.blob.core.Windows.net/SQLdbadiaries/wp-content/uploads/2011/03/920093
Trace flag 834: Use Microsoft Windows large-page allocations
for the buffer pool
Trace flag 834 causes SQL Server to use Microsoft
Windows large-page allocations for the memory that is allocated for the buffer
pool. The page size varies depending on the hardware platform, but the page
size may be from 2 MB to 16 MB. Large pages are allocated at startup and are
kept throughout the lifetime of the process. Trace flag 834 improves
performance by increasing the efficiency of the translation look-aside buffer
(TLB) in the CPU.
Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.
Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server.
Note if you are using the Column Store Index feature of SQL Server 2012, we do not recommend turning on trace flag 834.
Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.
Trace flag 834 may prevent the server from starting if memory is fragmented and if large pages cannot be allocated. Therefore, trace flag 834 is best suited for servers that are dedicated to SQL Server.
Note if you are using the Column Store Index feature of SQL Server 2012, we do not recommend turning on trace flag 834.
Also note the single and multi-page allocators
are replaced with any size page allocator in SQL Server 2012. See below:
======================
End of 延伸阅读on Large Page====================
11.2.2.
32位下各部分内存的分布和大小限制 482
·
Case 1 – 32-bit的ＯＳ和SQL Server
o
4GB – 2GB (for kernel) =
2GB (for SQL Server)
o
MemToLeave or Multi-page上限是384MB (default 256 +256 thread*0.5=384)。 MemToLeave是给大于8K的内存申请准备的。SQL Server把它们集中到一个区。在３２位SQL SERVER刚启动的时候，就会把这一块虚拟地址预留出来（包含worker threads所需），所以叫MemToLeave。
o
余下的给Buffer Pool, 上限是1664MB(the max for buffer pool including stolen memory)
·
Case 2 – 32-bit with AWE
enabled(SQL Server可以用大于2Ｇ的内存，但只有Database Cache能用，其它类型的内存还是一样受限制):
o
MemToLeave的上限还是384MB
o
buffer pool 中的stolen memory 部分是1664MB,
o
The database cache part
is extended by AWE now
o
换句话说，在32位下，MemToLeave和stolen memory 加起来不会超过2G。
·
Case 3 – 32-bit SQL
Server on a 64-bit Machine:
o
384MB for MemToLeave
o A
max of 3.6GB for buffer pool (X64位系统 32位SQL：因为X64支持WOW-Windows
on Windows，由于64位核心态已经不占用32位应用程序的进程的虚拟地址空间，所以WOW的32位应用程序进程用户态可以到4GB。Memtoleave-MULTI-PAGE算法不变，还是默认的384MB。Buffer pool可以达到3.6GB。如果开启了AWE，这3.6GB就可以基本都给buffer pool里的stolen memory使用了）
·
Case 4 – 32-bit SQL
Server on a 64-bit Machine with AWE enabled:
o
384MB for MemToLeave and
a max of 3.6GB for stolen memory
o
Database cache is
extended by AWE
·
Case 5 – 64-bit OS and SQL
Server
o
No limit on multi-page
(MemToLeave is not applicable anymore).
o
They can use the
available space in the VAS freely. Sometimes, multi-page and stolen memory can
even steal the space in the data cache in the buffer pool.
11.2.3.
SQL Server在不同服务器配置下各部分内存的最高使用上限 484
·
VAS = MemToLeave +
Buffer Pool(Database Page + Stolen)
·
32-bit
o
MemToLeave=384MB
o
Buffer Pool = 2GB –
384MB = 1664MB
·
32-bit with AWE
o
MemToLeave=384MB
o
Stolen = 2GB – 384MB =
1664MB
o
Database Page = Max Server Memory –
2GB
·
64-bit
o
Multi-page – no limit anymore
o
Stolen – no limit
o
Buffer pool = Max Server Memory – Multi-page
11.2.4.
一些内存使用错误理解 485
·
Windows上还有很多物理内存没有被使用，就意味着SQL Server不缺内存。- 非也，只能说明Windows不缺内存。
·
SQL Server进程的Private Bytes或Working Set在不停地增长，说明SQL Server有内存泄漏的问题。- 非也，正常，应该如此。
·
Max Server Memory的值，就是SQL Server内存使用量的最大值，超过这个值就不正常。
- Max Server Memory的值其实是Buffer
Pool的上限，
SQL Server内存使用 = buffer pool + MemToLeave。
·
SQL Server的内存使用总量，就是性能监视器里面的SQL Server：Memory
Manager – Total Server Memory的值。
- 非也。
o
SQL Server：Memory Manager – Total Server Memory的值是SQL Server自己的代码申请的内存空间大小。除此之外，SQL
Server的内存使用总量还包含了MemToLeave那部分。
o
如AWE未开启，SQL Server进程申请的逻辑内存数可以用Process：Private
Bytes看到。这个值包含了SQL
Server自己和第三方的代码。SQL
Server进程申请的物理内存数可以用Process：Working Set看到。这个值也包含了SQL
Server自己和第三方的代码。
o
如AWE已开启，SQL Server的Buffer Pool使用量用以SQL Server：Memory
Manager – Total Server Memory来判断。至于SQL
Server自己申请的内存总数（BufferPool
+ MemToLeave），可以通过有关的DMV计算出来，但第三方的代码申请的内存就不能做精确计算了。
·
当系统有内存压力时，SQL
Server总是自动释放内存。-
有例外，启动账号有Lock
Page In Memory权限。
·
SQL Server有办法将自己的内存绑定在物理内存里 –
非也，Kernel申请优先。
·
增加MemToLeave的大小可以提高SQL Server的性能
– 未必，只有在MemToLeave不够用时才会有效。
o
在32位的SQL Server上，默认MemToLeave是256MB+0.5MB*（Max Thread数目）。如果用完了，SQL
Server的一些重要功能就不能进行，甚至新的连接都建立不起来。在这种情况下，可以通过g参数增加MemToLeave。如想把MemToLeave设置成512MB+0.5MB*（Max Thread数目），可以启动-g512。
o
但SQL
Server的VAS只有2GB，给MemToLeave越多，Buffer Pool就越小。Data Cache那部分还可以通过ＡＷＥ扩展，但Stolen
Memory部分就没办法了。
·
增加物理内存一定会提高SQL
Server的性能
– 未必，关键是看能不能用得上。
o
那部分内存有瓶颈？是Database
Cache，Stolen，还是MemToLeave?
o
增加的内存能不能被所缺的内存用到？如32位+AWE，Stolen
Memory的不会超过1.6G，如果这部分内存不够，增加内存也不够，除非将系统升级到64位。
o
增加的内存一般都是被Buffer
Pool使用。但如果Database
Page没有压力，SQL
Server可能会缓存太多的动态T-SQL执行计划，对性能没什么好处，
还要增加维护成本。
·
Stolen真的是偷来的吗？- 非也，其实，SQL Server内存管理机制对这块内存已经预先Reserve好了，所以，需要用到的时候，直接Commit就行了，不像Data Page，要一小块一小块地先Reserve，再Commit。
11.3.
SQL Server内存使用状况分析方法 487
11.3.1.
SQL性能计数器 488
·
Memory Manager：监视服务器内存总体使用情况
o
Total Server
Memory（KB）- Buffer Pool的大小
o
Target
Server Memory（KB）
o
Optimizer Memory（KB）：服务器正在用于查询优化的动态内存总数
o
SQL Cache Memory（KB）：服务器正在用于动态SQL Server高速缓存的动态内存总数
o
Lock Memory（KB）：服务器用于锁的动态内存总量
o
Connection Memory（KB）：服务器正在用来维护连接的动态内存总量
o
Granted Workspace Memory(KB)：当前给予执行哈希、排序、大容量复制和索引创建等操作进程的内存总量
o
Memory
Grants Pending：等待工作空间内授权的进程总数。如果该值不等于0，就说明当前有一个用户的内存申请由于内存压力而被延迟。一般来讲，这就意味着有比较严重的内存瓶颈
·
Buffer Manager：用于监视内存如何使用
o
Buffer
Cache Hit Ratio：在缓冲区高速缓存中找到而不需要从磁盘中读取的页的百分比。经过很长时间后，该比率的变化应该很小，基本应该在99%以上。如果小于95%，通常就有了内存不足的问题。可以通过增加SQL Server的可用内存来提高
o
Database Pages: 缓冲池中有数据库内容的页数。也就是所谓的Database
Cache的大小
o
Free pages：所有空闲可用的总页数。当这个值降低是就说明SQL Server正在分配内存给一些用户。当这个值下降到比较低的值时（例如只剩几百个page了），SQL Server就会开始做Lazy Write，把一些内存让出来，所以该值一般不会为0.但如果该值反复降低，就说明内存存在瓶颈。一个没有内存瓶颈的SQL Server的Free Pages会维持在一个稳定的值。
o
Lazy
writes/sec：每秒被缓冲区管理器的Lazy writer写入的缓冲区数。Lazy writer是一个系统进程，用于呈批刷新脏的老化的缓冲区（包括更改的缓冲区，必须将这些更改写回磁盘，才能将缓冲区重用于其它页），并使它们可用于用户进程。当SQL
Server感觉到内存压力时，就会将最久没有被重用到的数据页和执行计划清理出内存。这些数据页和执行计划，就被称为“老化的缓冲区”，这个清理动作就是由Lazy
writer完成的。所以如果SQL Server内存压力不大，Lazy writer就不会被经常触发。如果被经常触发，就应该是有内存瓶颈
o
Page life
expectancy：页若不被引用，将在缓冲池中停留的秒数。如果SQL
Server没有新的内存需求，或者有空余的空间来完成新的内存需求，那么Lazy writer就不会被触发，页面会一直放在缓冲池中，Page life
expectancy就会维持在一个较高的值。如果SQL Server出现了内存压力，Lazy writer就会被触发，Page life expectancy也会突然下降。所以如果Page life
expectancy总是高高低低，SQL Server应该就出现了内存瓶颈
o
Page
reads/sec：每秒发出的物理数据库页读取数。此统计信息显示的是所有数据库间的物理页读取总数。如果用户访问的数据都缓存在了内存里，那么SQL
Server就不需要从磁盘读取页面，不许需要做任何的Page
reads。当SQL Server需要读取这些页面时，必须要为它们腾出内存空间。所以当Page
reads/sec高时，一般Page life
expectancy会下降，Lazy
writes/sec会上升
由于物理I/O开销大，Page Reads动作一定会影响SQL Server性能，可以通过使用更大的数据缓存、智能索引、更有效的查询或更改数据库设计等方法降低Page Reads
由于物理I/O开销大，Page Reads动作一定会影响SQL Server性能，可以通过使用更大的数据缓存、智能索引、更有效的查询或更改数据库设计等方法降低Page Reads
o
Page
writes/sec：每秒执行的物理数据库页写入数。该值和内存使用没有什么关系，和Checkpoint
pages/sec一样，更用户的修改量有关
o
Checkpoint
pages/sec：由要求刷新所有脏页的检查点或其它操作每秒刷新到磁盘的页数。该值和内存压力没有直接关系，和用户行为有关。如果用户操作主要是读，Checkpoint值就比较小。如果很多操作都是Insert/Update/Delete，name内存中修改过的数据脏页就会比较多，每次Checkpoint的量也会较大。主要用来分析磁盘I/O
o
Stolen Pages：用于非Database
Pages（包括执行计划缓存）的页数。这里就是Stolen Memory在Buffer Pool里的大小
o
Target Pages：缓冲池中理想的页数，乘以8KB，就应该是Target Server Memory的值
o
Total Pages：缓冲池中的页数（包括数据库页、可用页和Stolen页）乘以8KB，就应该还是Total Server Memory的值
·
SQL性能分析步骤：
o
当内存出现瓶颈时，会出现大量的paging动作，磁盘也就会很繁忙。所以要先解决内存瓶颈，才能降低I/O
o
分析磁盘性能是否正常，读/写是否已达到预期值，是读还是写繁忙
o
查看引起高I/O的操作类型（Page Reads、Page Writes、Lazy Writes、Checkpoints、Log Writes等）
·
监视计数器（2分钟）建议?：
Processor\% Privileged
Time
Processor\% Processor
Time
Memory\Available Bytes
PhysicalDisk\Avg. Disk
sec/Read
PhysicalDisk\ Avg. Disk
sec/Write
SQLServer:SQL
Statistics\Batch Requests/sec
SQLServer:Memory
Manager\Target Server Memory (KB)
SQLServer:Memory
Manager\Total Server Memory (KB)
Process (w3wp)\Working
Set
Process (w3wp)\Private
Bytes
11.3.2.
内存动态管理视图（dmv） 490
·
sys.dm_os_memory_clerk –
shows how memory is currently allocated and used. SQL Server 2005+ uses memory
clerk to manage SQL Server memory allocation and collection. 从这个DMV中，可以算出不同内存的大小，从而知道每一类内存是否到了极限：Reserved/Commit，Stolen， Buffer Pool（Single Page） ，and MemToLeave（Multi-page）。但要注意，这里显示的MemToLeave是指被SQL Server代码使用掉的那部分。MemToLeave里另一部分即被第三方代码所使用掉的内存将不包含在内。
/*The Script below is for SQL Server 2005 – 2008R2. In SQL Server
2012, the following columns have been renamed: single_pages_kb==>pages_kb,
the column multi_pages_kb has been removed.
The new pages_kb includes both the previous
single_pages and Multi_pages.
For other columns/counters changes related to the new memory
manager in SQL Server 2012, see http://blogs.msdn.com/b/SQLosteam/archive/2012/07/11/memory-manager-surface-area-changes-in-SQL-server-2012.aspx
*/
SELECT
a.type,
SUM(a.virtual_memory_reserved_kb) AS vm_reserved,
SUM(a.virtual_memory_committed_kb) AS vm_committed,
SUM(a.awe_allocated_kb) AS awe_allcoated,
SUM(a.shared_memory_reserved_kb) AS sm_reserved,
SUM(a.shared_memory_committed_kb) AS sm_commited,
SUM(multi_page_kb) AS multipage_allocatoer,
SUM(single_pages_kb) AS sinlgepage_allocator
FROM sys.dm_os_memory_clerks a
GROUP BY a.type
ORDER BY a.type
·
sys.dm_os_buffer_descriptors
- Returns information about
all the data pages that are currently in the SQL Server buffer pool.
--Returning cached page count for each database
SELECT COUNT(*)AS cached_pages_count
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,database_id
ORDER BY cached_pages_count DESC;
--Returning cached page count for each object in the current
database
SELECT COUNT(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;
·
sys.dm_exec_cached_plans
- Returns a row for each
query plan that is cached by SQL Server for faster query execution. You can use
this dynamic management view to find cached query plans, cached query text, the
amount of memory taken by cached plans, and the reuse count of the cached
plans.
SELECT usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_SQL_text(plan_handle)
WHERE usecounts > 1
ORDER BY usecounts DESC;
GO
11.4.
数据页缓冲区压力分析 497
11.4.1.
表现特征 497
·
Many lazy writes
frequently: SQL Server: Buffer Manager – Lazy writes/sec
·
Many page reads - SQL
Server: Buffer Manager – Page reads/sec. occasionally high is fine, but
frequent high indicates problems.
·
Stolen in the buffer
pool is low as the execution plan is often cleared. Check SQL Server: Buffer
Manager –Stolen pages. If there is a memory pressure on buffer pool, SQL
Server clears the execution plans in the buffer pool first, making the stolen
pages sharply decreases.
·
Page life expectancy is
often not very high and often decreasing as the cached pages are often cleared.
Check SQL Server: Buffer Manager – Page Life Expectancy on performance monitor
·
When you run
sys.sysprocessed, you will see some sessions are suspended, waiting for
ASYNC_IO_COMPLETION
11.4.2.
确定压力来源和解决办法 500
·
Caused by Windows OS and
other applications
o
Symptoms
ü
看看 SQL Server: Memory Manager – Total Server Memory有没有被压缩
ü
Check if the
Memory:Available Mbytes has decreased to a low value
ü
If AWE or Lock page in
memory is not used, then check if Process: Private Bytes – SQLservr and
Process: working set – SQLservr has sharply decreased
o
Solution
ü
Give SQL Server more
memory: Set Max server memory
ü
Install SQL Server on
dedicated server
·
Caused by usage of
database page – the total server memory for SQL Server has reached Max Server
Memory or SQL Server cannot get more memory from the OS, and the data pages are
much larger than buffer pool.
o
Symptoms
ü
SQL Server: Memory
Manager – Total Server Memory 接近SQL Server: Memory Manager – Target Server Memory， 但不会大于它，这点区别于外部压力。
ü
The other symptoms are
similar to those in case 1
o
Solutions
ü
Scale up – enable AWE or
adding more memory
ü
Scale out – migrate some
databases to other servers
ü
Re-design the tables
(partition the tables), indexes, calling procedures to reduce the use of buffer
pool.
ü
Find the queries using
most of the memory
·
Caused by stolen memory
o
Symptoms
ü
Rarely happening as if
there is a pressure on buffer pool, stolen should clear the execution plans.
However, some users may forget to close the objects such as cursors, or
un-prepare the execution plans. Those cannot be automatically cleared.
ü
Use
sys.dm_os_memory_clerks to find them(single_pages_kb)
o
Solution – find out who
uses so high stolen memory and why
·
Caused by multi-page or
MemToLeave
o
Symptom
ü
In 32-bit case, VAS is
only 2GB, MemToLeave is defaulted to 384MB as a max. But if you use the –g
parameter to increase the max for MemToLeave，the
data cache space 就变小了。
ü
In 64-bit case, no limit
for multi pages anymore, but if memory leak happens when using 3rd party
codes.
o
Solution
ü
Use
sys.dm_os_memory_clerks to find out who
has used so much MemToLeave memories (Multi_pages_kb)
11.4.3.
如何发现内存使用比较多的语句
·
使用DMV分析SQL Server启动以来做Read最多的语句。
o
分析方法
ü
使用DMV提取历史信息
Ø
sys.dm_exec_query_stats, 返回缓存查询计划的性能统计信息SQL会统计从上次SQL启动以来，一共做了多少次logical读写，多少次physical读，还记录执行所用的CPU时间总量。每个查询对应一行。
--find the top 50 physical
read。
SELECT TOP 50
qs.total_physical_reads,
qs.execution_count,
qs.total_physical_reads / qs.execution_count AS [Avg Physical Read IO],
qs.SQL_handle,
qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset,
qt.dbid,
dbname = DB_NAME(qt.dbid),
qt.objectid, --only
procedure,trigger,view,or function have an id
SUBSTRING(qt.text,qs.statement_start_offset / 2,
(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1
) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_SQL_text(qs.SQL_handle) AS qt
ORDER BY qs.total_physical_reads DESC
--find the top 50 logical
reads。
SELECT TOP 50
qs.total_logical_reads,
qs.execution_count, qs.total_logical_reads / qs.execution_count AS [Avg Logical Read IO],
qs.SQL_handle,
qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset,
qt.dbid,
dbname = DB_NAME(qt.dbid),
qt.objectid,
--这里必须要加1,因为有时statement_start_offset为0,那么需要取的长度要加上1,否则取出来的就会少一个字符. 如: select
substring('abc',0,3) 会输出'ab',所以要写成：select
substring('abc',0,3 + 1)
SUBSTRING(qt.text,qs.statement_start_offset / 2,
CASE
WHEN qs.statement_end_offset = -1
THEN (DATALENGTH(CAST(qt.text AS nvarchar(MAX))) - qs.statement_start_offset) / 2 + 1
ELSE (qs.statement_end_offset - qs.statement_start_offset) / 2 + 1
END
) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_SQL_text(qs.SQL_handle) AS qt
ORDER BY qs.total_logical_reads DESC
Ø
缺点有二：（1）记录的生存期与执行计划本身相关联（即当SQL Server有内存压力时，可能把有些执行计划删除掉了），（2）缺乏时效性（因为是累计的历史信息，无法知晓语句是在何时用得比较频繁）。
ü
使用SQL
Trace - SQL
Trace里面有一个reads字段，记录了某条语句完成过程中一共做了多少次读的动作，通过它可以找到read最多的语句。save the output to a
trace file, use fn_trace_gettable to input it into a SQL Server table, and
query the table for analysis.
SELECT * INTO #SAMPLE
FROM sys.fn_trace_gettable ('C:\Users\Administrator\Desktop\1.trc',DEFAULT)
WHERE EventClass IN(10,12) -- 10=RPC:Completed,
often for usp, 12=SQL BatchCompleted
SELECT TOP 1000 TextData,DatabaseID,HostName,ApplicationName,LoginName,SPID,
StartTime,EndTime,Duration,reads,writes,CPU
FROM #SAMPLE
--
find which client application request the large reads
SELECT DatabaseID,HostName,ApplicationName,SUM(reads) AS reads
FROM #SAMPLE
GROUP BY DatabaseID,HostName,ApplicationName
ORDER BY SUM(reads) DESC
/*find the top 1000 queries based on the number of reads descending order. 用这个方法可以找出最昂贵的单笔语句。Note, all reads in SQL trace is logical read, not physical reads. 只有语句访问的数据不在内存里时，才有物理读。*/
SELECT TOP 1000 TextData,DatabaseID,HostName,ApplicationName,LoginName,
SPID,StartTime,EndTime,Duration,reads,writes,CPU
FROM #SAMPLE
ORDER BY Reads DESC
ü
利用readtrace这个工具自动分析trace文件，找出使用大量系统资源的语句。
如果只是要找到一段时间内系统资源使用比较多的语句，不用做详细分析，readtrace这个工具能够自动完成。
o
解决方法
ü
增加物理内存
ü
归档历史数据，把表里的数据降下来
ü
调整应用程序设计，避免不必要的大数据量查询
11.5. Stolen memory缓存压力分析 508
·
除了database pages，其它内存分配基本都是直接从地址空间申请，不是先reserve,后commit的。stolen内存主要以8KB为单位分配，分布在buffer pool里。
·
stolen内存不缓存数据页面，但任何一条语句的执行都需要stolen内存来做语句分析，优化，执行计划的缓存可能也需要内存来做排序，计算。任何一个连接的建立，需要分配stolen内存给它建立数据结构和输入输出缓存区。如果stolen内存申请不到，SQL任何操作都会遇到问题。
· 还有一些其它的stolen内存也没有缓存，使用完毕立刻释放，供其它用户使用，例如：语义分析，优化，做排序，做Hash等。所以在32位SQL，虽然stolen内存只有不到2GB，但是很少有stolen内存不够用的情况。
· 但stolen内存会缓存执行计划和用户安全上下文，这些缓存越多越好。
· 但stolen内存会缓存执行计划和用户安全上下文，这些缓存越多越好。
·
在SQL2005 SP2以后,SQL产品组调小了执行计划的最高上限。这是因为如果一个SQL能够缓存这么多不同的执行计划， 说明它内部运行的大多数都是动态TSQL，很少能够重用。如果经常有执行计划重用的现象，SQL也就不需要每次都生成新的执行计划，从而缓存这麽多份了。在这样的SQL里，就算缓存再多的执行计划，重用的机会都很小。所以这么多的缓存对SQL的性能帮助不是很大，反而增加SQL的维护成本。从经验上看，SQL缓存1GB~2GB的执行计划基本足够了，更多的缓存基本上对性能帮助不大。在有些情况下，定期清空执行计划缓存，反而对SQL的健康起到帮助作用.
DBCC
DROPCLEANBUFFERS
11.5.1.
stolen缓存区与数据页缓存区的相互关系 509
·
Stolen内存一般不会挤压database pages内存 – 因为stolen内存很多是用完就释放的，不会累积下来。对于有缓存机制的stolen内存，主要就是执行计划，也有清理机制，当buffer pool有内存压力的时候，SQL会同时清除执行计划和database pages ,SQL都会把最久没有使用的对象清除。就算一个查询一次访问上百MB的数据，但是它的执行计划却是非常小的。
·
但是当lazy writer没有能清除正在被使用的stolen内存的时候，会发生stolen内存越积越多，最后侵占database pages空间的现象
11.5.2.
内部压力与外部压力
510
·
外部压力：Windows通知SQL要压缩内存的时候，整个buffer
pool里的所有内存包括stolen内存都面临着清理。
·
内部压力有二：（1）受database
pages挤压，当一个查询需要大量的data page的时候， （2）stolen内存内部一些始终未清理的对象，例如，打开了游标不关，或者prepare了一些语句没有unprepare,
那么只要这个连接不logout， SQL就无法清理和释放这些对象。
·
可以通过sys.dm_os_memory_clerk各类内存使用的的数量及各个Clerk的大小来判断出Stolen内存压力的来源。
11.5.3.
表现特征与解决办法
510
·
表现特征有2大类
o
错误- Requests cannot be completed, return error 701 etc.
Check the errorlog.
o
瓶颈 - Request will be done but slow。
·
解决办法 - 对于瓶颈问题，可以通过Check the waitingtype in
sys.sysporcesses进一步了解是否有瓶颈。如果waitingtype不为0x0000, 就有瓶颈。与Stolen内存有关的主要瓶颈及其相应的解决办法有:
ü
0x00B9 – 很少发生。主要发生在多用户同时往同一块缓存区里申请或释放内存，而在一个时间点，只有一个连接可以成功，其它的必须等。这常常发生在多个并发连接在大量地使用（需要每次都做编译的）动态T-SQL语句。
ü
0x007B – the queries contain many parameters or a long IN
subquery, the execution plan is too large to be in a single page, and need to
be in the multi-page or MemToLeave. 当MemToLeave有压力时（即用户申请这块内存但暂时得不到满足，就触发这个等待状态。
Ø
Do not use queries with many parameters or a long “IN” subquery.
Put the parameters into temp table and join these temp tables
Ø
Increase MemToLeave or use 64-bit SQL Server
Ø
Run DBCC FreeProcCache routinely or occasionally
ü
0x011A - Happens when a batch or usp is extremely complex, the
subsequent queries must wait until the complex done is done.
Ø
Change client query behaviors
Ø
Make the query less complex
Ø
Run DBCC FREEPROCCAHCE routinely
11.6.
multi-page缓存区压力分析 512
·
It is called MemToLeave in SQL Server 2000, from
2005, it is called multi-page allocation.
·
On a 32-bit machine, the default max is 256MB + 0.5MB* (Max
threads number in sp_configue) often 256. If you need to increase the limit,
use –g parameter, such as “-g512”
·
On a 64-bit machine, no limit, also note the max server memory is
only for buffer pool, not for multi-page memory.
·
Three purposes of multi-page memory：
o
For threads, each thread needs 512KB or 0.5MB
o
The Stolen memory over 8K from SQL Server（如Proc Cache Page）
o
3rd party
codes (heaps, COM, OLE-DB, xproc etc.)
·
If the limit of the multi-page memory is reached, new login cannot
connect SQL Server anymore.
·
Where does the pressure come from?
o
Outside SQL Server: SQL CLR, Linked Server, COM, xsp
o
From SQL Server – when the 8K page cannot handle
ü
Queries with many parameters, long “IN” subquery
ü
Network packet size – default to 4KB. 如果有些应用从提高网络交互效率的角度而把它改变为 8KB或更高, SQL Server will use multi-page. 对SQL Server而言，会negatively 影响内存分配。如果这样改变只能提高你的应用性能，那你就要, test, test, and test.
ü
客户端调用了Complex and heavy use of XML
functions.
11.7.
常见内存错误与解决办法 515
11.7.1.
错误701 – 申请Stolen Memory时失败516
·
Out of Memory – Error 701 in SQL Server 2005+ and 17803 in SQL
2000
·
Rarely happened in 64-bit environment
·
2种情况
o
memtoleave地址段没有连续空间可供使用。又分2种情况：
ü
一种可能性是SQL Server自己申请了太多的Multi-pages。通过Memory Clerk应该能够找到某个Clerk申请了很多的Multi-pages。很可能是某个超级复杂的批语句在编译的时候申请了太多的MemToLeave。
ü
另一种情况是第三方代码申请太多内存没有释放掉。如果在errorLog中没看到任何一个Clerk使用太多的MemToLeave。就要检查是否有以下的代码在SQL Server里活跃地在运行：SQL CLR， LINKED SERVER，EXEC sys.sp_OACreate 调用COM对象，sys.xp_cmdshell等等。
o
Buffer Pool里的Stolen申请不到。可以从sys.dm_os_memory_clerk或errorlog中可以找到使用量最大的那个Clerk.
11.7.2.
错误8645
– 语句运行时没能及时申请到内存
520
·
这个错误通常发生在一个需要申请内存做排序或者hash等操作的查询里，在规定时间里没有能得到足够内存。这里申请的内存基本都是Buffer Pool里的内存。
·
何时会发生？
o
SQL内存本身有压力，现在再发过来哪怕很小的内存申请数也不能满足
o
用户突然发来一个或几个需要大量内存非常复杂的语句，一下子把SQL内存资源搞得非常紧张
·
症状：
o
login
failed
o
已经连上的会异常中断，错误信息不固定。
o
运行select * from sys.sysprocesses ，waittype字段会显示0x40或0x0040(resource_semaphore)
等待资源
o
性能计数器:SQL mamager:memory grants pending对象值不为0
·
解决办法:
o
避免其它程序把SQL内存侵占
o
收集buffer manager 和memory
manager性能计数器
o
检查以下SQL内存参数是否设置合理：
ü
max
server memory
ü
min
server memory
ü
awe
enabled（启动lock
page in memory否?）
ü
min
memory per query
o
检查各个memory clerk的内存申请
o
检查工作负荷，例如：并发会话数，当前执行的查询，有可能的话开启SQL Trace
o
为SQL提供更多内存
o
移开占用资源的应用程序到别的服务器
o
增加max server memory
o
运行下面DBCC命令强行释放SQL内存缓存
ü
DBCC freesystemcache
ü
DBCC freesessioncache
ü
DBCC freeproccache
o
找出使用内存比较多的语句，简化它们，调整应用程序行为，减少工作负荷
o
检查动态管理视图sys.dm_exec_query_resource_semaphores,了解每个查询资源信号量的状态信息。（SQL里默认有两个查询资源信号量，分别处理复杂度不一样的查询，这样的设计有助于防止几个超大的查询把整个SQL资源用尽，连一些很简单的查询都不能响应的现象发生）
SELECT CONVERT(VARCHAR(30), GETDATE(), 121) AS runtime ,
resource_semaphore_id ,
target_memory_kb ,
total_memory_kb ,
available_memory_kb ,
granted_memory_kb ,
used_memory_kb ,
grantee_count ,
waiter_count ,
timeout_error_count
FROM sys.dm_exec_query_resource_semaphores
o
检查sys.dm_exec_query_memory_grants,返回已经获得内存授予的查询的有关信息，或依然在等待内存授予的查询的有关信息。无须等待就获得内存授予的查询将不会出现在此视图中。所以对一个没有内存压力的SQL，这个视图应该是空的。从这个查询，可以知道当时SQL
Server正在等待内存申请的查询，它们申请的数量和实际拿到的数量。这样有机会抓住造成SQL
Server内存压力的语句。
SELECT GETDATE() AS runtime ,
session_id ,
scheduler_id ,
dop ,
request_time ,
grant_time ,
requested_memory_kb ,
granted_memory_kb ,
used_memory_kb ,
timeout_sec ,
query_cost ,
resource_semaphore_id ,
wait_order ,
is_next_candidate ,
wait_time_ms ,
REPLACE(REPLACE(CAST(s2.text AS VARCHAR(0)), CHAR(10), ''), CHAR(13),
'') AS SQL_statement
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_SQL_text(SQL_handle) AS s2
11.7.3.
17189错误 - SQL Server无法创建新的线程 524
·
如果连接创建不出来，会出现SQL断断续续地连接不上的现象，已经在SQL里的连接还可能继续工作。只要有线程创建不出来的问题发生，SQL errorlog里就会有记录。
·
解决办法：
o
检查SQL使用了多少线程，是不是的确到了上限。运行下面的查询，检查有多少个KPID<>0的SPID （Kpid" is the kernel-process ID，spid is the Server Process
ID）。当一个SPID的KPID不为0的时候，就说明它正在使用线程运行中。再加上SQL自己运行所需要的线程（一般10到20个），就差不多是SQL使用的线程数目。
SELECT COUNT(*) FROM sys.sysprocesses WHERE kpid<>0
o
如果线程的数目远小于设置的最大数，那就要确认SQL是不是还有足够的memtoleave。
SELECT type
,
SUM(virtual_memory_reserved_kb) AS [vm reserved] ,
SUM(virtual_memory_committed_kb) AS [vm commited] ,
SUM(awe_allocated_kb) AS [awe allocated] ,
SUM(shared_memory_reserved_kb) AS [sm reserved] ,
SUM(shared_memory_committed_kb) AS [sm committed] ,
SUM(single_pages_kb) AS [singlepage
allocator] ,
SUM(multi_pages_kb) AS [multi page
allocated]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY type
11.8.
SQL Server 2012 内存管理的新变化 525
·
改善主要集中在multi-page allocator的缺陷，因为它不再适应现在的高端服务器、CPU和内存数目众多。
o
Multi-page allocator的可扩展性和性能不能适应CPU的增多。
o
Multi-page allocator是依赖操作系统来决定如何使用NUMA的。
o
Multi-page allocator不支持锁定内存页（locked Page）和Large Page。
=====================延伸阅读on NUMA====================
·
SQL Server与NUMA
o
先看看什么是UMA
ü
Uniform memory
access (UMA)
is a shared
memory architecture
used in parallel
computers. All the processors in the UMA model share the
physical memory uniformly. In a UMA architecture, access time to a memory
location is independent of which processor makes the request or which memory
chip contains the transferred data.
ü Types of UMA architectures
Diagram of a symmetric multiprocessing system
Ø
UMA using crossbar switches
Diagram of a typical SMP system. Three
processors are connected to the same memory module through a system
bus or crossbar
switch
o
可见，UMA与SMP紧密相连，那什么是SMP?
ü
Symmetric multiprocessing (SMP) involves
a symmetric multiprocessor system hardware and software architecture where two or more
identical processors connect to a single, shared main memory, have full access
to all I/O devices, and are controlled by a single operating system instance
that treats all processors equally, reserving none for special purposes. Most
multiprocessor systems today use an SMP architecture. In the case of multi-core processors, the SMP architecture applies to the
cores, treating them as separate processors.
ü
SMP systems are tightly coupled multiprocessor systems with a pool of
homogeneous processors running independently, each processor executing
different programs and working on different data and with capability of sharing
common resources (memory, I/O device, interrupt system and so on) and connected
using a system bus or a crossbar.
o
NUMA
ü
SMP using a single shared system bus represents one of the earliest styles of multiprocessor
machine architectures, typically used for building smaller computers with up to
8 processors.
ü
Larger computer systems might use newer architectures such as NUMA (Non-Uniform Memory Access), which dedicates different memory
banks to different processors. In a NUMA architecture, processors may access
local memory quickly and remote memory more slowly. This can dramatically
improve memory throughput as long as the data are localized to specific
processes (and thus processors). On the downside, NUMA makes the cost of moving
data from one processor to another, as in workload balancing, more expensive.
The benefits of NUMA are limited to particular workloads, notably on servers where the data are
often associated strongly with certain tasks or users.
ü
NUMA architectures logically follow in scaling from symmetric multiprocessing (SMP) architectures.
(One possible architecture of a NUMA system.
The processors connect to the bus or crossbar by connections of varying thickness/number.
This shows that different CPUs have different access priorities to memory based
on their relative location.)
ü
访问外部内存的开销与访问本地内存的开销比率称为NUMA比率。如果NUMA比率为1，则是ＳＭＰ。所以，NUMA的主要优点是伸缩性或扩展性。
ü
Contrast UMA(SPM) with NUMA
Ø
在ＳＭＰ中，所有的内存访问都传递到相同的共享内存总线。ＣＰＵ间会相互竞争对共享内存总线的访问。
Ø
而ＮＵＭＡ通过限制任何一条内存总线上的ＣＰＵ数量并依靠高速互联来连接各个节点。
====================== End of 延伸阅读on NUMA ====================
11.8.1.
总体变化 527
·
所有的内存请求都将通过新的内存管理器完成。
·
Any-size allocator取代Single-page
allocator和Multi-Page
allocator
·
新的内存管理器完全支持NUMA（以前是靠OS来支持）
·
ＶＡＳ的管理完全是动态的，特别对３２位的实例也是如此
·
所有通过内存管理器分配出去的内存都受“Max
Server Memory”所控制。
·
32位不再支持ＡＷＥ。
·
Buffer Pool现在作为一个纯粹的内存管理器的客服端来使用了。
11.8.2.
AWE和lockedpages的变化 528
·
AWE的变化
o
SQL Server 2000引进了AWE的功能以突破4GB内存的限制。
o
64-位SQL Server中的ＡＷＥ仍然可用，但基本不需要了，只在个别特殊的场景下有用。
o
32-位SQL Server中的ＡＷＥ已不存在，因为从Windows
Server 2008 R2开始，只有64位，SQL Server也大都部署在64位的环境中。
·
Locked Pages的变化
o
在SQL
Server 2005 - 2008R2中，64位的SQL Server使用AWE的API来达到把内存页锁定在内存中的作业，即所谓的锁定内存页（Locked
Pages）。在Enterprise
64-bit中，只要用户有Lock
Pages in Memory的权限就可以。而在Standard
64-bit中，
除了用户有Lock
Pages in Memory的权限外，还需要下载KB970070的补丁包，并使用T845 trace
flag。
o
Starting
with SQL Server 2012, the memory manager simplifies the usage of "locked
pages" across supported editions and processor architectures. In SQL
Server 2012, you can enable "locked pages" by granting the "lock
pages in memory" user right for the startup account for the instance of SQL
Server in Windows. This is applicable for Standard, Business Intelligence, and
Enterprise editions of SQL Server 2012 running on both 32-bit and 64-bit
processor architectures. Note： Even though the "awe enabled" feature
is not available in 32-bit SQL Server 2012, you can still use the "locked
pages" feature by assigning the "lock pages in memory" user
right for the SQL Server startup account。Locked Pages主要用来防止换页带来的性能影响。
o
How to know if Locked Pages is ON?
--Option 1 -
locked_page_allocations_kb should be 0
SELECT * FROM sys.dm_os_memory_nodes
--Option 2: check
the memory manager pane and note the locked ages allocated is 0
DBCC memorystatus
--Option 3: check the error log
o
How to set Locked Pages ON
11.8.3.
内存相关的诊断工具的变化 530
·
SQL Server错误日志的变化(以前可能需要从各个不同的日志来获得这类信息)
o
显示了所有的物理内存: Server Detected xxx
MB of RAM.
o
显示了SQL Server内存管理器使用方式的方式（包含一般的、Locked pages, 或Large Pages）
ü
Server using
conventional memory in the memory manager
ü
Server using locked
pages in the memory manager
ü
Server using large
pages in the memory manager
o
DMV的变化
ü
Single_page和Multi_Page统称为Page_allocation now
ü
KB, MB, 或Page统一为KB.
o
DBCC MemoryStatus的变化
ü
大部分与内存管理器有关的信息从Buffer
Pool移到了Memory
Manager
11.8.4.
SQL Server 2012内存相关配置的变化 533
·
Max Server Memory 在SQL Server 2012中除了限制Buffer Pool外，也限制了Multi Page Allocation和SQL CLR，这两者在以前是不受Max Server Memory限制的。现在不受限制的还有内部线程使用的栈和加载在SQL
Server内部的模块通过VirtualAlloc或者HeapAlloc直接向Windows获取的内存，如xsp, 通过sp_OA创建的OLE Objects,或者Linked Server Provider所分配的内存等。
·
-g 参数启动 –
在32-位的SQL Server 7.0 – SQL Server 2008R2中，所有超过8K的内存申请都需要在MemoryToLeaveg（MTL）中获得内存（包括multi-page， CLR，内部线程使用的栈，和通过VirtualAlloc或者HeapAlloc直接向Windows获取的内存，如xsp, 通过sp_OA创建的OLE Objects,或者Linked Server Provider所分配的内存等）。G参数可也用来调整MemoryToLeave的大小。但自SQL Server 2012来，SQL Server的内存管理器开始管理multi-page allocation和CLR allocation。只有后两者还需要用-g参数来调整。SQL Server 2012中的ＭＴＬ的默认值还是２５６ＭＢ。
11.8.5.
其它改变 535
·
SQL Server 2012之前，ＣＬＲ只有被使用时，才会初始化。从SQL Server 2012开始，只要SQL Server一启动，ＣＬＲ就会被初始化，不管CLR Enabled与否。
11.9.
小结 535
·
从系统的角度分析，首先要确认Windows系统不缺内存资源，也没有内存使用剧烈变化。可以从性能监视器的有关Counters确认Windows的内存使用情况。
·
确认SQL
Server的内存使用量稳定，没有被Windows或其它应用服务侵占。
·
确保SQL
Server的配置能充分使用服务器的内存资源。如对于32位、内存大于4GB的系统，要打开AWE。对于64位的系统，要检查Lock pages in
memory有没有打开。
·
检查Memory
Clerk，
确定每个内存管理组件的内存申请情况，以及Database
Pages, Stolen Memory和Multi-pages的使用情况。
·
检查sysprocesses的waittype字段，以及性能监视器的相关监视器，确定内存压力是在Database
Pages, Stolen Memory，还是Multi-pages。
·
检查errorlog里的相关错误信息，以及打印出来的Memory
clerks的值，确定内存错误发生的位置，是在Database
Pages, Stolen Memory，还是Multi-pages。
·
如果是Database
Pages有压力，要通过DMV的历史信息，或出问题的时间段的SQL
Trace日志，确定压力来自哪个库，哪个应用，哪些语句，可能的话，优化语句或数据库设计。
·
如果是Stolen
Memory有压力，确定当前Stolen
Memory的上限值是多少，压力来自哪个Memory
Clerk,来自哪些请求，连接处于哪种等待状态，为什么Lazy
Writers没有解决问题。
·
如果是Multi-pages有压力，确定当前它的上限值是多少，是SQL
Server还是第三方代码申请了更多的multi-pages。如果是SQL Server，又是哪个Memory Clerk。是否需要-g参数暂时规避问题。
压力来自哪个Memory
Clerk,来自哪些请求，连接处于哪种等待状态，为什么Lazy
Writers没有解决问题。
·
使用SQL
Server 2012时，要注意到Database
Page和multi-page统一由内存管理器管理，DMV也发生了变化。Max Server
Memory设置可以控制绝大部分的正常使用。
