翻译自：https://blogs.msdn.microsoft.com/karthick_pk/2012/06/15/troubleshooting-sql-server-memory/
SQL Server内存故障排除的第一步，是识别是否低内存条件出现在了MemToLeave或BPool或者因为外部内存压力。
注意：如果你不知道什么是BPOOL或者MemToLeave。在排除SQLServer内存故障之前，请阅读SQLServer内存架构基础。
如果你通过这篇博文不能找到原因，或者如果你想说明在SQL Server错误日志里dbcc memorystatus打印输出的错误，在这篇博文的评论部分或者在facebook用户组贴出来。我们将尝试协助你。
MemToLeave errors:
|
1
2
3
4
5
|
SQL Server 2000
WARNING: Failed to reserve contiguousmemory of Size= 65536.
WARNING: Clearing procedure cache to freecontiguous memory.
Error: 17802 “Could not create server eventthread.”
SQL Server could not spawn process_loginreadthread.
|
|
1
2
3
4
|
SQL Server 2005/2008
Failed Virtual Allocate Bytes:FAIL_VIRTUAL_RESERVE 122880
Failed to initialize the CommonLanguage Runtime (CLR) v2.0.50727 due to memory pressure. This is probably dueto memory pressure in the MemToLeave region of memory
|
Buffer Pool errors:
|
1
2
3
4
5
|
BPool::Map: no remappableaddress found.
BufferPool out of memorycondition
LazyWriter: warning, no freebuffers found.
|
Either BPool (or) MemToLeave errors:
|
1
2
3
4
5
6
|
Error: 17803 “Insufficient memory available..”
Buffer Distribution: Stolen=7901 Free=0 Procedures=1 Inram=201842Dirty=0 Kept=572…
Error: 701, Severity: 17,State: 123.
There is insufficient system memory to runthis query.
There is insufficient system memory inresource pool ‘default’ to run this query
|
Working set trim and page out errors(external memory pressure)
|
1
2
3
|
A significant part of SQL Server processmemory has been paged out. This may result in performance degradation.
A significant part of sql server processmemory has been paged out. This may result in a performance degradation.Duration: 0 seconds. Working set (KB): 2007640, committed (KB): 4594040, memoryutilization: 43%.
|
如果你看到以上错误，请跳转到SQLServer进程内存的重要部分被页交换出。
第1部分（MTL错误）：
如果问题是使用MTL，我们需要要确定SQL Server或者一些Non-SQL组件使用了大多数MemToLeave内存（记得MTL是什么吗？请参阅SQLServer内存架构）。
SQL Server 2000：在DBCCmemorystatus输出中，OS Reserved和OS Committed计数器将会告诉你SQL Server自己在MTL中使用了多少页。
注意：每页是8192字节，因此，MultipyOS Committed * 8192字节/1024 得到MB。
SQL Server 2005/2008：在SQL Server错误日志中OOM错误之后，立即打印的DBCCmemorystatus输出中，捕获所有节点（内存节点Id = 0,1..n）的MultiPage Allocator总和。这将告诉你SQL Server自己在MTL使用了多少KB。
你也可以从sys.dm_os_memory_clerks求multi_pages_kb的总和：
|
1
|
select
sum
(multi_pages_kb) fromsys.dm_os_memory_clerks
|
如果SQL Server自身使用了MemToLeave内存的大部分，查看DBCC MEMORYSTATUS输出的MultiPage Allocator值，确定哪个memory clerk消耗内存的大多数。
Sys.dm_os_memory_clerks输出也将表明哪个memoryclerk正消耗MTL内存的大多数。使用以下查询。使用sys.dm_os_memory_objects将会深入了解。
|
1
2
3
4
5
|
select
*
from
sys.dm_os_memory_clerksorder
by
multi_pages_kb
desc
select
b.type,a.type,* fromsys.dm_os_memory_objects a,sys.dm_os_memory_clerks b
wherea.page_allocator_address=b.page_allocator_address
order
by
b.multi_pages_kb
desc
,a.max_pages_allocated_count
desc
|
如果SQL Server拥有的内存非常少，确定是否有COM对象、SQL Mail或第三方扩展存储过程正被使用，如果可能将它们从进程中移出。
COM对象：
COM对象可以在每个sp_OACreate调用中利用可选的第三个参数（[context]）从进程中移出。如果传递给sp_OACreate的第三个参数的int值为4，SQL将尝试在它自己的dllhost.exe进程的外部实例化该对象。更多关于[context]参数可以在联机帮助“sp_OACreate”专题里找到。
警告：大多数COM对象在进程外运行良好，而某些会失败。我们应该使用context=4运行一些功能性测试，确保它们的对象可以成功在进程外运行。
链接服务器OLEDB提供者：
链接服务器OLEDB提供者可以通过设置“AllowInProcess”OLEDB提供者选项将该提供者设置为0来移出进程。对于每个SQL实例提供者选项存储在注册表的以下位置：
|
1
2
3
|
Default Instance:HKLM\SOFTWARE\Microsoft\MSSQLServer\Providers
Named Instance: HKLM\SOFTWARE\Microsoft\Microsoft SQL
Server\<instance>\Providers
|
如果对于相关第三方提供者的AllowInProcess注册表值不存在，以REG_DWORD值创建它并赋值为0.有些OLEDB提供者不能被成功移出进程，但是大多数可以。
扩展存储过程：
扩展存储过程总是在进程内运行；没有直接的方法移出进程。然而，在某些情况下可以将扩展存储过程放在一个独立的SQL实例，并使用服务器对服务器的RPC调用来在远程实例执行它们。该技术在KB 243428中有详细描述。
第2部分（BPOOL错误）：
如果问题是使用BPOOL，在SQL Server错误日志的OOM错误后立即打印的DBCC memorystatus输出，捕获所有节点（内存节点Id=0,1..n）的singlePageAllocator总和。这将会告诉你在MTL中每个memory clerk使用了多少KB。
Sys.dm_os_memory_clerks输出也表明哪个memoryclerk正消耗大多数BPOOL（single_pages_kb）内存。使用以下查询，你可以通过sys.dm_os_memory_objects深入了解：
|
1
2
3
4
|
select
*
from
sys.dm_os_memory_clerksorder
by
Single_pages_kb
desc
select
b.type,a.type,* fromsys.dm_os_memory_objects a,sys.dm_os_memory_clerks b
wherea.page_allocator_address=b.page_allocator_address
order
by
b.single_pages_kb
desc
|
sys.dm_os_memory_clerks可以提供SQLServer内存状态的完整图片，并可以sys.dm_os_memory_objects细化。
注意：single_pages_kb是Bpool，multi_pages_kb是MTL
可以帮助SQL Server内存故障排除的其他视图：
|
1
2
3
4
5
|
select
*
from
sys.dm_os_memory_objects
select
*
from
sys.dm_os_memory_pools
select
*
from
sys.dm_os_memory_nodes
select
* fromsys.dm_os_memory_cache_entries
select
* fromsys.dm_os_memory_cache_hash_tables
|
一些用于SQL Server内存故障排除的查询：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
|
--Bpool statistics
select
(
cast
(bpool_committed
as
bigint
) * 8192)/(1024*1024)
as
bpool_committed_mb,
(
cast
(bpool_commit_target
as
bigint
) *8192) / (1024*1024)
as
bpool_target_mb,
(
cast
(bpool_visible
as
bigint
)* 8192) /(1024*1024)
as
bpool_visible_mb
from
sys.dm_os_sys_info
go
-- Get me physical RAM installed and sizeof user VAS
select
physical_memory_in_bytes/(1024*1024)
as
phys_mem_mb,
virtual_memory_in_bytes/(1024*1024) asuser_virtual_address_space_size
from
sys.dm_os_sys_info
go
--System memory information
select
total_physical_memory_kb/(1024) asphys_mem_mb,
available_physical_memory_kb/(1024) asavail_phys_mem_mb,
system_cache_kb/(1024)
as
sys_cache_mb,
(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024)
as
kernel_pool_mb,
total_page_file_kb/(1024)
as
total_virtual_memory_mb,
available_page_file_kb/(1024) asavailable_virtual_memory_mb,
system_memory_state_desc
from
sys.dm_os_sys_memory
go
-- Memory utilized by SQLSERVR processGetMemoryProcessInfo() API used for this
select
physical_memory_in_use_kb/(1024) assql_physmem_inuse_mb,
locked_page_allocations_kb/(1024) asawe_memory_mb,
total_virtual_address_space_kb/(1024) asmax_vas_mb,
virtual_address_space_committed_kb/(1024)
as
sql_committed_mb,
memory_utilization_percentage asworking_set_percentage,
virtual_address_space_available_kb/(1024)
as
vas_available_mb,
process_physical_memory_low asis_there_external_pressure,
process_virtual_memory_low asis_there_vas_pressure
from
sys.dm_os_process_memory
go
--Reosurce monitor ringbuffer
select
*
from
sys.dm_os_ring_buffers
where
ring_buffer_type
like
'RING_BUFFER_RESOURCE%'
go
--Memory in each node
select
memory_node_id
as
node,virtual_address_space_reserved_kb/(1024)
as
VAS_reserved_mb,
virtual_address_space_committed_kb/(1024)
as
virtual_committed_mb,
locked_page_allocations_kb/(1024) aslocked_pages_mb,
single_pages_kb/(1024)
as
single_pages_mb,
multi_pages_kb/(1024)
as
multi_pages_mb,
shared_memory_committed_kb/(1024) asshared_memory_mb
from
sys.dm_os_memory_nodes
where
memory_node_id != 64
go
--Vas summary
with
vasummary(
Size
,reserved,
free
)
as
(
select
size
= vadump.
size
,
reserved =
SUM
(
case
(
convert
(
int
,vadump.base) ^ 0)
when
0
then
0
else
1end),
free
=
SUM
(
case
(
convert
(
int
, vadump.base) ^0x0)
when
0
then
1
else
0
end
)
from
(
select
CONVERT
(varbinary,
sum
(region_size_in_bytes))
as
size
,
region_allocation_base_address
as
base
from
sys.dm_os_virtual_address_dump
where
region_allocation_base_address<> 0x0
group
by
region_allocation_base_address
UNION
(
select
CONVERT
(varbinary,region_size_in_bytes),
region_allocation_base_address
from
sys.dm_os_virtual_address_dump
where
region_allocation_base_address = 0x0)
)
as
vadump
group
by
size
)
select
*
from
vasummary
go
-- Clerks that are consuming memory
select
*
from
sys.dm_os_memory_clerks
where
(single_pages_kb > 0)
or
(multi_pages_kb > 0)
or
(virtual_memory_committed_kb > 0)
go
-- Get me stolen pages
--
select
(
SUM
(single_pages_kb)*1024)/8192 astotal_stolen_pages
from
sys.dm_os_memory_clerks
go
-- Breakdown clerks with stolen pages
select
type,
name
,
sum
((single_pages_kb*1024)/8192)
as
stolen_pages
from
sys.dm_os_memory_clerks
where
single_pages_kb > 0
group
by
type,
name
order
by
stolen_pages
desc
go
-- Non-Bpool allocation from SQL Serverclerks
select
SUM
(multi_pages_kb)/1024 astotal_multi_pages_mb
from
sys.dm_os_memory_clerks
go
-- Who are Non-Bpool consumers
--
select
type,
name
,
sum
(multi_pages_kb)/1024as multi_pages_mb
from
sys.dm_os_memory_clerks
where
multi_pages_kb > 0
group
by
type,
name
order
by
multi_pages_mb
desc
go
-- Let's now get the total consumption ofvirtual allocator
--
selectSUM(virtual_memory_committed_kb)/1024
as
total_virtual_mem_mb
from
sys.dm_os_memory_clerks
go
-- Breakdown the clerks who use virtualallocator
select
type,
name
,
sum
(virtual_memory_committed_kb)/1024
as
virtual_mem_mb
from
sys.dm_os_memory_clerks
where
virtual_memory_committed_kb > 0
group
by
type,
name
order
by
virtual_mem_mb
desc
go
-- memory allocated by AWE allocator API'S
select
SUM
(awe_allocated_kb)/1024 astotal_awe_allocated_mb
from
sys.dm_os_memory_clerks
go
-- Who clerks consumes memory using AWE
select
type,
name
,
sum
(awe_allocated_kb)/1024
as
awe_allocated_mb
from
sys.dm_os_memory_clerks
where
awe_allocated_kb > 0
group
by
type,
name
order
by
awe_allocated_mb
desc
go
-- What is the total memory used by theclerks?
select
(
sum
(multi_pages_kb)+
SUM
(virtual_memory_committed_kb)+
SUM
(awe_allocated_kb))/1024
from
sys.dm_os_memory_clerks
go
--
-- Does this sync up with what the nodethinks?
--
selectSUM(virtual_address_space_committed_kb)/1024
as
total_node_virtual_memory_mb,
SUM
(locked_page_allocations_kb)/1024 astotal_awe_memory_mb,
SUM
(single_pages_kb)/1024 astotal_single_pages_mb,
SUM
(multi_pages_kb)/1024 astotal_multi_pages_mb
from
sys.dm_os_memory_nodes
where
memory_node_id != 64
go
--
-- Total memory used by SQL Server throughSQLOS memory nodes
-- including DAC node
-- What takes up the rest of the space?
select
(
SUM
(virtual_address_space_committed_kb)+
SUM
(locked_page_allocations_kb)+
SUM
(multi_pages_kb))/1024 astotal_sql_memusage_mb
from
sys.dm_os_memory_nodes
go
--
-- Who are the biggest cache stores?
select
name
, type,(
SUM
(single_pages_kb)+
SUM
(multi_pages_kb))/1024
as
cache_size_mb
from
sys.dm_os_memory_cache_counters
where
type
like
'CACHESTORE%'
group
by
name
, type
order
by
cache_size_mb
desc
go
--
-- Who are the biggest user stores?
select
name
, type,(
SUM
(single_pages_kb)+
SUM
(multi_pages_kb))/1024
as
cache_size_mb
from
sys.dm_os_memory_cache_counters
where
type
like
'USERSTORE%'
group
by
name
, type
order
by
cache_size_mb
desc
go
--
-- Who are the biggest object stores?
select
name
, type,(
SUM
(single_pages_kb)+
SUM
(multi_pages_kb))/1024
as
cache_size_mb
from
sys.dm_os_memory_clerks
where
type
like
'OBJECTSTORE%'
group
by
name
, type
order
by
cache_size_mb
desc
go
--Which object is really consuming fromclerk
select
*
from
sys.dm_os_memory_clerks a
,sys.dm_os_memory_objects b
where
a.page_allocator_address =b.page_allocator_address
--group by a.type, b.type
order
by
a.type, b.type
go
--To get the list of 3rd party DLL loadedinside SQL server memory
select
*
from
sys.dm_os_loaded_moduleswhere company <>
'Microsoft Corporation'
go
--Which database page is in my memory
select
db_name(database_id),(
cast
(
count
(*)
as
bigint
)*8192)/1024/1024
as
"size in mb"
fromsys.dm_os_buffer_descriptors
group
by
db_name(database_id)
|
其他SQL Server内存博文：
https://mssqlwiki.com/sqlwiki/sql-performance/basics-of-sql-server-memory-architecture/
https://mssqlwiki.com/2012/06/27/a-significant-part-of-sql-server-process-memory-has-been-paged-out/
其他性能博文：
https://mssqlwiki.com/sqlwiki/sql-performance/io-bottlenecks/
https://mssqlwiki.com/sqlwiki/sql-server-agent/sql-agent-maxworkerthreads-and-agent-subsystem/
https://mssqlwiki.com/sqlwiki/sql-performance/async_network_io-or-network_io/
本文转自UltraSQL51CTO博客，原文链接： http://blog.51cto.com/ultrasql/1789837，如需转载请自行联系原作者
