February 24, 2017 at 2:46 pm
This I am seeing on a particular SQL Instance . ( SQL 2012)
It is a simple daily maintenance task update stats and rerog all databases.
That is it.
The task takes around 10 minutes.
As soon as Job runs I get alerts for PLE dropping to ZERO.
I do not see this behaviour on otehr Servers.
Any comments ? Thanks.
February 24, 2017 at 3:08 pm
dvprao - Friday, February 24, 2017 2:46 PMThis I am seeing on a particular SQL Instance . ( SQL 2012)
It is a simple daily maintenance task update stats and rerog all databases.
That is it.
The task takes around 10 minutes.
As soon as Job runs I get alerts for PLE dropping to ZERO.
I do not see this behaviour on otehr Servers.
Any comments ? Thanks.
My guess is, you've hit a resource cliff, over which the server falls when this task runs. This is a good time to review your overall resource usage on this server, as well as the task itself to see if there are things that take place in parallel that might need to not do that, or if there's been large growth in one of the affected databases, that now causes the re-org to consume considerably more RAM. What kind of server do you have? Physical or Virtual Machine? Total RAM? Overall size of all the databases on this instance?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 24, 2017 at 4:24 pm
Steve ,
Thanks for responding.
This is a VM . A very small server.
Hardly heavy use. I would say , 6 GB RAM
SQL Memory set at MAX 4 GB . Mn 1 GB.
I have not seen this on any other servers I have ...
6 , 3 GB . BUt the SSIS DB is very large in comparison 25 GB. Should I cleanthis up ?
February 24, 2017 at 4:40 pm
I tried to run it one more time by excluding the large DB . Did not help.
Immediately the PLE tanks to ZERO.
I must be missing somethign here.
February 25, 2017 at 9:30 am
Wow... I can't say I can ever remember seeing anyone using such an absurdly small server for anything useful. The server OS generally needs at least 2 GB just to operate, never mind handle much else. Expecting to handle a 32 GB database with just 6 GB of total RAM and only 4GB of it allocated to SQL Server is just not realistic. Chances are, back when this server first went into operation, it was on the edge, at best, and the only thing that kept it from falling over a cliff a long time ago was the relative lack of usage. Now that you're actually putting some load on it, it's reacting largely as I would expect it to. I'd also guess that this hardware might not even be capable of enough of a RAM upgrade to do any good. It's probably long past time to get a professional performance consultant in to analyze the business need and then assess options.
That said, there may be some things you can do. If there is data in that 32 GB database that is no longer needed, then perhaps you can back it up to a USB hard drive and then purge it. The database won't get smaller, but it will have a lot less stuff in it to work on. Making it smaller means shrinking it, which isn't always a good idea. That's another thing the pro can help you assess.
These days, 32 GB of RAM is generally the smallest anyone uses for a database server, and it's usually better to be at 64 GB, so that's why your scenario appears to be so under-powered.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 25, 2017 at 2:52 pm
If you're not having performance issues with normal stuff, stop worrying about PLE dropping during index maintenance because it's going to use whatever resources it can get its hands on.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply