Thanks for the responses. I've been looking at this for a few days on and off. Today I've been sitting here watching the counters because I am seeing such weird, weird output.
Here are some numbers from right this instant:
Resource Monitor\Memory (Instance allocation is min 70,000 max 100,000)
In use: 105200
Standby: 25000
Free: 70
Standby and free look weird, I would expect 15 gig less in standby and 15 gig more in free.
Perfmon:
Buffer Manager:PLE = 12
% Disk Time (data) = 92,000 (!!!!)
Avg Queue Length (data) = 920
Disk Reads / sec (data) = 6000 (!!!!)
Disk Writes / sec (data) = 55
% Disk Time (log) = --
Avg Queue Length (log) = 0.02
Disk Reads / sec (log) = 0
Disk Write / sec (log) = 2
% Processor Time = 14.5
Instantaneous output from sp_whoisactive at the same time shows no signficant reads from query activity:
reads writes physical_reads
------ ------ --------------
54 0 64
29 0 96
6 0 0
75,567 0 28,832
4 0 0
4 0 0
4 0 0
4 0 0
4 0 0
5 0 0
40 0 14
18 0 0
5 0 0
5 0 0
4 0 0
NULL NULL NULL
13 0 16
5 0 0
5 0 0
NULL NULL NULL
NULL NULL NULL
56,959 5 56,780
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
69,398 36 3,417
1,452 0 1,722
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
120 3 16
NULL NULL NULL
NULL NULL NULL
4 0 0
1,756 8 304
NULL NULL NULL
7 0 7
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
207 2 23
NULL NULL NULL
90 0 0
NULL NULL NULL
8 0 0
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
27 0 0
98 0 0
NULL NULL NULL
8 0 0
NULL NULL NULL
NULL NULL NULL
101 3 92
224 6 16
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL NULL NULL
sys.dm_os_wait_stats
CXPACKET is at the top (15%), followed by...
SP_SERVER_DIAGNOSTICS_SLEEP
XE_TIMER_EVENT
HADR_FILESTREAM_IOMGR_IOCOMPLETION
DIRTY_PAGE_POLL
BROKER_EVENTHANDLER
XE_DISPATCHER_WAIT
LOGMGR_QUEUE
PAGEIOLATCH_SH is down in 12th place (4.3%)
NUMA
Unfortunately I know virtually nothing about NUMA yet. I guess this is me learning about it, heh.
I see 5 nodes in sys.dm_os_nodes, 2 "ONLINE", 1 "ONLINE DAC", 2 "OFFLINE".
In sys.dm_os_memory_nodes I see:
memory_node_id virtual_address_space_reserved_kb virtual_address_space_committed_kb locked_page_allocations_kb pages_kb shared_memory_reserved_kb shared_memory_committed_kb cpu_affinity_mask online_scheduler_mask processor_group foreign_committed_kb
-------------- --------------------------------- ---------------------------------- -------------------------- -------------------- ------------------------- -------------------------- -------------------- --------------------- --------------- --------------------
0 203592464 2183796 48977664 10796224 1920 1920 1048575 1048575 0 21068516
1 35712 35732 2784 2784 0 0 1048575 0 1 0
2 35712 35732 2784 2784 0 0 1099510579200 0 1 0
3 32768 32788 51128584 11795384 0 0 1099510579200 1099510579200 0 23373076
64 0 20 0 10797256 0 0 1048575 1048575 0 0
It looks really weird to me. Query activity is minimal but disk activity is thorugh the roof and PLE is collapsing at semi-regular intervals. Any interpretations would be most welcome.