SQL Server – Is Auditing Available in Standard Editions?

I bet you've configured the virtual CPUs in a way that some of the CPU nodes and/or memory nodes are offline.

Download sp_Blitz (disclaimer: I'm one of the authors of that free open source script) and run it:

sp_Blitz @CheckServerInfo = 1;

Look for warnings about CPU and/or memory nodes being offline. SQL Server Standard Edition only sees the first 4 CPU sockets, and you may have configured the VM as something like 6 dual-core CPUs. It'll end up hitting an issue similar to how Enterprise Edition's 20-core-limits cap the amount of memory you can see.

If you want to share sp_Blitz's output here, you can run it like this to output to Markdown, which you can then copy/paste into your question:

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1;

Update 2018/04/16 - confirmed. You attached the sp_Blitz output (thanks for that!) and it does indeed show that you have CPU and memory nodes offline. Whoever built the VM configured it as 12 single-core CPUs, so SQL Server Standard Edition is only seeing the first 4 sockets (cores), and the memory attached to them.

To fix it, shut down the VM, configure it as a 2-socket, 6-core VM, and then SQL Server Standard Edition will see all of the cores and memory. This will also reduce your SOS_SCHEDULER_YIELD waits too - right now, your SQL Server is hammering the first 4 cores, but that's it. After this fix, it'll be able to work on all 12 cores.

If you used that template, it should definitely be logging the queries you ran from SSMS.

Extended Event sessions don't start by default when you create them - you need to tell them to start. Perhaps you did that on the other server, but not in this second case?

You can check to see if the session is running by look at the results of:

select * from sys.dm_xe_sessions

If your session is not in that result set, it's not running.

You can start the session up by running:

ALTER EVENT SESSION your_session_name
ON SERVER  
STATE = start;

If you confirm it's running, and you're still not seeing results in your file, please script out the event session in SSMS and update your question with the results. Perhaps it's configured differently by mistake, or it is writing the file to a place you don't expect.

Fantastic job adding those details to your question, that was really helpful.

It sounds like what you were running into is what Shawn Melton mentions in his answer here.

In short, events (queries, in this case) are only written to that file when the MAX_DISPATCH_LATENCY (defaults to 30 seconds) or MAX_MEMORY (defaults to 4 MB) is reached.

For what it's worth, I have experienced that MAX_DISPATCH_LATENCY is not always enforced (i.e., if the MAX_MEMORY buffer limit is not reached, the file is not updated - even if it's been longer than 30 seconds). However, as Shawn mentioned, stopping the event session flushes the entire buffer of events into the file.