3

I'm trying to put my hands on the reason this SQL Server slows down sporadically. There are no patterns detected so far. It's a SQL Server 2008 R2 running on a Windows Server 2008R2 VM on top of VMware 5.0. The VM has 16GB of memory with max server memory in SQL set to 8GB. On seemingly random days i see Page Life Expectancy drop off a cliff from 42,000+ sec to 200-300 sec for a few minutes. It does not decrease with time. It literally falls of a cliff. A complete 90 degree drop on the chart when i plot the values. then i it starts to build up again

The Target Server Memory and Total Server Memory counters are consistent at 8GB throughout. There also no spikes in batch requests/sec or user connections at these times.

On the VM level, Memory Mapped is consistently at 16GB and Memory Ballooned is 0, so the VM is not under memory pressure.

I'm trying to think where i should look next. What counters or areas of interest should i put my eyes on as a next step to understand these performance drops on the server.

Improve this question
6
  • Are you using a memory reservation for your SQL VM? Is it for the full 8 GB?
    – Tim Brigham
    Commented Oct 16, 2013 at 17:29
  • The VM has 16GB, 8GB is assigned as Max. Server Memory in SQL. There is no reservation set for the VM, but at the same time I'm not seeing any indicators of memory pressure. Memory Mapped in MB counter is constantly reading 16384MB with no hitch. Memory Ballooned never goes above zero. Memory\Available MBytes shows a minimum of 2.6GB and none of the SQL Server memory counters are showing any memory pressure either.
    – Zero Subnet
    Commented Oct 16, 2013 at 17:53
  • Are there any scheduled tasks running on the server (typically agent jobs) or on other servers that use the database? To me it sounds like something is fired up which consumes all the RAM, and after it's done SQL starts to flush the cache out..
    – pauska
    Commented Oct 16, 2013 at 20:02
  • I don't have permanent access to the server. I have not been able to get back on it for a number of reasons but i will start monitoring more perf counters when i do and also check on scheduled tasks or maintenance plans that might be kicking off.
    – Zero Subnet
    Commented Oct 21, 2013 at 14:15
  • 1
    Check this article blogs.msdn.com/b/axinthefield/archive/2013/10/22/… We're experiencing similar behavior.
    – user32587
    Commented Jan 8, 2014 at 16:32

4 Answers 4

Reset to default
6

How about Database pages, Free list stalls/sec, Page reads/sec, Page lookups/sec, Readahead pages/sec? Their behavior, correlated with the event, would be very useful to know. Similar, all the counters in Memory Manager category.

Could be a query that scans a large amount of cold data hits the server. As it reads the cold data, it evicts pretty much everything else in the BP, including its own just-read pages. After the event the BP slowly start building up the hot pages and PLE start to increase. Counters in Access Methods object could confirm this.

Improve this answer
2

8GB doesn't sound like very much RAM for SQL Server. How big are the databases on that server?

If the problem isn't VMware, perhaps you should check what's happening in SQL Server itself. Check the Windows event viewer's application log for messages like, "A significant part of sql server process memory has been paged out. This may result in a performance degradation."

Also try running some of the queries here, particularly the top 20 resource-intensive queries. If the drops occur at specific times, there might be a resource-intensive agent job running at that time.

Improve this answer
2

It is important that in vmware itself that the VM be guarunteed the amount of RAM that windows thinks it has.

Next things to check:

  • -- windows virtual memory is set appropriately for the VM
  • -- SQL itself is configured for RAM appropriate to to the VM
  • -- SQL Tempdbs are set appropriate to number of cpu cores and RAM

After that, the odds are that there is a SQL job/query/process of some kind that is simply putting load on SQL. This may be solvable with more RAM, etc, but will be specific to the SQL activity that is hammering the instance.

Improve this answer
2

I would suspect that there is a a query kicking off that is performing a large table/index scan forcing SQL Server to dump the buffer cache and pull in the entire Table/Index into memory thus your Page Life Expectancy drastically dropping.

You could look at querying the plan cache to see if you can find indications of large table scans. This previous post, https://stackoverflow.com/questions/1540192/searching-for-table-index-scans/1540500#1540500, has an answer with a query you can use to query the plan cache looking for scans.

Improve this answer

Your Answer

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

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