0

Can somebody shed some light on if it's normal that Page Life Expectancy drops occasionally to 0 in SQL Server (2014) resulting in slow queries and timeouts?

I'm facing this on my developer machine when developing a web application and I don't know whether it is because of the development environment or something else. PLE may be over 10000 for one day or a couple of hours (during which queries work fast) until it drops to 0 and all my queries go from 15ms to 40 seconds or more.

I mean is there any way to control the performance of SQL Server in this matter or is this by design of the SQL Server that it may get slow, for example, due to memory pressure until it comes faster again etc.?

To me it looks like queries are read in a cache and at some point they're flushed out and queries start giving timeouts because everything is read from a disk. Is this something that can be avoided?

0

3 Answers 3

2

If your laptop has 4 gigs that means that SQL Server probably has access to 500 megs of RAM at best.

PLE tells you how long SQL Server is able to keep data in memory. If there's no load on the SQL Server then the PLE will just keep climbing. Once you put load on it again the PLE will drop as the SQL Server needs to read data from disk. The more data that it needs to load from disk the longer the queries will take.

If your disk is slow, which it probably is in your laptop the queries which hit the disk will just run slower as you want for the disk to spin.

If you want good performance on your laptop you'll want to ensure that SQL Server has enough RAM to ensure that the working set of your data sits in memory and not on disk. The first time it is accessed it'll need to be loaded from disk but after that you want it to stay in memory. You'll probably want an SSD as well so that the disk is fast enough.

1

I think I found a possible reason for PLE drops to 0 and it seems to be memory pressure. I found that my SQL Server logs are full of messages "A significant part of sql server process memory has been paged out. This may result in a performance degradation."

"Windows is moving the allocated bytes of the process from physical RAM to page file because of memory pressure. Memory pressure is most commonly caused by applications or windows components that are requesting more memory causing OS to start trimming working set of other processes to satisfy these new requests."

Link

I guess if I have simply too much applications running on my developer laptop (4 GB RAM) it starts affecting the SQL Server.

Do you think 8 GB or 16 GB RAM would be then better for a development laptop in this case? I remember that 4 GB was a minimum requirement for SQL Server 2014.

2
  • 1
    I would not develpo anything db heavy on a laptop - and f I have to, then on one with 16gb if I could. Minimum is minimum - the moment you want to test something with load.... heck, I often run test sql on a server with 100gb becasue that is what it takes to test on realistic data. Laptops have limitations. 4gb is extremely low - especialyl because you run a lot more than sql on it.
    – TomTom
    Dec 12, 2014 at 10:47
  • This sounds entirely plausible. To corroborate, take a look at the Total Memory and Target Memory perfmon counters for your instance. If Target is higher than Total, SQL Server would like to have more but cannot get more. Also, worrying about performance characteristics on your laptop is somewhat of a fool's errand. Insofar as (in my opinion) your laptop is the place to prove that the functionality is correct, and an actual server is where you prove that it performs well.
    – Ben Thul
    Dec 12, 2014 at 15:59
0

Do you have autoclose (or autoshrink) set to ON for the databases?

These are nasty options (Google searchresults) best set to off in any situation.
Neither has any real world use case

In this case, when the database is closed then all plans and cached data will be evicted. Then the database needs to start up.

Another option is SQL Server crashing. Check the logs.
For example, we recently hit this bug KB 2980395 in our data warehouse

1
  • No, they're switched off but if you read my post below I think the answer is, after all, memory pressure.
    – user405723
    Dec 12, 2014 at 10:45