Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3      [все]
 Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
Периодически сервер стал вылетать в
Ошибка: 701, серьезность: 17, состояние: 123 There is insufficient system memory in resource pool 'internal' to run this query.

Название ОС:                    MicrosoftR Windows ServerR 2008 Enterprise 
Версия ОС: 6.0.6002 Service Pack 2 сборка 6002
Тип системы: X86-based PC
Процессор(ы): Число процессоров - 4.
[01]: x64 Family 6 Model 29 Stepping 1 GenuineIntel ~2670 МГц
[02]: x64 Family 6 Model 29 Stepping 1 GenuineIntel ~2136 МГц
[03]: x64 Family 6 Model 29 Stepping 1 GenuineIntel ~2136 МГц
[04]: x64 Family 6 Model 29 Stepping 1 GenuineIntel ~2136 МГц
Полный объем физической памяти: 16 376 МБ
Файл подкачки: Макс. размер: 32 907 МБ


Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) 
Sep 22 2011 00:28:06
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)


min server memory (MB) 0
max server memory (MB) 12288
min memory per query (KB) 1024
awe enabled 1

SQLServer:Memory Manager Target Server Memory (KB) 12582912
SQLServer:Memory Manager Total Server Memory (KB) 10679808
Памяти серверу вроде как достаточно, общая производительность вопросов не вызывает вообще.


>bcdedit /enum:
Диспетчер загрузки Windows
--------------------
идентификатор {bootmgr}
device partition=C:
description Windows Boot Manager
locale ru-RU
inherit {globalsettings}
default {current}
displayorder {current}
toolsdisplayorder {memdiag}
timeout 5
resume No

Загрузка Windows
-------------------
идентификатор {current}
device partition=C:
path \Windows\system32\winload.exe
description Microsoft Windows Server 2008
locale ru-RU
inherit {bootloadersettings}
osdevice partition=C:
systemroot \Windows
resumeobject {3a048c8b-0b20-11df-8c30-8d4e4b63b24f}
nx OptOut
pae ForceEnable

Кусок из errorlog c memorystatus:
+
2012-09-18 15:07:54.89 spid78      
Memory Manager KB
---------------------------------------- ----------
VM Reserved 1557356
VM Committed 157924
AWE Allocated 10682368
Reserved Memory 1024
Reserved Memory In Use 0
2012-09-18 15:07:54.89 spid78
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 1554540
VM Committed 155220
AWE Allocated 10682368
MultiPage Allocator 24824
SinglePage Allocator 151608
2012-09-18 15:07:54.89 spid78
Memory node Id = 32 KB
---------------------------------------- ----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 151616
2012-09-18 15:07:54.89 spid65 Ошибка: 701, серьезность: 17, состояние: 123.
2012-09-18 15:07:54.89 spid65 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 7720
MultiPage Allocator 2096
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 1506552
VM Committed 108784
AWE Allocated 10682368
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 3760
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 11744
MultiPage Allocator 896
2012-09-18 15:07:54.89 spid65 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 664
MultiPage Allocator 88
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 0
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 10560
VM Committed 10560
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 42424
MultiPage Allocator 5152
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 720
MultiPage Allocator 0
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 216
MultiPage Allocator 344
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 16
2012-09-18 15:07:54.89 spid78
MEMORYCLERK_SNI (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 56
MultiPage Allocator 32
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 648
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1152384
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24664
MultiPage Allocator 9088
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_SOSNODE (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 1520
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24680
MultiPage Allocator 10608
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 576
SM Committed 576
SinglePage Allocator 0
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2224
MultiPage Allocator 168
2012-09-18 15:07:54.90 spid78
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1736
MultiPage Allocator 40
2012-09-18 15:07:54.90 spid78
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 568
MultiPage Allocator 200
2012-09-18 15:07:54.90 spid78
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 88
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2012-09-18 15:07:54.90 spid78
CACHESTORE_STACKFRAMES (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2012-09-18 15:07:54.90 spid78
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
2012-09-18 15:07:54.90 spid78
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 136
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 376
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid60 Error: 701, Severity: 17, State: 130.
2012-09-18 15:07:54.90 spid60 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid78
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 224
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid78
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid78
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 23120
MultiPage Allocator 1760
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid78
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1344
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid78
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3792
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.90 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.90 spid78
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 22592
MultiPage Allocator 384
2012-09-18 15:07:54.90 spid78
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 472
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
USERSTORE_SXC (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-09-18 15:07:54.90 spid78
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 480
MultiPage Allocator 0
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 384
MultiPage Allocator 0
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 784
MultiPage Allocator 56
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_SNI_PACKET (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 784
MultiPage Allocator 112
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 0
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 4100
VM Committed 4100
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3664
MultiPage Allocator 0
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_LOCK_MANAGER (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2012-09-18 15:07:54.91 spid78
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 4100
VM Committed 4100
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3688
MultiPage Allocator 0
2012-09-18 15:07:54.91 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.91 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.91 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.91 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.91 spid52 Error: 701, Severity: 17, State: 123.
2012-09-18 15:07:54.91 spid52 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.91 spid76 Error: 701, Severity: 17, State: 130.
2012-09-18 15:07:54.91 spid76 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:54.92 spid76 Error: 701, Severity: 17, State: 130.
2012-09-18 15:07:54.92 spid76 There is insufficient system memory in resource pool 'internal' to run this query.
2012-09-18 15:07:55.00 spid78
Buffer Pool Value
---------------------------------------- ----------
Committed 1334976
Target 1572864
Database 718603
Dirty 50800
In IO 0
Latched 1
Free 599352
Stolen 17021
Reserved 0
Visible 171008
Stolen Potential 145436
Limiting Factor 10
Last OOM Factor 10
Last OS Error 0
Page Life Expectancy 1047110
2012-09-18 15:07:55.00 spid78
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 4174827520
Available Virtual Memory 222334976
Available Paging File 20635893760
Working Set 237846528
Percent of Committed Memory in WS 100
Page Faults 10340698
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2012-09-18 15:07:55.00 spid78
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 5
TotalPages 220
InUsePages 0
2012-09-18 15:07:55.00 spid78
Global Memory Objects Pages
---------------------------------------- ----------
Resource 777
Locks 461
XDES 312
SETLS 24
SE Dataset Allocators 48
SubpDesc Allocators 24
SE SchemaManager 1741
SE Column Metadata Cache 1328
SQLCache 67
Replication 2
ServerGlobal 28
XP Global 2
SortTables 2
2012-09-18 15:07:55.00 spid78
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 107773
Current Max 107773
Future Max 107773
Physical Max 122833
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2012-09-18 15:07:55.00 spid78
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 5672
Current Max 5672
Future Max 5672
2012-09-18 15:07:55.00 spid78
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 1122902016
Target Memory 79921152
Last Notification 2
Timeout 6
Early Termination Factor 5
2012-09-18 15:07:55.00 spid78
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
2012-09-18 15:07:55.00 spid78
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2012-09-18 15:07:55.00 spid78
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2012-09-18 15:07:55.00 spid78
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 0
Reserved Limit 144048
2012-09-18 15:07:55.00 spid78
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 163004
Predicted 163816
Private Target 0
Private Limit 0
Total Target 162457
Total Limit 306505
OOM Count 338
2012-09-18 15:07:55.00 spid78
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 7499
Rate -3270
Target Allocations 6273
Future Allocations 0
Overall 137073
Last Notification 2
2012-09-18 15:07:55.00 spid78
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 11457
Rate 205
Target Allocations 9756
Future Allocations 0
Overall 137073
Last Notification 2
2012-09-18 15:07:55.00 spid78
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 144048
Rate 607
Target Allocations 121021
Future Allocations 30708
Overall 137073
Last Notification 2


Прошу советов в понимании причины ошибки.

Пока вызывают подозрения следующие строки:
MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
SinglePage Allocator 1152384

Process/System Counts Value
---------------------------------------- ----------
Process physical memory low 1


Также не совсем ясно, как трактовать эти данные:
Query Memory Objects (internal)               Value
---------------------------------------- ----------
Available 107773
Current Max 107773
Future Max 107773
Physical Max 122833

Прошу помочь советом, что еще полезного можно посмотреть / подкрутить в такой ситуации.
19 сен 12, 09:45    [13186436]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
step_ks,
возможно у вас больше 4Гиг не заберёт SQL Server, он то думает что работает на Win32, даже на Windows64.
Не знаю как там "мутить" с AWE/PAE, но у меня когда не получилось втыкнуть "как проверить работает ли" (ошибки такой не было). Как-то 32 СКЛ сервер на 32 Винде было проще.
Такая ошибка была только на Экспрессе или если ограничить память 1ГБ и запуститить в одном месте FTS по ХТМЛькам.
19 сен 12, 12:31    [13188040]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
NIIIK, ОС 32-разрядная и сервер берет больше 4 Гб (см. выше)
19 сен 12, 13:57    [13188809]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
+ FTS не используется, xml - почти нет (в отдельных местах изредка ничтожные строки по паре килобайт оpenxml-ом обрабатываются)
19 сен 12, 14:00    [13188834]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2476
Блог
step_ks,

Нашел такую вот темку на sqlservercentral.
Там грешат на использование расширенных процедур в которых есть утечки памяти. Также пишут, что каким-то образом с этим может быть связан resource governor. В общем, посмотрите, если еще не смотрели, может натолкнет на какие-то мысли...
19 сен 12, 14:31    [13189269]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
SomewhereSomehow, спасибо, эту тему не читал, но много уже чего пересмотрел.
ActiveX-ов в хранимках нет. Расширенные процедуры есть, но не должны течь и памяти много потреблять. Эксплуатируются уже не один год на сотнях серверов и более простых конфигураций. А тут вот началось на одном. На разных запросах, периодически. Кроме сиквела стороннего софта на сервере нет.
19 сен 12, 15:01    [13189602]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
да, resource governor не задействован.
19 сен 12, 15:03    [13189623]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
step_ks,

а что покажет этот запрос (скриншот)?

+
	select 'CPU & Memory' [Hardware info]
	
		select 
		cpu_count/hyperthread_ratio [Physical CPUs],
		cpu_count [Available CPUs],
		ceiling(physical_memory_in_bytes/1024./1024.) [Physical Memory (MB)]
		from sys.dm_os_sys_info 

	select 'Current Memory consumption:' [Memory]
	select @@servername [SQL Instance],
			(select cntr_value/1024 from master..sysperfinfo where  counter_name = 'Total Server Memory (KB)') [Current Memory (MB)],
			(select cntr_value/1024 from master..sysperfinfo where  counter_name = 'Target Server Memory (KB)') [Target Memory (MB)]

	select 'Memory settings:' [Configuration]
			select description, value_in_use
			from master.sys.configurations  
			where name in ('min server memory (MB)','max server memory (MB)')
	
	select convert(money,cntr_value/60.) [Page Life Expectancy (min)] 
	FROM sys.dm_os_performance_counters 
	WHERE [object_name] like '%:Buffer Manager%' 
	AND counter_name='Page life expectancy'


	select 'Usage by DB:  ' + convert(varchar(20),convert(numeric(10,2),count(*)/128.)) +' (MB)' [Buffer Cache] 
		FROM sys.dm_os_buffer_descriptors
		
	SELECT 
		CASE database_id 
			WHEN 32767 THEN 'ResourceDb' 
			ELSE db_name(q.database_id) 
			END AS [Database],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id and is_modified=0) [Clean (MB)],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id and is_modified=1) [Dirty (MB)],
		(select convert(numeric(10,2),count(*)/128.) from sys.dm_os_buffer_descriptors where database_id=q.database_id) [Total (MB)]
	FROM sys.dm_os_buffer_descriptors q
	--where is_modified = 0 
	GROUP BY database_id
	ORDER BY 2 DESC


	select 'Usage by all objects (Grouped):  ' +  
			convert(varchar(20),ceiling(sum(cast(size_in_bytes as numeric(10,2)))/1024./1024.)) + ' (MB)' [Procedure Cache]
	FROM sys.dm_exec_cached_plans

	select  objtype [Object Type], 
			convert(numeric(10,2),sum(size_in_bytes/1024.)/1024.) [Size (MB)]
			, avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024./1024. AS [Total MB - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
	from sys.dm_exec_cached_plans 
	group by objtype
	order by 2 desc
19 сен 12, 15:21    [13189800]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
komrad
step_ks,

а что покажет этот запрос (скриншот)?


Завтра обязательно выполню именно ваши запросы, сегодня уже возможности нет.

Кое-что похожее недавно выполнялось со следующимим результатами:

из sysperfinfo, значение приведено к KB:
countervalue
SQLServer:Memory Manager - Target Server Memory (KB)12582912
SQLServer:Memory Manager - Total Server Memory (KB)10679808



из sp_configure:
namerun_value
min server memory (MB)0
max server memory (MB)12288



SELECT OBJECT_NAME,cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = N'SQLServer:Buffer Manager' and counter_name = N'Page life expectancy'
OBJECT_NAME [Page Life Expectancy]
SQLServer:Buffer Manager 1049095



SELECT TOP 10 DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 
AND database_id <> 32767 
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;
Database NameCached Size (MB)
svod2288.289062
budget20121694.757812
budget2011221.578125
kadr74.789062
delo7.453125
budget2011_temp7.453125
budget2011_test7.304687
budget2011_perechen7.257812
ministr20117.187500
budget2011_out7.125000



SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <=4
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC;
Database NameCached Size (MB)
tempdb680.031250
master515.000000
msdb9.312500
model0.179687



SELECT  cp.objtype, size=sum(cp.size_in_bytes),count(*)
FROM sys.dm_exec_cached_plans AS cp
group by cp.objtype
ORDER BY 3 desc
objtypesize
Adhoc1354055681578
Prepared84770816945
Proc332210176653
View28852224342
Trigger1118208081
UsrTab4014087
Check983046
19 сен 12, 16:07    [13190317]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
хм, 500 Мб для master в буфере...
19 сен 12, 16:43    [13190727]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2476
Блог
step_ks,

Да, про мастер это интересно.
Но еще интересно:
"min server memory (MB) 0 " и
"max server memory (MB) 12288"
как так?

У вас нет случайно там ограничения на макс. память и ограничение на макс. темпдб?
Может быть стоит помониторить запросы, которые требуют память и пытаются делать spill в tempdb?
Многие пишут так "работало-работало на 2000/2005 сервере - перешли на 2008 - такая ошибка" - у вас точно ничего не менялось? В том числе уровни совместимости может?
Вы говорите, что утечек нет, но мало ли, просто проверить, если отключить их, или поменять на clr, будет такая же фигня? И вообще, есть ли потребители памяти в процессе сиквела, которые не контролируются сиквелом, ком объекты там или хз что?
Честно говоря, не сталкивался с таким.
Но в любом случае, это интересно и хочется понять в чем дело.
Так что, отпишитесь.
19 сен 12, 19:57    [13191798]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6718
Off
SomewhereSomehow
Там грешат на использование расширенных процедур в которых есть утечки памяти. Также пишут, что каким-то образом с этим может быть связан resource governor.
А как текут всякие linked servers ко всяким оракаклам - мама не горюй.
Это надо хорошенько извратится чтобы хоть как-то работало.
19 сен 12, 22:17    [13192489]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
SomewhereSomehow
step_ks,
Но еще интересно:
"min server memory (MB) 0 " и
"max server memory (MB) 12288"
как так?

А что тут?

SomewhereSomehow

У вас нет случайно там ограничения на макс. память и ограничение на макс. темпдб?

что вы имеете ввиду?

SomewhereSomehow
Может быть стоит помониторить запросы, которые требуют память и пытаются делать spill в tempdb?

Это точно не в первую очередь. Тут как раз та ситуация, когда "у всех (от 2000 до 2008 R2) работает, а у этих нет"

SomewhereSomehow
Многие пишут так "работало-работало на 2000/2005 сервере - перешли на 2008 - такая ошибка" - у вас точно ничего не менялось? В том числе уровни совместимости может?

Переходов не было. Был 2008 RTM, на нем время от времени и раньше получали такое, но было реже, теперь стало чаще (растет база?). Первым делом накатили SP3 - не помогло. Уровень в 10 стоял точно довольно давно, если не всегда. Думаете, попробовать понизить для эксперимента?

SomewhereSomehow
Вы говорите, что утечек нет, но мало ли, просто проверить, если отключить их, или поменять на clr, будет такая же фигня?

Проверить никогда не помешает, конечно. Но отключить нереально - продакшен. Переписать на clr в разумные сроки тоже не представляется возможным, а, учитывая то, что clr может и вообще нормально не взлетать на 32 битах (MemToLeave), даже как-то не очень хочется (ведь у других-то всё работает!)

SomewhereSomehow
И вообще, есть ли потребители памяти в процессе сиквела, которые не контролируются сиквелом, ком объекты там или хз что?

Не должно быть комов вообще, но проверим еще раз. Как бы вот узнать еще нормально, есть ли вообще такие потребления.



Mnior
Off
SomewhereSomehow
Там грешат на использование расширенных процедур в которых есть утечки памяти. Также пишут, что каким-то образом с этим может быть связан resource governor.
А как текут всякие linked servers ко всяким оракаклам - мама не горюй.
Это надо хорошенько извратится чтобы хоть как-то работало.

Да, кстати. linked'ов нет.
19 сен 12, 23:11    [13192775]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
komrad
step_ks,

а что покажет этот запрос (скриншот)?


Вот. Только не скриншотом.
Вчера вечером меняли max server memory - подняли с 12 до 13 Гб и был для эксперимента включен 'optimize for ad hoc workloads' - поэтому данные уже несколько отличаются от того, что я давал вчера. Но ошибка все равно время от времени появляется. Что характерно - чаще к концу рабочего дня.

+ Результат
Hardware info
-------------
CPU & Memory

Physical CPUs Available CPUs Physical Memory (MB)
------------- -------------- ---------------------
4 24 16377




Memory
---------------------------
Current Memory consumption:

SQL Instance Current Memory (MB) Target Memory (MB)
----------------------------------- --------------------
PDC1 10429 13312



Configuration
----------------
Memory settings:

description value_in_use
------------------------------------------------
Minimum size of server memory (MB) 0
Maximum size of server memory (MB) 13312

Page Life Expectancy (min)
--------------------------
20085,2167


Buffer Cache
---------------------------------------
Usage by DB: 5947.69 (MB)


Database Clean (MB) Dirty (MB) Total (MB)
---------------------------------- ----------- ----------- -----------
svod 2418.05 0.49 2418.54
budget2012 1776.02 11.05 1787.06
master 515.53 9.66 525.19
tempdb 363.80 365.94 729.74
budget2011 226.05 0.05 226.09
kadr 74.55 0.24 74.79
ResourceDb 22.45 0.00 22.45
dwh_dag 8.48 0.02 8.51
msdb 8.38 1.08 9.45
budget2011_temp 7.45 0.00 7.45
delo 7.45 0.00 7.45
budget2011_test 7.30 0.00 7.30
budget2011_perechen 7.26 0.00 7.26
ministr2011 7.19 0.00 7.19
budget2011_out 7.12 0.01 7.13
budget2011_temp2 7.05 0.00 7.05
budget2011_04052011 6.94 0.00 6.94
svod2 6.63 0.00 6.63
Budget2009 6.39 0.01 6.40
Budget2010 6.27 0.02 6.29
Budget2010_proekt2 6.13 0.00 6.13
budget2011_bk 6.04 0.00 6.04
kadr_test 5.94 0.00 5.94
fin 5.85 0.01 5.86
budget2011_spr 5.84 0.00 5.84
Budget2010_test 5.61 0.00 5.61
Budget2010_perechen 5.44 0.00 5.44
fin_min 5.40 0.01 5.41
budget_pos_2009 4.29 0.00 4.29
budget2008 4.22 0.01 4.23
budget2007 3.41 0.01 3.41
revizor_mf 2.72 0.00 2.72
ReportServer 0.98 0.00 0.98
budget2012_del 0.85 0.00 0.85
budget2012_test_20120124 0.78 0.00 0.78
budget2012_120205_2012-09-06 0.77 0.00 0.77
Skif3 0.77 0.02 0.80
budget2012_test_20120201 0.60 0.00 0.60
budget2012_test_20120130 0.60 0.00 0.60
budget2012_PERECHEN 0.59 0.00 0.59
Skif31 0.44 0.00 0.44
ReportServerTempDB 0.40 0.00 0.40
dwh_mz 0.29 0.00 0.29
adminarch 0.23 0.00 0.23
model 0.18 0.00 0.18
Skif_TEMP 0.18 0.00 0.18
drweb 0.18 0.00 0.18


Procedure Cache
----------------------------------------------------------
Usage by all objects (Grouped): 343 (MB)


Object Type Size (MB) Avg Use Count Total MB - USE Count 1 Total Plans - USE Count 1
-------------------- ----------- ------------- ------------------------ -------------------------
Proc 243.18 471 49.898437 59
Prepared 58.73 26 13.218750 114
Adhoc 17.09 63 5.497879 202
View 16.12 134 0.000000 0
Trigger 7.48 36 1.117187 3
UsrTab 0.17 156 0.132812 1
Check 0.05 63 0.015625 1




По поводу 500 Мб под master в буфере - оказалось, что на сервере стоит DrWeb Enterprise Suite, пишущий логи или вроде того в свои таблицы в мастере. Попробуем отключить.
20 сен 12, 11:29    [13194583]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
step_ks
Вчера вечером меняли max server memory - подняли с 12 до 13 Гб и был для эксперимента включен 'optimize for ad hoc workloads' - поэтому данные уже несколько отличаются от того, что я давал вчера. Но ошибка все равно время от времени появляется. Что характерно - чаще к концу рабочего дня.

если включили только вчера, то как ошибка появляется к концу рабочего дня?
И дня то не прошло...

Экспериментировать надо не наобум, а имея факты на руках.
А кэш процедурный вы изменением памяти сбросили.


step_ks
По поводу 500 Мб под master в буфере - оказалось, что на сервере стоит DrWeb Enterprise Suite, пишущий логи или вроде того в свои таблицы в мастере. Попробуем отключить.

небось еще и под sa пишет

по названию сиквела (PDC1) - у вас случайно он не домен-контроллер заодно?
20 сен 12, 12:13    [13194990]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
komrad
step_ks
Вчера вечером меняли max server memory - подняли с 12 до 13 Гб и был для эксперимента включен 'optimize for ad hoc workloads' - поэтому данные уже несколько отличаются от того, что я давал вчера. Но ошибка все равно время от времени появляется. Что характерно - чаще к концу рабочего дня.

если включили только вчера, то как ошибка появляется к концу рабочего дня?
И дня то не прошло...

Действительно, вы внимательны. Меняли в районе 11-12 дня, судя по errorlog. Ошибка стала появляться после 16. Те результаты, что я выкладывал были получены до 11. Так что полдня плотной работы c новыми настройками прошло + сегодня с утра

komrad
Экспериментировать надо не наобум, а имея факты на руках.
А кэш процедурный вы изменением памяти сбросили.

К моменту выполнения ваших скриптов он уже вполне рабочий, достаточно времени прошло. Иначе я бы не стал выполнять.
+ Время вполне терпит, настройки вернуть назад и подождать день проблем не вызовет.

komrad
step_ks
По поводу 500 Мб под master в буфере - оказалось, что на сервере стоит DrWeb Enterprise Suite, пишущий логи или вроде того в свои таблицы в мастере. Попробуем отключить.

небось еще и под sa пишет

по названию сиквела (PDC1) - у вас случайно он не домен-контроллер заодно?
С мест сообщают, что контроллер. И разнести возможности пока нет.
20 сен 12, 12:46    [13195261]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
step_ks,

попробуйте добавить startup параметр -g384 в строку запуска сиквела
на 32-битных сиквелах это помогает решить проблему согласно google

+ проверьте в логе наличие такой записи:
"A significant part of sql server process memory has been paged out. This may result in a performance degradation"

step_ks
К моменту выполнения ваших скриптов он уже вполне рабочий, достаточно времени прошло.

тогда можно отключить optimize for ad-hoc workload - у вас adhoc запросов мало

Procedure Cache----------------------------------------------------------
Usage by all objects (Grouped): 343 (MB)
Object Type Size (MB) Avg Use Count Total MB - USE Count 1 Total Plans - USE Count 1
-------------------- ----------- ------------- ------------------------ -------------------------
Proc 243.18 471 49.898437 59
Prepared 58.73 26 13.218750 114
Adhoc 17.09 63 5.497879 202
View 16.12 134 0.000000 0
Trigger 7.48 36 1.117187 3
UsrTab 0.17 156 0.132812 1
Check 0.05 63 0.015625 1
20 сен 12, 14:10    [13196083]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
komrad
step_ks,

попробуйте добавить startup параметр -g384 в строку запуска сиквела
на 32-битных сиквелах это помогает решить проблему согласно google

ссылки не осталось у вас?
20 сен 12, 14:34    [13196325]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
step_ks
komrad
step_ks,

попробуйте добавить startup параметр -g384 в строку запуска сиквела
на 32-битных сиквелах это помогает решить проблему согласно google

ссылки не осталось у вас?


тут
20 сен 12, 14:41    [13196399]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
komrad
step_ks
пропущено...

ссылки не осталось у вас?


тут

тоже читал уже. Проверим этот -g, ок.
20 сен 12, 15:03    [13196661]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2476
Блог
step_ks,

Все понятно. Тогда извиняйте, пока, кроме как тщательно смотреть на предмет вещей которые потребляют память но сиквелом не контролируются, идей нет. Что про темпдб спрашивал, не принимайте в расчет, там другое. А насчет параметра -g в той же ссылке и написано одним из участников:
автор
We thought that the -g flag had solved the problem, but in fact this just extended our uptime from two to five weeks.
Т.е. типа подумали что это решило проблему, но на самом деле увеличило интервал между ошибками. Так что имейте ввиду.
20 сен 12, 15:56    [13197139]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
antair
Member

Откуда:
Сообщений: 3
У меня такая беда началась 24 сентября. До этого никогда не было. Windows updates?
1 окт 12, 14:22    [13249600]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
antair
У меня такая беда началась 24 сентября. До этого никогда не было. Windows updates?

Всё может быть, но врядли. Показывайте errorlog.

Поставил сегодня попробовать -g384, до этого неделю работало нормально.
Подозреваю таки этого клерка:
MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
SinglePage Allocator 1152384

ибо при нормальном полете на этом же сервере и на других рабочее значение на 2 порядка меньше.

http://msdn.microsoft.com/en-us/library/cc627395%28v=SQL.100%29.aspx
MEMORYCLERK_SQLQERESERVATIONS shows how much memory has been reserved by the query execution (QE) to run queries with sorts/joins. An out of memory error that occurs when reservations are high usually indicates a bug in the server.

Искать тяжелые по хэшам/сортам запросы придется, похоже.
1 окт 12, 14:51    [13249823]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
antair
Member

Откуда:
Сообщений: 3
+
2012-10-01 14:00:41.34 spid56 Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2012-10-01 14:00:41.34 spid56
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 120732803072
Available Virtual Memory 8570872111104
Available Paging File 156253941760
Working Set 4211482624
Percent of Committed Memory in WS 98
Page Faults 1976444
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2012-10-01 14:00:41.34 spid56
Memory Manager KB
---------------------------------------- ----------
VM Reserved 219194184
VM Committed 4194304
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 4194304
Current Committed 4194304
Pages Allocated 2798408
Pages Reserved 3070784
Pages Free 783856
Pages In Use 4013696
Page Alloc Potential -29104
NUMA Growth Phase 2
Last OOM Factor 1
Last OS Error 0
2012-10-01 14:00:41.34 spid56
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 219191048
VM Committed 2097144
Locked Pages Allocated 0
Pages Allocated 1062800
Pages Free 425416
Target Committed 2097144
Current Committed 2097144
Foreign Committed 40
Away Committed 0
Taken Away Committed 0
2012-10-01 14:00:41.34 spid56
Memory node Id = 1 KB
---------------------------------------- ----------
VM Reserved 3072
VM Committed 2097140
Locked Pages Allocated 0
Pages Allocated 1735600
Pages Free 358448
Target Committed 2097144
Current Committed 2097144
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
2012-10-01 14:00:41.34 spid56
Memory node Id = 64 KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 19480
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 600
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 20080
2012-10-01 14:00:41.34 spid393 Error: 701, Severity: 17, State: 65.
2012-10-01 14:00:41.34 spid393 There is insufficient system memory in resource pool 'default' to run this query.
2012-10-01 14:00:41.34 spid98 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.34 spid98 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 58443316
VM Committed 339328
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 840008
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1631640
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 58443316
VM Committed 339328
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2471648
2012-10-01 14:00:41.34 spid19s Error: 701, Severity: 17, State: 123.
2012-10-01 14:00:41.34 spid19s There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.34 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.34 spid97 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.34 spid97 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 256
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLQUERYEXEC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16624
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLQUERYEXEC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16880
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1416
2012-10-01 14:00:41.34 spid55 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.34 spid55 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLOPTIMIZER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1472
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 352
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 9984
VM Committed 9984
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 17176
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 9128
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------- ----------
VM Reserved 9984
VM Committed 9984
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 26304
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4744
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3216
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 7960
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 9461504
VM Committed 19688
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 59336
2012-10-01 14:00:41.34 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.34 spid56
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 768
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SQLHTTP (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 184
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SNI (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 184
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SNI (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 384
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 296
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 80
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_BHF (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 512
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_BHF (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 592
2012-10-01 14:00:41.35 spid132 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid132 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1024
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SQLQERESERVATIONS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3069128
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SQLQERESERVATIONS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3070152
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 6144
VM Committed 6144
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2012-10-01 14:00:41.35 spid98 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_XE_BUFFER (node 1) KB
---------------------------------------- ----------
VM Reserved 3072
VM Committed 3072
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_XE_BUFFER (Total) KB
---------------------------------------- ----------
VM Reserved 9216
VM Committed 9216
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_XTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_HOST (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 40
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 35472
2012-10-01 14:00:41.35 spid97 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 13736
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2600
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 51808
2012-10-01 14:00:41.35 spid19s Error: 18400, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SOSOS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 208768
VM Committed 208608
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 576
SM Committed 576
Pages Allocated 0
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 232
2012-10-01 14:00:41.35 Server Error: 18054, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_FILETABLE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3448
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_HADR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3392
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_SQLLOGPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 39608
2012-10-01 14:00:41.35 spid85 Error: 701, Severity: 17, State: 123.
2012-10-01 14:00:41.35 spid85 There is insufficient system memory in resource pool 'default' to run this query.
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_LWC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2012-10-01 14:00:41.35 spid56
MEMORYCLERK_FSCHUNKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
2012-10-01 14:00:41.35 spid56
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3336
2012-10-01 14:00:41.35 Server Error: 18054, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 10976
2012-10-01 14:00:41.35 spid56
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 112
2012-10-01 14:00:41.35 spid56
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
2012-10-01 14:00:41.35 spid56
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 88
2012-10-01 14:00:41.35 spid56
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid55 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid56
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid53 Error: 701, Severity: 17, State: 123.
2012-10-01 14:00:41.35 spid53 There is insufficient system memory in resource pool 'default' to run this query.
2012-10-01 14:00:41.35 spid56
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid132 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 Server Error: 9602, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_STACKFRAMES (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_STACKFRAMES (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2012-10-01 14:00:41.35 spid71 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid71 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 spid73 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid73 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 Server Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERTBLACS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 40
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2012-10-01 14:00:41.35 Server Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid71 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid73 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_SEHOBTCOLUMNATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 704
2012-10-01 14:00:41.35 spid56
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 88
2012-10-01 14:00:41.35 spid13s Error: 19032, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid56
CACHESTORE_SYSTEMROWSET (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 104
2012-10-01 14:00:41.35 spid56
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2012-10-01 14:00:41.35 spid56
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid56
CACHESTORE_CONVPRI (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid56
CACHESTORE_CONVPRI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2012-10-01 14:00:41.35 spid56
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2012-10-01 14:00:41.35 spid56
CACHESTORE_SEARCHPROPERTYLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid56
CACHESTORE_COLUMNSTOREOBJECTPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2012-10-01 14:00:41.35 spid56
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 14528
2012-10-01 14:00:41.35 spid56
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 880
2012-10-01 14:00:41.35 spid56
USERSTORE_DBMETADATA (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 312
2012-10-01 14:00:41.35 spid56
USERSTORE_DBMETADATA (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1192
2012-10-01 14:00:41.35 spid56
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2912
2012-10-01 14:00:41.35 spid56
USERSTORE_TOKENPERM (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 240
2012-10-01 14:00:41.35 spid56
USERSTORE_TOKENPERM (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3152
2012-10-01 14:00:41.35 spid56
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2200
2012-10-01 14:00:41.35 spid56
USERSTORE_OBJPERM (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2408
2012-10-01 14:00:41.35 spid56
USERSTORE_OBJPERM (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4608
2012-10-01 14:00:41.35 spid56
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1000
2012-10-01 14:00:41.35 spid56
USERSTORE_SXC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 912
2012-10-01 14:00:41.35 spid56
USERSTORE_SXC (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2012-10-01 14:00:41.35 spid56
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1920
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 200
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_LBSS (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_LBSS (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 392
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4072
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4824
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_SNI_PACKET (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8952
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 12576
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 16388
VM Committed 16388
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 11624
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 13848
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_LOCK_MANAGER (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 16388
VM Committed 16388
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 25496
2012-10-01 14:00:41.35 spid143 Error: 701, Severity: 17, State: 130.
2012-10-01 14:00:41.35 spid143 There is insufficient system memory in resource pool 'default' to run this query.
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_XACT_CACHE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 336
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_XACT_CACHE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 288
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_XACT_CACHE (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2012-10-01 14:00:41.35 spid56
OBJECTSTORE_XACT_CACHE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 640
2012-10-01 14:00:41.35 spid393 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid393 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 spid126 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid126 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 spid119 Error: 701, Severity: 17, State: 130.
2012-10-01 14:00:41.35 spid119 There is insufficient system memory in resource pool 'default' to run this query.
2012-10-01 14:00:41.35 spid393 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid126 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.35 spid140 Error: 701, Severity: 17, State: 130.
2012-10-01 14:00:41.35 spid140 There is insufficient system memory in resource pool 'default' to run this query.
2012-10-01 14:00:41.35 spid134 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid134 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 spid141 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid141 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 spid127 Error: 701, Severity: 17, State: 89.
2012-10-01 14:00:41.35 spid127 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-01 14:00:41.35 spid134 Error: 18056, Severity: 20, State: 29.
2012-10-01 14:00:41.35 spid134 The client was unable to reuse a session with SPID 134, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2012-10-01 14:00:41.35 spid127 Error: 18056, Severity: 20, State: 29.
2012-10-01 14:00:41.35 spid127 The client was unable to reuse a session with SPID 127, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2012-10-01 14:00:41.35 spid141 Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
2012-10-01 14:00:41.36 spid56
Buffer Pool Value
---------------------------------------- ----------
Database 308418
Simulated 76284
Target 294649856
Dirty 10884
In IO 0
Latched 3
Page Life Expectancy 32
2012-10-01 14:00:41.36 spid56
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 15
TotalPages 1989
InUsePages 69
2012-10-01 14:00:41.36 spid56
Global Memory Objects Pages
---------------------------------------- ----------
Resource 473
Locks 3187
XDES 492
DirtyPageTracking 24
SETLS 24
SubpDesc Allocators 24
SE SchemaManager 813
SE Column Metadata Cache 940
SE Column Metadata Cache Store 5
SQLCache 304
Replication 2
ServerGlobal 61
XP Global 2
SortTables 1028
2012-10-01 14:00:41.36 spid56
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 279357
Current Max 279357
Future Max 279357
Physical Max 384657
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2012-10-01 14:00:41.36 spid56
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 14703
Current Max 14703
Future Max 14703
2012-10-01 14:00:41.36 spid56
Remote Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 192328
Current Max 192328
2012-10-01 14:00:41.36 spid56
Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 1
Waiting 0
Available 364744
Current Max 364872
Future Max 364872
Physical Max 380157
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2012-10-01 14:00:41.36 spid56
Small Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 19203
Current Max 19203
Future Max 19203
2012-10-01 14:00:41.36 spid56
Remote Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 190078
Current Max 190078
2012-10-01 14:00:41.36 spid56
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 3489660928
Target Memory 2723479552
Last Notification 1
Timeout 6
Early Termination Factor 5
2012-10-01 14:00:41.36 spid56
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 96
Available Units 96
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
2012-10-01 14:00:41.36 spid56
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 24
Available Units 24
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2012-10-01 14:00:41.36 spid56
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2012-10-01 14:00:41.36 spid56
Optimization Queue (default) Value
---------------------------------------- ----------
Overall Memory 3489660928
Target Memory 2596708352
Last Notification 1
Timeout 6
Early Termination Factor 5
2012-10-01 14:00:41.36 spid56
Small Gateway (default) Value
---------------------------------------- ----------
Configured Units 96
Available Units 92
Acquires 4
Waiters 0
Threshold Factor 380000
Threshold 380000
2012-10-01 14:00:41.36 spid56
Medium Gateway (default) Value
---------------------------------------- ----------
Configured Units 24
Available Units 24
Acquires 0
Waiters 0
Threshold Factor 12
Threshold 54098090
2012-10-01 14:00:41.36 spid56
Big Gateway (default) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2012-10-01 14:00:41.37 spid56
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 128
Reserved Limit 409693
2012-10-01 14:00:41.37 spid56
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 103957
Predicted 103962
Private Target 0
Private Limit 0
Total Target 524288
Total Limit 524288
OOM Count 0
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 8669
Rate -302
Target Allocations 322345
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 18780
Rate -256
Target Allocations 332456
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 313676
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_COMMITTED (internal) Pages
---------------------------------------- ----------
Allocations 76506
Rate 5
Target Allocations 390187
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_XTP (internal) Pages
---------------------------------------- ----------
Allocations 2
Rate 0
Target Allocations 313678
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
Memory Pool (default) Pages
---------------------------------------- ----------
Allocations 10766
Predicted 106650
Private Target 0
Private Limit 0
Total Target 524288
Total Limit 524288
OOM Count 0
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- ----------
Allocations 7333
Rate -1084
Target Allocations 321009
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- ----------
Allocations 3305
Rate -12311
Target Allocations 316981
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_RESERVE (default) Pages
---------------------------------------- ----------
Allocations 128
Rate -2257216
Target Allocations 409688
Future Allocations 95039
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
MEMORYBROKER_FOR_XTP (default) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 313676
Future Allocations 0
Overall 425984
Last Notification 1
2012-10-01 14:00:41.37 spid56
Memory Broker Clerk (Buffer Pool) Pages
---------------------------------------- ----------
Total 308418
Simulated 76284
Simulation Benefit 0.0000010566
Internal Benefit 0
External Benefit 0
Value Of Memory 0
Periodic Freed 0
Internal Freed 0
2012-10-01 14:00:41.37 spid56 Error: 701, Severity: 17, State: 123.
2012-10-01 14:00:41.37 spid56 There is insufficient system memory in resource pool 'default' to run this query.

1 окт 12, 15:04    [13249937]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2241
step_ks
Подозреваю таки этого клерка:
MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
SinglePage Allocator 1152384

ибо при нормальном полете на этом же сервере и на других рабочее значение на 2 порядка меньше.
1 Гб вроде не очень много от 12 доступных. К тому же это зарезервированная память, реально использованной там может быть намного меньше. И пока она не используется запросами, то страницы данных и кэш процедур из неё не удаляются.

step_ks
Искать тяжелые по хэшам/сортам запросы придется, похоже.
sys.dm_exec_query_memory_grants
2 окт 12, 00:23    [13253199]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
Mind
step_ks
Подозреваю таки этого клерка:
MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
SinglePage Allocator 1152384

ибо при нормальном полете на этом же сервере и на других рабочее значение на 2 порядка меньше.
1 Гб вроде не очень много от 12 доступных. К тому же это зарезервированная память, реально использованной там может быть намного меньше. И пока она не используется запросами, то страницы данных и кэш процедур из неё не удаляются.

Кэш процедур в момент вылета, судя по всему, вообще вычищается.
Procedure Cache                               Value
---------------------------------------- ----------
TotalProcs 5
TotalPages 220

Что касается -g384 - вылеты остаются. И даже не реже.
+ новый errorlog после установки -g384
2012-10-03 17:26:04.73 spid64      
Memory Manager KB
---------------------------------------- ----------
VM Reserved 1421228
VM Committed 98544
AWE Allocated 4751360
Reserved Memory 1024
Reserved Memory In Use 0
2012-10-03 17:26:04.73 spid64
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 1418412
VM Committed 95840
AWE Allocated 4751360
MultiPage Allocator 23944
SinglePage Allocator 110904
2012-10-03 17:26:04.73 spid64
Memory node Id = 32 KB
---------------------------------------- ----------
VM Reserved 1728
VM Committed 1672
AWE Allocated 0
MultiPage Allocator 1600
SinglePage Allocator 110888
2012-10-03 17:26:04.73 spid59 Error: 701, Severity: 17, State: 130.
2012-10-03 17:26:04.73 spid59 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-03 17:26:04.73 spid69 Error: 701, Severity: 17, State: 65.
2012-10-03 17:26:04.73 spid69 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-03 17:26:04.73 spid61 Error: 701, Severity: 17, State: 130.
2012-10-03 17:26:04.73 spid61 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 6696
MultiPage Allocator 2096
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 1375480
VM Committed 54404
AWE Allocated 4751360
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 3760
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 3768
MultiPage Allocator 864
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 640
MultiPage Allocator 88
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 240
VM Committed 240
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 6848
VM Committed 6848
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 17280
MultiPage Allocator 5120
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 512
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 200
MultiPage Allocator 344
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 16
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SNI (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 32
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 336
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid61 Error: 701, Severity: 17, State: 130.
2012-10-03 17:26:04.73 spid61 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1106880
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- ----------
VM Reserved 4224
VM Committed 4224
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24112
MultiPage Allocator 9096
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SOSNODE (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 1520
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24128
MultiPage Allocator 10616
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 576
SM Committed 576
SinglePage Allocator 0
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 752
MultiPage Allocator 96
2012-10-03 17:26:04.73 spid64
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1744
MultiPage Allocator 40
2012-10-03 17:26:04.73 spid64
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 728
MultiPage Allocator 200
2012-10-03 17:26:04.73 spid64
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 288
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2012-10-03 17:26:04.73 spid64
CACHESTORE_STACKFRAMES (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 8
2012-10-03 17:26:04.73 spid64
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 16
2012-10-03 17:26:04.73 spid64
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 136
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 16
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 376
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 224
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 32
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 19920
MultiPage Allocator 1008
2012-10-03 17:26:04.73 spid64
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 1928
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 2024
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 21864
MultiPage Allocator 384
2012-10-03 17:26:04.73 spid64
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 256
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
USERSTORE_SXC (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 8
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 264
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 336
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 472
MultiPage Allocator 56
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_SNI_PACKET (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 0
MultiPage Allocator 56
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 472
MultiPage Allocator 112
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 400
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 4100
VM Committed 4100
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 5032
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_LOCK_MANAGER (node 32) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 24
MultiPage Allocator 0
2012-10-03 17:26:04.73 spid64
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 4100
VM Committed 4100
AWE Allocated 0
SM Reserved 0
SM Committed 0
SinglePage Allocator 5056
MultiPage Allocator 0
2012-10-03 17:26:04.78 spid64
Buffer Pool Value
---------------------------------------- ----------
Committed 593632
Target 1696927
Database 429054
Dirty 88415
In IO 0
Latched 0
Free 151459
Stolen 13119
Reserved 0
Visible 154624
Stolen Potential 133773
Limiting Factor 10
Last OOM Factor 10
Last OS Error 0
Page Life Expectancy 203824
2012-10-03 17:26:04.78 spid64
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 9957384192
Available Virtual Memory 375402496
Available Paging File 26917613568
Working Set 176160768
Percent of Committed Memory in WS 100
Page Faults 9451570
System physical memory high 1
System physical memory low 0
Process physical memory low 1
Process virtual memory low 0
2012-10-03 17:26:04.78 spid64
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 5
TotalPages 241
InUsePages 0
2012-10-03 17:26:04.78 spid64
Global Memory Objects Pages
---------------------------------------- ----------
Resource 713
Locks 632
XDES 222
SETLS 24
SE Dataset Allocators 48
SubpDesc Allocators 24
SE SchemaManager 1549
SE Column Metadata Cache 1037
SQLCache 58
Replication 2
ServerGlobal 28
XP Global 2
SortTables 2
2012-10-03 17:26:04.78 spid64
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 100691
Current Max 100691
Future Max 100691
Physical Max 110891
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
2012-10-03 17:26:04.78 spid64
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 5299
Current Max 5299
Future Max 5299
2012-10-03 17:26:04.78 spid64
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 1015316480
Target Memory 44081152
Last Notification 2
Timeout 6
Early Termination Factor 5
2012-10-03 17:26:04.78 spid64
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
2012-10-03 17:26:04.78 spid64
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
2012-10-03 17:26:04.78 spid64
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
2012-10-03 17:26:04.78 spid64
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 0
Reserved Limit 138360
2012-10-03 17:26:04.78 spid64
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 152220
Predicted 158050
Private Target 0
Private Limit 0
Total Target 146892
Total Limit 285252
OOM Count 0
2012-10-03 17:26:04.78 spid64
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 6997
Rate -1169
Target Allocations 5486
Future Allocations 0
Overall 123940
Last Notification 2
2012-10-03 17:26:04.78 spid64
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 6863
Rate -65
Target Allocations 5381
Future Allocations 0
Overall 123940
Last Notification 2
2012-10-03 17:26:04.78 spid64
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 138360
Rate 5830
Target Allocations 113062
Future Allocations 27722
Overall 123940
Last Notification 2
2012-10-03 17:26:04.78 spid64 Error: 701, Severity: 17, State: 130.
2012-10-03 17:26:04.78 spid64 There is insufficient system memory in resource pool 'internal' to run this query.
2012-10-03 17:26:42.43 spid59 Error: 701, Severity: 17, State: 130.
2012-10-03 17:26:42.43 spid59 There is insufficient system memory in resource pool 'internal' to run this query.
4 окт 12, 08:53    [13265344]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
antair
Member

Откуда:
Сообщений: 3
У меня, похоже, вылечилось последними апдейтами. (SQL 2012).
14 окт 12, 15:02    [13315682]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
antair,

MS SQL SERVER 2017 Developer Edition. Все баги с 701 ошибкой снова проявились во всей красе с первых же секунд с момента старта приложения. Не помог ни один совет с Microsoft и даже со stackoverflow.
25 фев 19, 20:14    [21819114]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36167
EV.P
antair,

MS SQL SERVER 2017 Developer Edition. Все баги с 701 ошибкой снова проявились во всей красе с первых же секунд с момента старта приложения. Не помог ни один совет с Microsoft и даже со stackoverflow.
Так не пишите с багами.
25 фев 19, 20:17    [21819119]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2241
EV.P
antair,

MS SQL SERVER 2017 Developer Edition. Все баги с 701 ошибкой снова проявились во всей красе с первых же секунд с момента старта приложения. Не помог ни один совет с Microsoft и даже со stackoverflow.
Это крик отчаяния чтобы вас пожалели? тогда к психотерапевту или у вас все таки какой-то вопрос по серверу?
1 мар 19, 01:45    [21822314]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
Mind
EV.P
antair,

MS SQL SERVER 2017 Developer Edition. Все баги с 701 ошибкой снова проявились во всей красе с первых же секунд с момента старта приложения. Не помог ни один совет с Microsoft и даже со stackoverflow.
Это крик отчаяния чтобы вас пожалели? тогда к психотерапевту или у вас все таки какой-то вопрос по серверу?


Конкретный вопрос - как избежать ошибки 701 "There is insufficient system memory in resource pool"?
Не помогло даже обновление до MS SQL Server 2019 CTP 2.2 и установка максимального объёма памяти, выделяемого серверу в 12Гб (при установленных в сервер 32 Гб).
5 мар 19, 17:57    [21825654]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
EV.P
Mind
пропущено...
Это крик отчаяния чтобы вас пожалели? тогда к психотерапевту или у вас все таки какой-то вопрос по серверу?


Конкретный вопрос - как избежать ошибки 701 "There is insufficient system memory in resource pool"?
Не помогло даже обновление до MS SQL Server 2019 CTP 2.2 и установка максимального объёма памяти, выделяемого серверу в 12Гб (при установленных в сервер 32 Гб).


Объём базы - 2 Гб сейчас. Количество транзакций в секунду около 20. В основном это update. Запросы несложные, так как бизнес-логика вынесена в сервер приложений (.Net C#).
5 мар 19, 17:58    [21825658]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
EV.P
Конкретный вопрос - как избежать ошибки 701 "There is insufficient system memory in resource pool"?

Переписать запрос.
Можно еще показать @@version, запрос и полное сообщение об ошибке.
5 мар 19, 23:56    [21825829]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
step_ks
EV.P
Конкретный вопрос - как избежать ошибки 701 "There is insufficient system memory in resource pool"?

Переписать запрос.
Можно еще показать @@version, запрос и полное сообщение об ошибке.


@@version:
Microsoft SQL Server 2019 (CTP2.2) - 15.0.1200.24 (X64) Dec 5 2018 16:51:26 Copyright (C) 2018 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )

Запрос:
exec sp_executesql N'if not exists (select 1 from trades where stellartradeid = @stellartradeid)
                                   insert into trades (stellartradeid, tradedate, buysell, traderid, volume, netvolume, price, nettradevolume, orderid, exchangetradeid,
                                                       outrightid, strategyid, tradestatusid, tradetypeid, modifieddatetime, indatetime, tradedatetimelocal, clearingdate,
                                                       ispem, issim)
                                            values (@stellartradeid, @tradedate, @buysell, @traderid, @volume, @netvolume, @price, @nettradevolume, @orderid, @exchangetradeid,
                                                    @outrightid, @strategyid, @tradestatusid, @tradetypeid, @modifieddatetime, @indatetime, @tradedatetimelocal,
                                                    @clearingdate, @ispem, @issim);',N'@stellartradeid int,@tradedate datetime2(7),@buysell int,@traderid int,@volume int,@netvolume decimal(19,8),@price decimal(15,8),@nettradevolume decimal(19,8),@orderid varchar(20),@exchangetradeid varchar(255),@tradestatusid int,@tradetypeid int,@outrightid int,@strategyid int,@modifieddatetime datetime2(7),@indatetime datetime2(7),@tradedatetimelocal datetime2(7),@clearingdate datetime2(7),@ispem bit,@issim bit',@stellartradeid=388716293,@tradedate='2019-03-05 16:03:28',@buysell=-1,@traderid=4787,@volume=5,@netvolume=-5.00000000,@price=158.08000000,@nettradevolume=-790.40000000,@orderid='978204193',@exchangetradeid='20190305:72060810968432684:1551767405020376916:50000:5231100:14139:5231102',@tradestatusid=7,@tradetypeid=9,@outrightid=423,@strategyid=NULL,@modifieddatetime='2019-03-05 16:03:28',@indatetime='2019-03-05 18:13:13.7111830',@tradedatetimelocal='2019-03-05 14:03:28',@clearingdate='2019-03-05 00:00:00',@ispem=0,@issim=0


Полное сообщение об ошибке: "Для выполнения этого запроса недостаточно системной памяти в пуле ресурсов "bm"."
Источник: MSSQLSERVER
Код: 701
Уровень: Ошибка
Категория задачи: Сервер

Во.
6 мар 19, 10:12    [21825945]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
step_ks,

Что может быть проще, чем 'if not exists (select 1 from ...) insert into ... else update....'?
6 мар 19, 10:25    [21825948]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
EV.P
step_ks,

Что может быть проще, чем 'if not exists (select 1 from ...) insert into ... else update....'?


1. "else update" не видать
2. trades - таблица? триггеров нет?
3. пул 'bm' - ваш? если да, то без него работает?
6 мар 19, 10:44    [21825974]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
step_ks
3. пул 'bm' - ваш? если да, то без него работает?

у товарища, похоже, Resource Governor включен
6 мар 19, 10:52    [21825983]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
step_ks,

1. Пул BM - мой. На него отведено 80% памяти через Resource Governor.
2. Resource Governor включён.
3. trades - memory optimized таблица. Триггеров нет.
Update действительно нет. if not exists нужен чтобы не вставлять дубль.
Ошибка стабильно вылетает.
Поможет ли добавление учётной записи MS SQL Server в групповую учётку по "allow lock pages in memory"?
Без Resource Governor ошибка также появляется.
6 мар 19, 11:49    [21826063]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
EV.P
step_ks,
3. trades - memory optimized таблица. Триггеров нет.

По memory optimized опыта нет.
Можно посоветовать не делать memory optimized - 12 ГБ, имхо, эт не тот объем ОЗУ, чтобы использовать inmemory.

EV.P
Поможет ли добавление учётной записи MS SQL Server в групповую учётку по "allow lock pages in memory"?

В любом случае не помешает.
6 мар 19, 11:54    [21826077]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
EV.P
Без Resource Governor ошибка также появляется.

есть текст ошибки?
6 мар 19, 12:01    [21826085]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6399
автор
Поможет ли добавление учётной записи MS SQL Server в групповую учётку по "allow lock pages in memory"?


Оно уже там. Да и не поможет, поскольку Вы лимитировали пул.
У вас может быть настолько неудачная стратегия выполнения запроса, что он требует большое количество памяти. Т.е. хочет грант, который не помещается в пул. Отдайте запросу всю память и посмотрите, сколько будет выделено и сколько использовано фактически. Потом прикрутите фитилёк выделения памяти в своём пуле на запрос до фактического +10..15%.
6 мар 19, 12:03    [21826087]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6399
step_ks,

возможно, allow lock pages in memory как раз и мешает. Мало памяти, а в своп использовать нельзя блокировкой.
6 мар 19, 12:05    [21826091]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
step_ks,

Memory optimized крайне нужен для OLAP. Сейчас делаю приложение, которое как раз использует кубы данных.
6 мар 19, 12:08    [21826095]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
komrad
EV.P
Без Resource Governor ошибка также появляется.

есть текст ошибки?


Error: 701, Severity: 17, State: 123
There is insufficient system memory in resource pool 'bm' to run this query.
6 мар 19, 12:10    [21826098]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
Владислав Колосов,

Как отдать всю память запросу? Где это лимитируется?
6 мар 19, 12:11    [21826100]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6399
Не буду врать, по-моему, выделяется до трети объема памяти, доступной серверу, на запрос.
6 мар 19, 12:19    [21826108]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
Владислав Колосов,

Нашёл информацию, что это стандартный баг сервера. Для 2008 Этот баг пофиксили в SP3. Для 2012 и более поздних - ещё нет.
Также нашёл, что memory optimized базы работают крайне ужасно (нет редактора колонок и ключей и т.д.).
6 мар 19, 12:34    [21826126]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
EV.P
komrad
пропущено...

есть текст ошибки?


Error: 701, Severity: 17, State: 123
There is insufficient system memory in resource pool 'bm' to run this query.

это ошибка при выключенном Resource Governor?
6 мар 19, 12:39    [21826136]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
Владислав Колосов
step_ks,
возможно, allow lock pages in memory как раз и мешает. Мало памяти, а в своп использовать нельзя блокировкой.

allow lock pages in memory не запрещает же своп совсем.
6 мар 19, 12:45    [21826142]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
komrad,

Нет, при включённом.
При выключенном текст ошибки:
Insufficient memory to continue the execution of the query.
Error: 701, Severity: 17, State: 103
6 мар 19, 12:47    [21826147]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
EV.P
komrad,

Нет, при включённом.
При выключенном текст ошибки:
Insufficient memory to continue the execution of the query.
Error: 701, Severity: 17, State: 103


у вас даже при всех доступных ресурсах не лезет в ин-мемори таблицу столько данных
попробуйте вставлять не все записи, а по частям - нащупаете лимит

вот тут почитайте
https://blog.sqlauthority.com/2015/01/26/sql-server-error-msg-701-level-17-state-103-there-is-insufficient-system-memory-in-resource-pool-to-run-this-query/
6 мар 19, 12:55    [21826160]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
step_ks
Member

Откуда:
Сообщений: 936
EV.P
komrad,

Нет, при включённом.
При выключенном текст ошибки:
Insufficient memory to continue the execution of the query.
Error: 701, Severity: 17, State: 103

Т.е. код тот же - 701, а текст "Insufficient memory to continue the execution of the query."?
Уверены, что это сообщение ms sql ? На SQL 2017 такого не нашлось в sys.messages.
6 мар 19, 12:59    [21826166]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 4806
step_ks
EV.P
komrad,

Нет, при включённом.
При выключенном текст ошибки:
Insufficient memory to continue the execution of the query.
Error: 701, Severity: 17, State: 103

Т.е. код тот же - 701, а текст "Insufficient memory to continue the execution of the query."?
Уверены, что это сообщение ms sql ? На SQL 2017 такого не нашлось в sys.messages.

у страдальца

Microsoft SQL Server 2019 (CTP2.2) - 15.0.1200.24 (X64) Dec 5 2018 16:51:26 Copyright (C) 2018 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: ) 
6 мар 19, 13:03    [21826173]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
komrad
step_ks
пропущено...

Т.е. код тот же - 701, а текст "Insufficient memory to continue the execution of the query."?
Уверены, что это сообщение ms sql ? На SQL 2017 такого не нашлось в sys.messages.

у страдальца

Microsoft SQL Server 2019 (CTP2.2) - 15.0.1200.24 (X64) Dec 5 2018 16:51:26 Copyright (C) 2018 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: ) 


Да, верно.

Ещё вот накопал такую инфу:
https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/

Нужно запустить sql server с параметров -q для принудительного включения режима сортировки UTF-8 для базы данных, содержащей таблицы, оптимизированные для памяти, и даже для столбцов в этих таблицах, оптимизированных для памяти.
Ничего себе! Как же так???
6 мар 19, 13:30    [21826219]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
andy st
Member

Откуда:
Сообщений: 699
Уж не задраны ли до упора BUCKET_COUNT?
6 мар 19, 13:55    [21826275]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
andy st,

При создании таблиц никаких bucket count не указывал. Каково значение по умолчанию и как его рассчитать?
6 мар 19, 13:57    [21826279]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
andy st
Member

Откуда:
Сообщений: 699
EV.P, это по поводу индексов для MEMORY_OPTIMIZED
Большие значения требуют много памяти вне зависимости от количества строк в таблице.
Если индексы отсутствуют -> продолжать копать по другим направлениям.
6 мар 19, 14:09    [21826299]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
andy st
EV.P, это по поводу индексов для MEMORY_OPTIMIZED
Большие значения требуют много памяти вне зависимости от количества строк в таблице.
Если индексы отсутствуют -> продолжать копать по другим направлениям.


Тут пишут, что размер должен быть 1 – 2X от числа уникальных значений в таблице.
Хэш-индексов у меня нет.
6 мар 19, 14:21    [21826322]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
andy st
EV.P, это по поводу индексов для MEMORY_OPTIMIZED
Большие значения требуют много памяти вне зависимости от количества строк в таблице.
Если индексы отсутствуют -> продолжать копать по другим направлениям.


Запрос
SELECT OBJECT_NAME(S.object_id) as TableName
     , I.name AS IndexName
        , S.total_bucket_count
        , S.empty_bucket_count
        , S.avg_chain_length
        , S.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats S
JOIN sys.indexes I
on S.index_id = I.index_id
   AND 
   S.object_id = I.object_id;

не выдаёт ничего. Хэш-индексов у меня нет (если только обычные некластерные). Bucket_count не указывал. Значит, проблема лежит где-то в другом месте? Или необходимо всё равно создавать хэш-индексы, поскольку интуиция говорит именно об утечке памяти при попытке выполнить запрос?
6 мар 19, 14:28    [21826333]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
andy st
Member

Откуда:
Сообщений: 699
EV.P, да тут простор для раскопок.
А сколько памяти кушают таблички?
select object_name(object_id),* from sys.dm_db_xtp_table_memory_stats

Имеет смысл исключить вероятность того, что проблема не в этой таблице сделав её обыкновенной и помониторив наличие ошибок.
И т.к. сервер - CTP, то проблема может быть из за "непоправимых улучшений" в любом компоненте сервера.
6 мар 19, 16:09    [21826477]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
andy st
EV.P, да тут простор для раскопок.
А сколько памяти кушают таблички?
select object_name(object_id),* from sys.dm_db_xtp_table_memory_stats

Имеет смысл исключить вероятность того, что проблема не в этой таблице сделав её обыкновенной и помониторив наличие ошибок.
И т.к. сервер - CTP, то проблема может быть из за "непоправимых улучшений" в любом компоненте сервера.


Кушают таблицы совсем чуть-чуть:
(Отсутствует имя столбца) object_id memory_allocated_for_table_kb memory_used_by_table_kb memory_allocated_for_indexes_kb memory_used_by_indexes_kb
tradestatuses 18099105 192 0 192 1
tradetypes 50099219 256 0 192 1
expiries 78623323 64 3 384 3
stellarpositions 750625717 1408 573 12352 286
trades 1070626857 36480 34709 20544 6062
strategies 1074102867 0 0 128 0
prices 1077578877 0 0 128 0
strategyelements 1106102981 0 0 128 0
timezones 1237579447 192 0 192 1
positions 1454628225 27712 26425 29568 9381
categories 1557580587 0 0 128 0
changetimes 1589580701 0 0 128 0
currencies 1621580815 0 0 128 0
exchanges 1653580929 128 1 192 0
outrights 1685581043 64 27 192 7
intakes 1717581157 128 1 192 2
offices 1749581271 64 0 192 0
orders 1797581442 0 0 128 0
orderstatuses 1829581556 0 0 128 0
ordertypes 1861581670 0 0 128 0
timeshifts 1938105945 64 3 192 1
positiontypes 1957582012 128 0 192 0
subscriptionlist 2005582183 0 0 128 0
products 2130106629 640 16 320 1
traders 2133582639 64 5 192 1
6 мар 19, 16:24    [21826499]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
andy st
Member

Откуда:
Сообщений: 699
EV.P,
загрузка пула
SELECT pool_id  
     , Name  
     , min_memory_percent  
     , max_memory_percent  
     , max_memory_kb/1024 AS Max_memory_MB  
     , used_memory_kb/1024 AS Used_memory_MB
     , target_memory_kb/1024 AS Target_memory_MB
   FROM sys.dm_resource_governor_resource_pools 

Смотреть в процессе работы приложения.

Читать, как работает сборщик мусора для таких таблиц. Без учета механизмов работы с памятью при использовании memory_optimized таблиц активный OLAP выжрет оперативку очень быстро. Возможно, софт активно вставляет-удаляет в одной транзакции.

Самое печальное - никаких уточнений по сценарию работы приложения, ни информации по частоте появления ошибки и доп. информации для локализации проблемы. А телепаты либо в отпуске, либо сократили.
6 мар 19, 17:34    [21826604]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
andy st,

Промониторил. Ошибка стабильно выскакивает. Запрос постоянно выдавал данные used_memory_MB для моего пула в 1422 (т.е. Мб).
Другие два пула (internal и default) также потребляли не более 1 Гб. В компьютере установлено 32 Гб. Диспетчер выдавал уровень потребления памяти постоянно не более 8 Гб. Т.е. проблема в самой бажистости MS SQL новых версий (aka memory optimized). Задумка, конечно, хорошая, но по факту это только трата ресурсов. Во.
7 мар 19, 14:10    [21827324]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
andy st
Member

Откуда:
Сообщений: 699
EV.P,
Трата оперативки - это да. В моем случае оказалось, что с пользой.
Простой перевод софта на использование memory_optimized таблиц тупо убил систему. Чтение документации и гуглёжка помогла на 100% - для некоторых отчётов ускорение получилось до 10 раз. Конечно, переписывался код процедур, менялась логика загрузки данных и формирования отчётов. А при условии, что он и до этого переписывался не раз с целью ускорения работы, то полученное ускорение большей частью можно списать на таблички в памяти.
И цели грызть внезапные кактусы на ctp не было - использовался 2016sp2. Может еще в этом проблема.
7 мар 19, 17:20    [21827595]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
andy st,

Несколько вопросов:
1. Использовали ли групповую политику "allow manage page locks in memory"?
2. Добавляли ли свой пул или пользовались байдингом со стандартным default? Если делали свой пул, то какие настройки вносили?
3. Какой уровень совместимости базы? У меня сейчас стоит SQL Server 2019 (150).
7 мар 19, 17:39    [21827612]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
EV.P
Member

Откуда:
Сообщений: 112
Также хочу спросить, добавляли ли параметр -q в стартап sql, как это настоятельно требовали рекомендации?
7 мар 19, 17:46    [21827615]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
andy st
Member

Откуда:
Сообщений: 699
EV.P,
"allow manage page locks in memory" выключено
пул default
уровень совместимости (130)
-q не используется
11 мар 19, 16:43    [21829444]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
Sql Quantum Leap
Member

Откуда:
Сообщений: 1
EV.P
Ещё вот накопал такую инфу:
https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/

Нужно запустить sql server с параметров -q для принудительного включения режима сортировки UTF-8 для базы данных, содержащей таблицы, оптимизированные для памяти, и даже для столбцов в этих таблицах, оптимизированных для памяти.
Ничего себе! Как же так???


Hello. I am the author of that post you are referring to. Sorry that I am not replying in Russian, but I do not know Russian and I am only able to read the posts here thanks to my browser translating the content :-).

I think you have misunderstood what I was saying in that post. The "-q" option is not a start up option. It is an undocumented maintenance option that changes the collation of the instance, all databases, and all columns in all user tables. That is not what should be done to fix this problem.

You found my post because it mentions a similar 701 error, but I do not believe it is the same issue. I only got that error because I forced the database with the memory optimized tables to use UTF-8. I don't believe that is the case here, since that would have required using "-q" to force UTF-8 where it is not supported, which is what caused the error. It does not fix the error.

Please do not use the "-q" option to fix this issue. It might make things worse.
15 мар 19, 22:04    [21834364]     Ответить | Цитировать Сообщить модератору
 Re: Error 701.There is insufficient system memory in resource pool'internal' to run this query  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28365
Sql Quantum Leap
You found my post because it mentions a similar 701 error, but I do not believe it is the same issue. I only got that error because I forced the database with the memory optimized tables to use UTF-8. I don't believe that is the case here, since that would have required using "-q" to force UTF-8 where it is not supported, which is what caused the error. It does not fix the error.

Please do not use the "-q" option to fix this issue. It might make things worse.
They are trying to check all versions which they saw :-)
16 мар 19, 11:50    [21834525]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3      [все]
Все форумы / Microsoft SQL Server Ответить