更新时间:2022-09-15 22:44:07
翻译自: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,如需转载请自行联系原作者