5

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.

Improve this question
13
  • do you have access to a vsphere client? Is the performance tab giving any information, something about balooning? You might have to ask your VMware admin if your ESX were under a heavy load...
    – KookieMonster
    Jan 12, 2015 at 14:34
  • @KookieMonster I did ask the VMware team, and they sent a report that showed that the VM was not even at 50% of CPU on their end (I'm not a VMware guy so I'm not sure how the numbers correlate). They are the ones who gave me the indication that there was a sudden drop of 16GB of RAM. The only other noteworthy thing was a few seconds of 0 network activity at the start of the 100% cpu usage.
    – Reaces
    Jan 12, 2015 at 14:38
  • have you set min and max memory for both instances? If you don't, they will fight for it and you may see this symptom.
    – rottengeek
    Jan 12, 2015 at 15:05
  • @Amanda Yep, one instance has 30GB as max, the other 56GB, leaving 10GB for the OS.
    – Reaces
    Jan 12, 2015 at 15:06
  • I think it should be fairly easy to set up an extended event to capture this. One way to get around ballooning is to LPIM. :D
    – rottengeek
    Jan 12, 2015 at 15:10

1 Answer 1

Reset to default
2

Because SQL Server released the memory back to the OS, this means the memory pressure was external, so you aren't going to find the reason within SQL Server. Your answer is going to come from outside SQL Server and I know of one tool that can track the memory allocations of the OS.

Rammap https://technet.microsoft.com/en-us/sysinternals/rammap.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.