andrew shalaev
Member
Откуда: Moscow
Сообщений: 610
|
Дорого времени суток!
ОС: Windows Server 2012 DataCenter СУБД: Microsoft SQL Server 2012 - 11.0.5548.0 (X64) Sep 6 2014 17:19:28 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
С определенного момента сервер начал тормазить и в логе стали появляться следующие ошибки:
1. + |
A time-out occurred while waiting for buffer latch -- type 3, bp 0000028422297380, page 7:1916856, stat 0x10b, database id: 2, allocation unit Id: 6488064, task 0x000003C4822E8188 : 77, waittime 300 seconds, flags 0x3d000002039, owning task 0x0000000007893868. Not continuing to wait. A time-out occurred while waiting for buffer latch -- type 3, bp 0000028422297380, page 7:1916856, stat 0x10b, database id: 2, allocation unit Id: 6488064, task 0x000000000F8B1868 : 73, waittime 300 seconds, flags 0x3c000001039, owning task 0x0000000007893868. Not continuing to wait. A time-out occurred while waiting for buffer latch -- type 4, bp 00000281CA678AC0, page 17:145730657, stat 0x109, database id: 6, allocation unit Id: 72058208667893760, task 0x00000122B2615868 : 0, waittime 300 seconds, flags 0x1018, owning task 0x0000000000000000. Not continuing to wait. A time-out occurred while waiting for buffer latch -- type 4, bp 00000281CA678AC0, page 17:145730657, stat 0x109, database id: 6, allocation unit Id: 72058208667893760, task 0x0000019B3F87F0C8 : 0, waittime 300 seconds, flags 0x1038, owning task 0x0000000000000000. Not continuing to wait.
|
Далее 2. + |
Error: 26073, Severity: 16, State: 1. TCP connection closed but a child process of SQL Server may be holding a duplicate of the connection's socket. Consider enabling the TcpAbortiveClose SQL Server registry setting and restarting SQL Server. If the problem persists, contact Technical Support. FlushCache: cleaned up 7171 bufs with 2742 writes in 73133 ms (avoided 685 new dirty bufs) for db 8:0 average throughput: 0.77 MB/sec, I/O saturation: 285, context switches 7553 last target outstanding: 220800, avgWriteLatency 0 Using 'dbghelp.dll' version '4.0.5'
|
Далее 3.
+ |
SQL Server has encountered 5 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [DATA02.ndf] in database [] (6). The OS file handle is 0x0000000000003208. The offset of the latest long I/O is: 0x0000d796870000
SQL Server has encountered 12 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [DATA05.ndf] in database [] (6). The OS file handle is 0x0000000000003224. The offset of the latest long I/O is: 0x00000a8b340000
SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [DATA11.ndf] in database [] (6). The OS file handle is 0x0000000000003230. The offset of the latest long I/O is: 0x00007170a50000
...
|
Далее 4.
+ | There is insufficient system memory in resource pool 'default' to run this query.
|
И наконец 5.
+ |
0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 17033488 OBJECTSTORE_LOCK_MANAGER (node 7) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 6713104 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 OBJECTSTORE_LOCK_MANAGER (Total) KB ---------------------------------------- ---------- VM Reserved 8390656 VM Committed 8390656 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 115437680 OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 6) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 16 OBJECTSTORE_XACT_CACHE (node 0) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 4515568 OBJECTSTORE_XACT_CACHE (node 1) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 3742568 OBJECTSTORE_XACT_CACHE (node 2) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 3257368 OBJECTSTORE_XACT_CACHE (node 3) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 3484080 OBJECTSTORE_XACT_CACHE (node 4) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 4289752 OBJECTSTORE_XACT_CACHE (node 5) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 3281536 OBJECTSTORE_XACT_CACHE (node 6) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 4081816 OBJECTSTORE_XACT_CACHE (node 7) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 3678616 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 OBJECTSTORE_XACT_CACHE (Total) KB ---------------------------------------- ---------- VM Reserved 0 VM Committed 0 Locked Pages Allocated 0 SM Reserved 0 SM Committed 0 Pages Allocated 30331320 Buffer Pool Value ---------------------------------------- ---------- Database 170587106 Simulated 42274891 Target 1047904256 Dirty 705868 In IO 56 Latched 225 Page Life Expectancy 306 Procedure Cache Value ---------------------------------------- ---------- TotalProcs 17242 TotalPages 353846 InUsePages 44444 Global Memory Objects Pages ---------------------------------------- ---------- Resource 22829 Locks 14429722 XDES 17366 DirtyPageTracking 6044 SETLS 161 SubpDesc Allocators 359 SE SchemaManager 56545 SE Column Metadata Cache 26634 SE Column Metadata Cache Store 18 SQLCache 13373 Replication 2 ServerGlobal 187 XP Global 2 SortTables 3 Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 3 Waiting 0 Available 252738413 Current Max 252795488 Future Max 252795488 Physical Max 252795488 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 1 Waiting 0 Available 704356 Current Max 704512 Future Max 704512 Remote Query Memory Objects (internal) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 126397744 Current Max 126397744 Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 13 Waiting 0 Available 244658259 Current Max 252795488 Future Max 252795488 Physical Max 252795488 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 9 Waiting 0 Available 702870 Current Max 704512 Future Max 704512 Remote Query Memory Objects (default) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 126397744 Current Max 126397744 Query Memory Objects (reserved) Value ---------------------------------------- ---------- Grants 34 Waiting 0 Available 244712171 Current Max 252795488 Future Max 252795488 Physical Max 252795488 Next Request 0 Waiting For 0 Cost 0 Timeout 0 Wait Time 0 Small Query Memory Objects (reserved) Value ---------------------------------------- ---------- Grants 20 Waiting 0 Available 700214 Current Max 704512 Future Max 704512 Remote Query Memory Objects (reserved) Value ---------------------------------------- ---------- Grants 0 Waiting 0 Available 126397744 Current Max 126397744 Optimization Queue (internal) Value ---------------------------------------- ---------- Overall Memory 2215116800000 Target Memory 2215116800000 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway (internal) Value ---------------------------------------- ---------- Configured Units 640 Available Units 640 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (internal) Value ---------------------------------------- ---------- Configured Units 160 Available Units 160 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (internal) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Optimization Queue (default) Value ---------------------------------------- ---------- Overall Memory 2215116800000 Target Memory 2215116800000 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway (default) Value ---------------------------------------- ---------- Configured Units 640 Available Units 639 Acquires 1 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (default) Value ---------------------------------------- ---------- Configured Units 160 Available Units 160 Acquires 0 Waiters 0 Threshold Factor 12 Threshold 184593066666 Big Gateway (default) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Optimization Queue (reserved) Value ---------------------------------------- ---------- Overall Memory 2215116800000 Target Memory 2215116800000 Last Notification 1 Timeout 6 Early Termination Factor 5 Small Gateway (reserved) Value ---------------------------------------- ---------- Configured Units 640 Available Units 640 Acquires 0 Waiters 0 Threshold Factor 380000 Threshold 380000 Medium Gateway (reserved) Value ---------------------------------------- ---------- Configured Units 160 Available Units 160 Acquires 0 Waiters 0 Threshold Factor 12 Threshold -1 Big Gateway (reserved) Value ---------------------------------------- ---------- Configured Units 1 Available Units 1 Acquires 0 Waiters 0 Threshold Factor 8 Threshold -1 Memory Pool Manager Pages ---------------------------------------- ---------- Reserved Current 16283717 Reserved Limit 290878719 Memory Pool (internal) Pages ---------------------------------------- ---------- Allocations 23630873 Predicted 23630929 Private Target 0 Private Limit 0 Total Target 332800001 Total Limit 332800001 OOM Count 0 MEMORYBROKER_FOR_CACHE (internal) Pages ---------------------------------------- ---------- Allocations 802410 Rate 1 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_STEAL (internal) Pages ---------------------------------------- ---------- Allocations 41712 Rate -85 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_RESERVE (internal) Pages ---------------------------------------- ---------- Allocations 57231 Rate 48 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_COMMITTED (internal) Pages ---------------------------------------- ---------- Allocations 22729518 Rate 7 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_XTP (internal) Pages ---------------------------------------- ---------- Allocations 2 Rate 0 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 Memory Pool (default) Pages ---------------------------------------- ---------- Allocations 21806260 Predicted 21806433 Private Target 0 Private Limit 0 Total Target 332800001 Total Limit 332800001 OOM Count 0 MEMORYBROKER_FOR_CACHE (default) Pages ---------------------------------------- ---------- Allocations 13487272 Rate 173 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_STEAL (default) Pages ---------------------------------------- ---------- Allocations 180117 Rate -116 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_RESERVE (default) Pages ---------------------------------------- ---------- Allocations 8138871 Rate -10206 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_XTP (default) Pages ---------------------------------------- ---------- Allocations 0 Rate 0 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 Memory Pool (reserved) Pages ---------------------------------------- ---------- Allocations 12691155 Predicted 12879603 Private Target 0 Private Limit 0 Total Target 332800001 Total Limit 332800001 OOM Count 0 MEMORYBROKER_FOR_CACHE (reserved) Pages ---------------------------------------- ---------- Allocations 4490978 Rate -46 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_STEAL (reserved) Pages ---------------------------------------- ---------- Allocations 189273 Rate 1708 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_RESERVE (reserved) Pages ---------------------------------------- ---------- Allocations 8010904 Rate 186740 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 MEMORYBROKER_FOR_XTP (reserved) Pages ---------------------------------------- ---------- Allocations 0 Rate 0 Target Allocations 270400000 Future Allocations 0 Overall 270400000 Last Notification 1 Memory Broker Clerk (Buffer Pool) Pages ---------------------------------------- ---------- Total 170587145 Simulated 42274879 Simulation Benefit 0.0000000043 Internal Benefit 0 External Benefit 0 Value Of Memory 0 Periodic Freed 0 Internal Freed 0
|
В момент тормазов на сервере я запускал:
SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null
и он мне ничего не выводил, т.е. всем запросам выделялась память.
Если я правильно понимаю у сервера стало резко не хватать памяти и он стал свопить на диск. Если так, то можно ли сказать куда память делась?
|