I have a SQL Server 2008 R2 used for sharepoint and ssrs. The SQL Server hosts two instances.
For a few minutes both instances were timing out, not responding.
CPU slowly rose to 100%, 30% from both instances, and 30% from the OS (and a few smaller things).
Afterwards, 16GB of memory was unallocated from SQL Server, a few GB of memory was used by some process (can't tell which on the esxi).
Since then, SQL Server again has its maximum amount of memory, and PLE is around 15000.
What I'd like to know is, how can I track WHY SQL Server was forced to give up its memory?
Either a history somewhere in the trace file, or a windows event? (I struck out on both so far)
I tried:
DECLARE @log NVARCHAR(100)
SELECT @log = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) + '\log.trc'
FROM sys.traces
WHERE id = 1
SELECT
g.DatabaseID,
g.LoginName,
g.StartTime,
g.EndTime,
g.DatabaseName,
g.FileName,
e.name
FROM ::fn_trace_gettable(@log, 0) as g
inner join sys.trace_events e on g.eventclass = e.trace_event_id
inner join sys.trace_categories as c on e.category_id = c.category_id
inner join sys.master_files as m on g.databaseID = m.database_id and g.filename = m.name
order by StartTime desc
But couldn't find anything during the time of the outage.
If it turns out not to be possible to find out why this happened, how can I monitor this in the future.
EDIT:
SQL Server version: 10.50.6000.34 Enterprise edition,
The server is being virtualised in esxi 5.5,
Page file is set at 8GB.
The error log shows a single failed logon of a sharepoint service user. (And a lot of successful log backups)
Update:
I've since had confirmation from the VMWare team that no Vmotions happened during this period on any related machines. And no ballooning occurred that might have affected the machine.
I've also had the network team confirm that Nagios showed no noticeable spike in latency on any of the machines related to this farm.
And we've since excluded any possible reports that were running at the time.
We have however seen 3 other occurrences of a 16GB of memory drop from SQL server in past statistics. 2 of which were related with reports of a temporary decrease in performance.