March 15, 2013 at 11:55 am
I have a server which I'm trying to setup identically to our running production server and I'm running into issues. The hardware is identical, as follows:
2x 8-Core Opteron 6212
32gb RAM
670GB of Disk Space split 60 C: / 610 E:
OS is Windows Server 2008 R2 Enterprise
SQL Server is 2012 SP1 with rollup (11.0.3128)
The server has four instances installed, across which we're trying to split resources by setting Max RAM and Process/IO Affinity, like this:
\NCR - 8GB, Process on Cores 1,2,3, I/O on Core 4
\GTA - 8GB, Process on Cores 5,6,7, I/O on Core 8
\ENT - 8GB, Process on Cores 9,10,11, I/O on Core 12
\DOCINDEX - 4GB, Process on Core 13, I/O on Core 14
Leaving 4GB, and Cores 0 and 15 free for the OS.
The production server is setup like this and running fine. The development server is fine right up until I change the Process and IO affinity. As soon as I do that and restart the SQL Server Services or the whole box, GTA and DOCINDEX's services will start, then immediately stop.
Since I can't get them started, I end up uninstalling and reinstalling the instance. Then they run fine with no process or IO affinity set. I can change the Max RAM, but if I change the Process or IO affinity again they do the same thing.
I've tried stopping all the SQL services and just turning on DOCINDEX or just turning on GTA, thinking that it might be a problem with resource availability, but they still won't start.
I've even gone as far as reformatting the entire box again from scratch, making sure I follow the procedure I wrote up when I built the production box and run into the same issue.
The system event logs have no helpful information at all. The only event related to the services in question are a bunch of "This is informational" events describing the startup, then one saying "The service has entered the stopped state".
The ERRORLOG has the same lack of information. I can post both if needed.
Help!
Jon
March 15, 2013 at 12:12 pm
Just to add some information/confirmation:
I started the DOCINDEX instance in minimal configuration mode using the following command:
"C:\Program Files\Microsoft SQL Server\MSSQL11.DOCINDEX\MSSQL\Binn\sqlserver.exe" -sDOCINDEX -f
Then I connected to the database and ran the following:
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'affinity mask', 0x00000000
GO
RECONFIGURE
GO
Which resulted in the following output:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'affinity mask' changed from 8192 to 0. Run the RECONFIGURE statement to install.
Then I stopped the server and restarted it from services.msc . It started and ran with no issues and the process and IO affinity were both set back to automatic.
I changed them back to Process on CPU 13 and IO on CPU 14. I went into services.msc, stopped the service, and upon restart it did the same "The service started then stopped" thing.
So it's DEFINITELY the affinity that's doing this, I just wish I knew why. At least now I have a way to get things working again without having to uninstall and reinstall the whole instance but it's still annoying.
March 15, 2013 at 12:43 pm
Are those two instances (the ones failing) SQL Express by any chance?
btw, generally configuring the cores as you tried is not usually a good idea. Those are fairly advanced settings that usually should be left at default unless there's a really, really good reason. If you want to balance the CPU load of the instances, the windows resource manager's a better choice
http://technet.microsoft.com/en-us/library/cc755056.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2013 at 12:48 pm
All four instances are SQL Server 2012 Enterprise
We've configured the affinity like that because these instances used to be completely separate (but much less powerful) servers, and when we negotiated the purchase of the larger servers with management we had to guarantee that any one instance would not be able to affect the performance of any other instance by encroaching on its resources.
In other words, if someone does something crazy in NCR, it should affect only NCR. It should not be able to max out more than its 3 CPUs or eat up more than its 8gb of RAM.
Is there another way to limit which cores a given instance will use?
Jon
March 15, 2013 at 12:54 pm
GilaMonster (3/15/2013)
If you want to balance the CPU load of the instances, the windows resource manager's a better choice
Firstly you can do more granular limits, second, because if the other instances are doing nothing at all, why shouldn't the one get most of the CPU, it's not preventing the others from working, they're idle. If all 4 are working heavily, then the limits kick in.
Can you post the error log of one of the times the startup failed? The entire error log please.
Also, 4GB for the OS might be a little on the low side for a 32 GB server. Monitor, make sure that the available memory (perfmon counter) doesn't drop below 600MB-1GB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2013 at 1:02 pm
Okay, I will read up on the Windows System Resource Manager and start the ball rolling to figure out how that works and get it into testing so that we can eventually roll to our production environment.
Until that time, I would still really like to find out why I'm able to run this configuration on one server and not the other. Can you offer any assistance, despite the fact that you don't recommend this configuration?
March 15, 2013 at 1:04 pm
Sure, I'll get the Errorlog up shortly
March 15, 2013 at 1:05 pm
cryovenom (3/15/2013)
Can you offer any assistance, despite the fact that you don't recommend this configuration?
If you post the error log that I asked for, maybe.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2013 at 1:11 pm
Here is the ERRORLOG for DOCINDEX
---
2013-03-15 15:07:27.05 Server Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
2013-03-15 15:07:27.05 Server (c) Microsoft Corporation.
2013-03-15 15:07:27.05 Server All rights reserved.
2013-03-15 15:07:27.05 Server Server process ID is 2084.
2013-03-15 15:07:27.05 Server System Manufacturer: 'Supermicro', System Model: 'H8DGG'.
2013-03-15 15:07:27.05 Server Authentication mode is MIXED.
2013-03-15 15:07:27.06 Server Logging SQL Server messages in file 'E:\SQLData\DOCINDEX\MSSQL11.DOCINDEX\MSSQL\Log\ERRORLOG'.
2013-03-15 15:07:27.06 Server The service account is 'STAFFNET\sqladmin'. This is an informational message; no user action is required.
2013-03-15 15:07:27.06 Server Registry startup parameters:
-d E:\SQLData\DOCINDEX\MSSQL11.DOCINDEX\MSSQL\DATA\master.mdf
-e E:\SQLData\DOCINDEX\MSSQL11.DOCINDEX\MSSQL\Log\ERRORLOG
-l E:\SQLData\DOCINDEX\MSSQL11.DOCINDEX\MSSQL\DATA\mastlog.ldf
2013-03-15 15:07:27.06 Server Command Line Startup Parameters:
-s "DOCINDEX"
2013-03-15 15:07:27.40 Server SQL Server detected 2 sockets with 4 cores per socket and 8 logical processors per socket, 16 total logical processors; using 16 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2013-03-15 15:07:27.40 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2013-03-15 15:07:27.40 Server Detected 32766 MB of RAM. This is an informational message; no user action is required.
2013-03-15 15:07:27.40 Server Using locked pages in the memory manager.
2013-03-15 15:07:27.42 Server Large Page Allocated: 32MB
2013-03-15 15:07:27.43 Server Large Page Allocated: 32MB
2013-03-15 15:07:27.45 Server Large Page Allocated: 32MB
2013-03-15 15:07:27.48 Server Large Page Allocated: 32MB
2013-03-15 15:07:27.97 Server Processor affinity turned on: node 0, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-03-15 15:07:27.97 Server Processor affinity turned on: node 1, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-03-15 15:07:27.97 Server Processor affinity turned on: node 2, processor mask 0x0000000000000000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-03-15 15:07:27.97 Server Processor affinity turned on: node 3, processor mask 0x0000000000002000. Threads will execute on CPUs per affinity settings. This is an informational message; no user action is required.
2013-03-15 15:07:27.97 Server I/O affinity turned on, processor mask 0x0000000000004000. Disk I/Os will execute on CPUs per affinity I/O mask/affinity64 mask config option. This is an informational message only; no user action is required.
2013-03-15 15:07:28.07 Server This instance of SQL Server last reported using a process ID of 1412 at 3/15/2013 3:07:21 PM (local) 3/15/2013 7:07:21 PM (UTC). This is an informational message only; no user action is required.
2013-03-15 15:07:28.07 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-03-15 15:07:28.07 Server Node configuration: node 1: CPU mask: 0x00000000000000f0:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-03-15 15:07:28.07 Server Node configuration: node 2: CPU mask: 0x0000000000000f00:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-03-15 15:07:28.07 Server Node configuration: node 3: CPU mask: 0x000000000000f000:0 Active CPU mask: 0x0000000000002000:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2013-03-15 15:07:28.09 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2013-03-15 15:07:28.09 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2013-03-15 15:10:15.10 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2013-03-15 15:10:15.10 Server
Process/System Counts Value
---------------------------------------- ----------
Available Physical Memory 21823102976
Available Virtual Memory 8747790282752
Available Paging File 37028200448
Working Set 131485696
Percent of Committed Memory in WS 100
Page Faults 44643
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
2013-03-15 15:10:15.10 Server
Memory Manager KB
---------------------------------------- ----------
VM Reserved 46859968
VM Committed 322916
Locked Pages Allocated 3902916
Large Pages Allocated 215040
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 4194304
Current Committed 4225832
Pages Allocated 40056
Pages Reserved 0
Pages Free 3830912
Pages In Use 394792
Page Alloc Potential 3589800
NUMA Growth Phase 1
Last OOM Factor 3
Last OS Error 2
2013-03-15 15:10:15.10 Server
Memory node Id = 0 KB
---------------------------------------- ----------
VM Reserved 46761536
VM Committed 224532
Locked Pages Allocated 68876
Pages Allocated 36928
Pages Free 0
Target Committed 293384
Current Committed 293408
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
2013-03-15 15:10:15.10 Server
Memory node Id = 1 KB
---------------------------------------- ----------
VM Reserved 32768
VM Committed 32788
Locked Pages Allocated 1040
Pages Allocated 1040
Pages Free 0
Target Committed 33832
Current Committed 33832
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
2013-03-15 15:10:15.10 Server
Memory node Id = 2 KB
---------------------------------------- ----------
VM Reserved 32768
VM Committed 32788
Locked Pages Allocated 1040
Pages Allocated 1040
Pages Free 0
Target Committed 33832
Current Committed 33832
Foreign Committed 0
Away Committed 3830912
Taken Away Committed 0
2013-03-15 15:10:15.10 Server
Memory node Id = 3 KB
---------------------------------------- ----------
VM Reserved 32768
VM Committed 32788
Locked Pages Allocated 3831960
Pages Allocated 1048
Pages Free 3830912
Target Committed 3833232
Current Committed 3864752
Foreign Committed 8
Away Committed 0
Taken Away Committed 3830912
2013-03-15 15:10:15.10 Server
Memory node Id = 64 KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1088
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 12999744
VM Committed 65536
Locked Pages Allocated 132
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 152
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1008
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 536
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 288
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SNI (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SNI (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SNI (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SNI (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SNI (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 80
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
MEMORYCLERK_XTP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
MEMORYCLERK_HOST (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 17968
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3352
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSNODE (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3352
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSNODE (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3544
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2424
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 30640
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSOS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 200
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 138496
VM Committed 106496
Locked Pages Allocated 31816
SM Reserved 0
SM Committed 0
Pages Allocated 0
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
2013-03-15 15:10:15.10 Server
MEMORYCLERK_XE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 376
2013-03-15 15:10:15.10 Server
MEMORYCLERK_SQLLOGPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2072
2013-03-15 15:10:15.10 Server
CACHESTORE_OBJCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_SQLCP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1704
2013-03-15 15:10:15.10 Server
CACHESTORE_PHDR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_XPROC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
CACHESTORE_TEMPTABLES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
CACHESTORE_NOTIF (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
CACHESTORE_VIEWDEFINITIONS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
CACHESTORE_XMLDBTYPE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_XMLDBELEMENT (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_XMLDBATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_STACKFRAMES (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_STACKFRAMES (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_STACKFRAMES (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_STACKFRAMES (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_STACKFRAMES (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 40
2013-03-15 15:10:15.10 Server
CACHESTORE_BROKERTBLACS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
CACHESTORE_BROKERKEK (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_BROKERDSH (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_BROKERUSERCERTLOOKUP (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_BROKERRSB (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_BROKERREADONLY (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 32
2013-03-15 15:10:15.10 Server
CACHESTORE_BROKERTO (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
CACHESTORE_EVENTS (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
CACHESTORE_SEHOBTCOLUMNATTRIBUTE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
2013-03-15 15:10:15.10 Server
CACHESTORE_COLUMNSTOREOBJECTPOOL (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
2013-03-15 15:10:15.10 Server
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
2013-03-15 15:10:15.10 Server
USERSTORE_SCHEMAMGR (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 152
2013-03-15 15:10:15.10 Server
USERSTORE_TOKENPERM (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 168
2013-03-15 15:10:15.10 Server
USERSTORE_SXC (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
USERSTORE_SXC (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
USERSTORE_SXC (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
USERSTORE_SXC (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
USERSTORE_SXC (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
2013-03-15 15:10:15.10 Server
USERSTORE_SXC (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 40
2013-03-15 15:10:15.10 Server
OBJECTSTORE_SNI_PACKET (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2013-03-15 15:10:15.10 Server
OBJECTSTORE_SNI_PACKET (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2013-03-15 15:10:15.10 Server
OBJECTSTORE_SNI_PACKET (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2013-03-15 15:10:15.10 Server
OBJECTSTORE_SNI_PACKET (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 56
2013-03-15 15:10:15.10 Server
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
2013-03-15 15:10:15.10 Server
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 280
2013-03-15 15:10:15.10 Server
OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 368
2013-03-15 15:10:15.10 Server
OBJECTSTORE_LOCK_MANAGER (node 0) KB
---------------------------------------- ----------
VM Reserved 16384
VM Committed 16384
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 648
2013-03-15 15:10:15.10 Server
OBJECTSTORE_LOCK_MANAGER (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 648
2013-03-15 15:10:15.10 Server
OBJECTSTORE_LOCK_MANAGER (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 648
2013-03-15 15:10:15.10 Server
OBJECTSTORE_LOCK_MANAGER (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 648
2013-03-15 15:10:15.10 Server
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
2013-03-15 15:10:15.10 Server
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 16384
VM Committed 16384
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2616
2013-03-15 15:10:15.10 Server
OBJECTSTORE_XACT_CACHE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
OBJECTSTORE_XACT_CACHE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
OBJECTSTORE_XACT_CACHE (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
OBJECTSTORE_XACT_CACHE (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
2013-03-15 15:10:15.10 Server
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
2013-03-15 15:10:15.10 Server
OBJECTSTORE_XACT_CACHE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 80
2013-03-15 15:10:15.10 Server
Buffer Pool Value
---------------------------------------- ----------
Database 0
Simulated 0
Target 65503232
Dirty 0
In IO 0
Latched 0
Page Life Expectancy 167
2013-03-15 15:10:15.10 Server
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 0
Reserved Limit 474920
2013-03-15 15:10:15.10 Server
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 49368
Predicted 97702
Private Target 0
Private Limit 0
Total Target 524288
Total Limit 524288
OOM Count 0
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 998
Rate 4587
Target Allocations 337189
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 4013
Rate 12480
Target Allocations 348097
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 331604
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_COMMITTED (internal) Pages
---------------------------------------- ----------
Allocations 44355
Rate 31258
Target Allocations 407217
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_XTP (internal) Pages
---------------------------------------- ----------
Allocations 2
Rate 9
Target Allocations 331615
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
Memory Pool (default) Pages
---------------------------------------- ----------
Allocations 0
Predicted 94982
Private Target 0
Private Limit 0
Total Target 524288
Total Limit 524288
OOM Count 0
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 331604
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 331604
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_RESERVE (default) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 425984
Future Allocations 94848
Overall 425984
Last Notification 1
2013-03-15 15:10:15.10 Server
MEMORYBROKER_FOR_XTP (default) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 331604
Future Allocations 0
Overall 425984
Last Notification 1
2013-03-15 15:10:15.12 Server Exception raised in Startup::CommunicationsManager.
March 15, 2013 at 1:16 pm
2013-03-15 15:10:15.10 Server Failed allocate pages: FAIL_PAGE_ALLOCATION 1
That's memory related, might have something to do with the NUMA node configuration, not sure though.
Going to have to consult an expert on this one. Let me see if I can find someone awake at this hour of a Friday.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2013 at 1:20 pm
I'll try setting it to default for memory allocation and see if that goes away.
I'm also going to try allocating just processor and not I/O affinity, as that was an earlier config that we were using but changed away from because we thought it might have been the cause of some performance issues.
I'll report back shortly with logs.
March 15, 2013 at 1:28 pm
cryovenom (3/15/2013)
I'll try setting it to default for memory allocation and see if that goes away.
Will be interesting to see, but memory allocation is the one thing you do need to do on a server with multiple instances, leaving one or all at default's not a good idea, they will compete for memory and with the OS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2013 at 1:39 pm
My guess without being able to query the system is that you've mixed NUMA nodes in your settings. The AMD 6212 would have been my last choice for a SQL Server processor today, the single threaded performance of those is substantially lower than the current Intel E5 series processors. The 6212 presents 2 NUMA nodes per socket, so you have 4 total NUMA nodes each with 4 cores and 8GB of RAM in your given configuration.
I don't know why you are using affinity mask settings here which are deprecated, instead of using ALTER SERVER CONFIGURATION (http://msdn.microsoft.com/en-us/library/ee210585.aspx) which is how you should be setting your process affinity per instance in SQL Server 2012. As Gail already pointed out, I wouldn't recommend setting a server up in the manner that you are doing here. If you want isolated workloads, make that server a VM host and run 4 different VMs so you have the type of isolation you are trying to get here.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
March 15, 2013 at 1:59 pm
Jonathan Kehayias (3/15/2013)
My guess without being able to query the system is that you've mixed NUMA nodes in your settings.
This may sound like a rather basic question, but what do you mean by "you've mixed NUMA nodes in your settings"? Can two instances not use cores in the same NUMA node? or can one instance not use cores in separate NUMA nodes? I noticed that in 2012 it separated my cores into 4 nodes, while 2008 didn't do that, but I didn't realize there were things I couldn't split across nodes.
Jonathan Kehayias (3/15/2013)
The AMD 6212 would have been my last choice for a SQL Server processor today, the single threaded performance of those is substantially lower than the current Intel E5 series processors.
That was purely a cost restriction. We were able to get Supermicro servers with 2x Opteron 6212s, 32GB of RAM and 6x240GB SSDs, with hotswap PSUs, hotswap drive bays and Adaptec RAID cards for under $4k ea, taxes inc. We couldn't find anything close on the Intel side, or through large vendors like HP, Dell, IBM, etc... So we built these whitebox servers. They're way more performant than we even need, considering that each of these instances used to be a server with 2x single-core Opteron 2000-series processors and 4GB of DDR2 on Tyan boards in Antec cases with no hotswap anything and Seagate 7,200rpm drives. We're happy with the price/performance ratio of these new boxes and have no complaints about the Opterons
Jonathan Kehayias (3/15/2013)
I don't know why you are using affinity mask settings here which are deprecated, instead of using ALTER SERVER CONFIGURATION (http://msdn.microsoft.com/en-us/library/ee210585.aspx) which is how you should be setting your process affinity per instance in SQL Server 2012.
I was setting them in SSMS. The only reason that chunk of SQL using sp_configure 'affinity mask' appears there is because that's what I found when I was looking for a way to un-do the setting without an uninstall/reinstall of the instance. I'm assuming SSMS uses ALTER SERVER CONFIGURATION behind the scenes to set things.
Jonathan Kehayias (3/15/2013)
As Gail already pointed out, I wouldn't recommend setting a server up in the manner that you are doing here. If you want isolated workloads, make that server a VM host and run 4 different VMs so you have the type of isolation you are trying to get here.
I appreciate the recommendation. Adding VM licensing and three more OS licenses to the mix is not something I will easily be able to sell management on, and our chosen VM host (VMWare ESXi 5) has a bit of trouble with the RAID cards on these servers so I'd likely have to hack something together by installing an OS and running virtualization software within the OS, and lose a bunch of resources to overhead along the way. The Windows System Resource Manager that Gail mentioned seems like a much more attractive option if that results in similar resource isolation guarantees.
And as I said, until I get that working, I'd like to solve this mystery if possible.
March 15, 2013 at 2:12 pm
cryovenom (3/15/2013)
This may sound like a rather basic question, but what do you mean by "you've mixed NUMA nodes in your settings"? Can two instances not use cores in the same NUMA node? or can one instance not use cores in separate NUMA nodes? I noticed that in 2012 it separated my cores into 4 nodes, while 2008 didn't do that, but I didn't realize there were things I couldn't split across nodes.
If you only have 2 nodes in 2008 you have different hardware. While the 2012 SQLOS memory manager was rewritten, the NUMA layout presented is from the hardware and SQLOS creates memory nodes that map to the hardware nodes directly, so you have to have different configurations for hardware.
I was setting them in SSMS. The only reason that chunk of SQL using sp_configure 'affinity mask' appears there is because that's what I found when I was looking for a way to un-do the setting without an uninstall/reinstall of the instance. I'm assuming SSMS uses ALTER SERVER CONFIGURATION behind the scenes to set things.
That is a dangerous and incorrect assumption. Just because SSMS does something one way doesn't mean that is the best, or even correct, way of doing things.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply