October 22, 2014 at 2:37 am
Hi,
I have a SQL 2014 installed on Windows 2012 R2 Standard edition.
"Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
Aug 13 2014 11:36:34
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)"
Its running on a VM (VM Ware), with 8 processors assigned to the VM and 16GB of memory.
The account is set to Lock Pages in memory.
SQL Configuration:
cost threshold for parallelism 50
max degree of parallelism4
min server memory (MB)1024
max server memory (MB)12288
affinity64 mask0
affinity I/O mask0
affinity64 I/O mask0
optimize for ad hoc workloads 0
NOTE:
I've changed the optimize for ad hoc workload this morning to see if it will make a difference or not.
Also I've set the Max server memory to 10GB as well before, but changed it back to 12GB.
The workload type is basically huge extracts that we need to run for the actuarial department. So not a OLTP environment. At this stage it's only one person using the server. Running single queries at a time.
Output from DBCC MemoryStatus:
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 10371735552
Available Virtual Memory 140704786894848
Available Paging File 53057978368
Working Set 125255680
Percent of Committed Memory in WS 100
Page Faults 1758936
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
(10 row(s) affected)
Memory Manager KB
---------------------------------------- -----------
VM Reserved 31497152
VM Committed 153672
Locked Pages Allocated 5027632
Large Pages Allocated 83968
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 12582912
Current Committed 5181304
Pages Allocated 2271056
Pages Reserved 0
Pages Free 2698144
Pages In Use 319192
Page Alloc Potential 11634576
NUMA Growth Phase 0
Last OOM Factor 1
Last OS Error 0
(16 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 31497088
VM Committed 153652
Locked Pages Allocated 5027632
Pages Allocated 2271056
Pages Free 2698144
Target Committed 12582888
Current Committed 5181288
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
(10 row(s) affected)
Memory node Id = 64 KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
(3 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 6912
(6 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 12460608
VM Committed 16384
Locked Pages Allocated 52440
SM Reserved 0
SM Committed 0
Pages Allocated 2164096
(6 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 88
(6 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1008
(6 row(s) affected)
MEMORYCLERK_QUERYDISKSTORE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 480
(6 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
(6 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 1088
VM Committed 0
Locked Pages Allocated 1088
SM Reserved 0
SM Committed 0
Pages Allocated 16392
(6 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1456
(6 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 6300544
VM Committed 6752
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 20728
(6 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 528
(6 row(s) affected)
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 64
(6 row(s) affected)
MEMORYCLERK_SNI (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 80
(6 row(s) affected)
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 296
(6 row(s) affected)
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 176
(6 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- -----------
VM Reserved 21504
VM Committed 21504
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_XTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1672
(6 row(s) affected)
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32536
(6 row(s) affected)
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2488
(6 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 35024
(6 row(s) affected)
MEMORYCLERK_SOSOS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
(6 row(s) affected)
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 44992
VM Committed 39936
Locked Pages Allocated 4904
SM Reserved 0
SM Committed 0
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 192
SM Committed 192
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 200
(6 row(s) affected)
MEMORYCLERK_FSAGENT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
MEMORYCLERK_FILETABLE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 5376
(6 row(s) affected)
MEMORYCLERK_SQLLOGPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2144
(6 row(s) affected)
MEMORYCLERK_LWC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 64
(6 row(s) affected)
MEMORYCLERK_FSCHUNKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48
(6 row(s) affected)
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1344
(6 row(s) affected)
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3240
(6 row(s) affected)
CACHESTORE_PHDR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1640
(6 row(s) affected)
CACHESTORE_XPROC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
(6 row(s) affected)
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_STACKFRAMES (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 40
(6 row(s) affected)
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
(6 row(s) affected)
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
CACHESTORE_SEHOBTCOLUMNATTRIBUTE (node 0) KB
----------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 168
(6 row(s) affected)
CACHESTORE_SYSTEMROWSET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 640
(6 row(s) affected)
CACHESTORE_CONVPRI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
(6 row(s) affected)
CACHESTORE_FULLTEXTSTOPLIST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
(6 row(s) affected)
CACHESTORE_SEARCHPROPERTYLIST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
CACHESTORE_COLUMNSTOREOBJECTPOOL (node 0) KB
----------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 64
(6 row(s) affected)
CACHESTORE_XML_SELECTIVE_DG (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4360
(6 row(s) affected)
USERSTORE_DBMETADATA (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 432
(6 row(s) affected)
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1472
(6 row(s) affected)
USERSTORE_OBJPERM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 792
(6 row(s) affected)
USERSTORE_SXC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
(6 row(s) affected)
USERSTORE_SXC (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 40
(6 row(s) affected)
USERSTORE_QDSSTMT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
(6 row(s) affected)
OBJECTSTORE_LBSS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 224
(6 row(s) affected)
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 952
(6 row(s) affected)
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
(6 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1008
(6 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 544
(6 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 32768
VM Committed 32768
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3584
(6 row(s) affected)
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
(6 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- -----------
VM Reserved 32768
VM Committed 32768
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3608
(6 row(s) affected)
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
(6 row(s) affected)
OBJECTSTORE_XACT_CACHE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 152
(6 row(s) affected)
Buffer Pool Value
---------------------------------------- -----------
Database 270488
Simulated 18642
Target 32751616
Dirty 673
In IO 0
Latched 0
Page Life Expectancy 48343
(7 row(s) affected)
Procedure Cache Value
---------------------------------------- -----------
TotalProcs 43
TotalPages 781
InUsePages 0
(3 row(s) affected)
Global Memory Objects Pages
---------------------------------------- -----------
Resource 410
Locks 451
XDES 65
DirtyPageTracking 8
SETLS 8
SubpDesc Allocators 8
SE SchemaManager 250
SE Column Metadata Cache 294
SE Column Metadata Cache Store 7
SQLCache 213
Replication 2
ServerGlobal 52
XP Global 2
SortTables 3
(14 row(s) affected)
Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 1045252
Current Max 1045252
Future Max 1045252
Physical Max 1126927
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 55013
Current Max 55013
Future Max 55013
(5 row(s) affected)
Remote Query Memory Objects (internal) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 563463
Current Max 563463
(4 row(s) affected)
Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 1122843
Current Max 1122843
Future Max 1122843
Physical Max 1122843
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
(11 row(s) affected)
Small Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 59097
Current Max 59097
Future Max 59097
(5 row(s) affected)
Remote Query Memory Objects (default) Value
---------------------------------------- -----------
Grants 0
Waiting 0
Available 561421
Current Max 561421
(4 row(s) affected)
Optimization Queue (internal) Value
---------------------------------------- --------------------
Overall Memory 10328047616
Target Memory 9697820672
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (internal) Value
---------------------------------------- -----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 row(s) affected)
Medium Gateway (internal) Value
---------------------------------------- -----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
(6 row(s) affected)
Big Gateway (internal) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Optimization Queue (default) Value
---------------------------------------- --------------------
Overall Memory 10328047616
Target Memory 9615556608
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway (default) Value
---------------------------------------- -----------
Configured Units 32
Available Units 32
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
(6 row(s) affected)
Medium Gateway (default) Value
---------------------------------------- -----------
Configured Units 8
Available Units 8
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
(6 row(s) affected)
Big Gateway (default) Value
---------------------------------------- -----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
(6 row(s) affected)
Memory Pool Manager KB
---------------------------------------- -----------
Reserved Current 0
Reserved Limit 11634688
(2 row(s) affected)
Memory Pool (internal) KB
---------------------------------------- -----------
Allocations 305368
Predicted 305368
Private Target 0
Private Limit 0
Total Target 11953760
Total Limit 11953760
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (internal) KB
---------------------------------------- -----------
Allocations 10072
Rate 0
Target Allocations 9399080
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (internal) KB
---------------------------------------- -----------
Allocations 81520
Rate 0
Target Allocations 9470528
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (internal) KB
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 9389008
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_COMMITTED (internal) KB
---------------------------------------- -----------
Allocations 212104
Rate 0
Target Allocations 9601112
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_HASHED_DATA_PAGES (internal) KB
--------------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 9389008
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_XTP (internal) KB
---------------------------------------- -----------
Allocations 1672
Rate 0
Target Allocations 9390680
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
Memory Pool (default) KB
---------------------------------------- -----------
Allocations 13704
Predicted 2259384
Private Target 0
Private Limit 0
Total Target 11953760
Total Limit 11953760
OOM Count 0
(7 row(s) affected)
MEMORYBROKER_FOR_CACHE (default) KB
---------------------------------------- -----------
Allocations 12520
Rate 0
Target Allocations 9401528
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_STEAL (default) KB
---------------------------------------- -----------
Allocations 1184
Rate 0
Target Allocations 9390192
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_RESERVE (default) KB
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 10085984
Future Allocations 2245680
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_HASHED_DATA_PAGES (default) KB
-------------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 9389008
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
MEMORYBROKER_FOR_XTP (default) KB
---------------------------------------- -----------
Allocations 0
Rate 0
Target Allocations 9389008
Future Allocations 0
Overall 10085984
Last Notification 1
(6 row(s) affected)
Memory Broker Clerk (Buffer Pool) Pages
---------------------------------------- ----------------------
Total 270488
Simulated 18642
Simulation Benefit 0
Internal Benefit 0
External Benefit 0
Value Of Memory 0
Periodic Freed 0
Internal Freed 0
(8 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now the problems:
The initial run on the extracts is running within reasonable time. But after that the server is crawling. Until the server is bounced - which was only done as a test. This is on different queries, so its not a single query that we can point out causing the issues.
The second problem is we get the error:
There is insufficient system memory in resource pool 'default' to run this query.
I even get the above error when I just try to get the Execution plan of a rather big query. The query will run for 1-2 hours trying to build the execution plan, then fail with the above error. - Note, I'm not running the query - simply trying to get the execution plan. This particular query will give me the execution plan within a second on the 2008 server.
The third problem:
When doing Selects on some queries is damn fast - until we do a SELECT INTO, then the query turns from a sub second query to multiple minute queries. (Result sets from 10k+)
We want to move from SQL 2008 R2 on which these queries all run fine without any issues. (The SQL 2008 only have 5GB of memory assigned to SQL & 3 GB to windows).
I know that without queries it's a bit difficult to trouble shoot, but because the problem is over multiple queries (most of the queries) it will be a bit difficult to build test cases for each one. And I think this is more an environment issue than a query issue.
Ok what have tried:
I know some of these are not best practice to do... 🙁
1. Reduced the max memory to at least 10GB. (now back to 12GB)
2. I initially thought that the issues was with parallelism, as the wait stats was showing that CXPacket was quite high & the wait times on the queries was also showing 100% to CPU. so I've changed the default setting for cost threshold for parallelism to 50 and also the max degree of parallelism to 4. This didn't really improved the performance much.
3. Made sure that the Statistics are Updated.
4. The database compatibility level of the DB = 120. (I've tried it as 100 & 110)
5. Ran DBCC FREEPROCCACHE (which didn't help either) & DBCC FREESYSTEMCACHE ('all'). (Just to see if I can generate a new execution plan within a reasonable time).
6. I've checked the % Pagefile usage - this is about 1% peaking at 1.5%.
I've read the following articles trying to resolve this:
http://voluntarydba.com/post/2013/03/19/Is-my-SQL-Servers-memory-over-committed.aspx
And some other post that I don't have the links for anymore, but basically boils down to the same as these two.
Any help to get this resolved this will be appreciated.
October 22, 2014 at 3:18 am
SQL Server 2014 has a new cardinality estimator that could lead to totally different execution plans. What compatibility level is the database?
If you have good enough performance from the 2008R2 box and want to keep it consistent in the new box, I would use the old cardinality estimator, which means you should keep your database compatibility level to at most 2012 (110).
-- Gianluca Sartori
October 22, 2014 at 3:28 am
spaghettidba (10/22/2014)
SQL Server 2014 has a new cardinality estimator that could lead to totally different execution plans. What compatibility level is the database?If you have good enough performance from the 2008R2 box and want to keep it consistent in the new box, I would use the old cardinality estimator, which means you should keep your database compatibility level to at most 2012 (110).
Hi,
I've tried this as well... (See point 4 in my post of what I've tried from my post.)
I thought it might be that the cardinality estimator is playing around with me - tested it and same results.
But thanks... 🙂
October 22, 2014 at 3:31 am
Wait - tried it again to set the Compatibility level back to 110.
Now the out of memory issue error is gone....
Will double check the performance after a couple of extracts was run.
October 22, 2014 at 3:35 am
What do wait stats look like compared to the ones you had on the old server?
-- Gianluca Sartori
October 22, 2014 at 5:07 am
Wait stats on the new Server:
wait_type wait_time_s pct running_pct
--------------------------- --------------------------------------- ------------ ------------
SP_SERVER_DIAGNOSTICS_SLEEP 64199.91 18.27 91.40
CXPACKET 14712.68 4.19 91.40
PAGELATCH_SH 5982.82 1.70 91.40
PAGELATCH_EX 4001.93 1.14 91.40
THREADPOOL 2374.19 0.68 91.40
SOS_SCHEDULER_YIELD 1664.25 0.47 91.40
I will have to re-generate the wait stats for the SQL 2008 one again. Just want to run some of the extracts first.
October 22, 2014 at 5:48 am
My first thoughts were around parameter sniffing and issues there, especially if you're seeing changes in behavior based on changing the cardinality estimator (changing the compatibility level). But, I'm also stuck on the Lock Pages in Memory. Jonathan Kehayias, one of my go-to people for system configurations, suggests not turning it on in a virtualized environment[/url], such as you're dealing with here. That's backed up by what Brent Ozar (another person I trust) suggests. So, you might want to start there as part of the experiment.
I'd probably leave the compatibility level at 120 (I love the new cardinality estimator), but maybe you should try clearing the procedure cache at the end of your load process so that the plans get recompiled after the load is done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2014 at 7:15 am
As far as the new CE is concerned, Kimberly Tripp blogged some days ago about options to adopt the new CE: http://www.sqlskills.com/blogs/kimberly/sqlskills-procs-analyze-data-skew-create-filtered-statistics/
Before migrating to the new server, I would bechmark under different conditions (CE 7, CE 12) and decide whether upgrading compatibility level or not.
-- Gianluca Sartori
October 22, 2014 at 7:21 am
Grant Fritchey (10/22/2014)
My first thoughts were around parameter sniffing and issues there, especially if you're seeing changes in behavior based on changing the cardinality estimator (changing the compatibility level). But, I'm also stuck on the Lock Pages in Memory. Jonathan Kehayias, one of my go-to people for system configurations, suggests not turning it on in a virtualized environment[/url], such as you're dealing with here. That's backed up by what Brent Ozar (another person I trust) suggests. So, you might want to start there as part of the experiment.I'd probably leave the compatibility level at 120 (I love the new cardinality estimator), but maybe you should try clearing the procedure cache at the end of your load process so that the plans get recompiled after the load is done.
Hi,
With the compatibility level at 120 we get the out of memory issues. Even just to get the estimated query plan fails with the out of memory error (after is running for an hour and a half).
We've tried to clear the procedure cache - no difference.
I would have loved to use the DB on 120 compatibility mode. (Especially for the parallel select into operations etc).
As soon as I change the compatibility mode to 110, the plan generates within seconds.
So for now our out of memory issue is sorted. (although not in a way I would have loved it to be sorted)
The issue with the server getting slower with each run, is still on the table.
Which is probably something to do with LPIM.
I've asked the VM admins - they say that none of our VMs ever uses the balloon drivers - the servers have enough capacity available. But I will experiment a bit with this...
If we don't set the LPIM - I ques it will be best to set the Min Memory option then? So that whenever the balloon driver of the VM host kicks in, it won't take too much memory from the SQL VM?
So for now - remove LPIM, set MAX_Server_Memory to 12GB (leaving 4 to OS) and set Min_server_memory to something like 10GB?
October 22, 2014 at 9:21 am
That's really odd that the 120 compatibility level would do this. Have you applied any of the CUs?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 22, 2014 at 12:06 pm
You might also consider trying it with compat level 120, but running the query with OPTION (QUERYTRACEON 9481) which forces it (even in 120 level) to use the old CE. I would assume you'd see the same behavior but that at least would say, yes, definitely its the CE and not something else with the compatibility levels.
October 22, 2014 at 1:16 pm
Grant Fritchey (10/22/2014)
That's really odd that the 120 compatibility level would do this. Have you applied any of the CUs?
Hi,
Yip - I've applied CU3.
I will test again later this week. Will set the compatibility back to 120, and check if we get the same results.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply