This is an awesome post.
To answer your final question, I'd speculate that your answer is "yes".
That said, I probably would have pursued soft numa before resorting to the trace flags. I think you are right about the numa node allocation and that's could be at the root of your problem. Via soft numa, you could scale out the requests, depending on your count of numa nodes (4?) - to 4, if that's the correct number, and then assign, via ip address, each host to a specific numa node, in addition to that, I'd disable hyper threading. Combined, the issue would likely decrease, however, it would do so at the cost of fewer schedulers.
On a seperate thought, I'd look at forced parameterization - the fact that your load is driving your CPU so high is very interesting and it may be worth looking into that.
Lastly, on multi-numa node systems, I typically have the output of the following queries dumping to a table every N seconds. Makes for some interesting analysis when workload changes or trace flags are implemented:
SELECT getdate() as poll_time, node_id, node_state_desc, memory_node_id, online_scheduler_count, active_worker_count, avg_load_balance, idle_scheduler_count
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'
and
SELECT top 10 getdate() as sample_poll, wait_type, count (*)
FROM sys.dm_os_waiting_tasks
WHERE [wait_type] NOT IN
('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
'SQLTRACE_BUFFER_FLUSH','WAITFOR', 'BROKER_TASK_STOP',
'BROKER_RECEIVE_WAITFOR', 'OLEDB','CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT' )
GROUP BY wait_type
ORDER BY COUNT (*) DESC
SQLServer:Databases\Log Flush Wait Time counter, it is staying fairly constant between 600 ms and 900 ms with an average of 750ms
750ms wait time per flush is huge. It means every transaction statement must wait in average 750ms to commit. If you don't use explicit transactions for writes it means each write statement (INSERT/UPDATE/DELETE) must wait 750ms to complete. However this does not corroborate with the other sttaement 'PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms.'. One explanation is that you have log growths (you do, the counters say so) which skew the log flush wait times but that would only explain averages over long periods.
SQLServer:Databases\Log Growths, I'm at 325
Figure out which database is growing and resize the log appropriately. You should not occur growth events in production, they are extremely expensive. the counter has instances per database.
PhysicalDisk\Avg. Disk sec/Write ... For the Log Files volume, it stays almost steady at 8 ms. Is it normal for the Log Files to have consistent writes with no break?
Yes. A transaction cannot commit unless the log was written to disk. As long as you have transactions, you will have log disk writes.
PhysicalDisk\Avg. Disk sec/Write counters: For the TempDB volume, I get dramatic spikes every few minutes that are upwards of 100 ms.
It would be good to know why. Can you correlate it with a specific activity that causes the spike? Can you correlated it with database checkpoints occurring?
Memory\Pages Input/sec counter, the values fluctuate dramatically and have an average of 6 but have many spikes well upwards of 100 (some as high as 400).
So you're paging. Why? Insufficient memory? Any other offending process on the server host? Virtualized overcommitted environment? Only you can tell. Does swapping IO overlap with any of the IOs above (same physical path, ie. same disks)? Then some of the SQL timers may be influenced by this paging spikes.
Best Answer
You can use this script written by Dimitri Furman from Microsoft. It will measure transactions/sec per database. You need to run this against the database where you want to measure transactions/sec. I tested this against V12 version and it works.
https://blogs.msdn.microsoft.com/dfurman/2015/04/02/collecting-performance-counter-values-from-a-sql-azure-database/