SQL Server内存故障排除

  1. 云栖社区>
  2. 博客>
  3. 正文

SQL Server内存故障排除

技术小甜 2017-11-17 16:15:00 浏览1115
展开阅读全文

翻译自:https://blogs.msdn.microsoft.com/karthick_pk/2012/06/15/troubleshooting-sql-server-memory/

 

SQL Server内存故障排除的第一步,是识别是否低内存条件出现在了MemToLeaveBPool或者因为外部内存压力。

 

注意:如果你不知道什么是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 ReservedOS 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_clerksmulti_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值为4SQL将尝试在它自己的dllhost.exe进程的外部实例化该对象。更多关于[context]参数可以在联机帮助“sp_OACreate”专题里找到。

警告:大多数COM对象在进程外运行良好,而某些会失败。我们应该使用context=4运行一些功能性测试,确保它们的对象可以成功在进程外运行。

 

链接服务器OLEDB提供者:

链接服务器OLEDB提供者可以通过设置“AllowInProcessOLEDB提供者选项将该提供者设置为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正消耗大多数BPOOLsingle_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_kbBpoolmulti_pages_kbMTL

 

可以帮助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,freeas (select size = vadump.size,
reserved = SUM(case(convert(int,vadump.base) ^ 0)  when then else 1end),
free SUM(case(convert(int, vadump.base) ^0x0) when then else 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, namesum(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/05/18/sql-server-performance-degraded-in-32-bit-sql-server-after-i-adding-additional-ram/

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,如需转载请自行联系原作者




网友评论

登录后评论
0/500
评论
技术小甜
+ 关注