February 18, 2012 at 5:34 pm
Hi,
We are running SQL Server 2008 R2 Enterprise Edition (64-bit) on a server with 72GB of RAM. This server is mostly used as a SQL Server but also hosts couple of LOB applications.
The server has just 5 databases and 1 among them is highest volume database with millions of transactions happening everyday but this also means the indexes (or rather indices) are fragmented at the end of the day so we have a maintenance plan that rebuilds all index and updates all statistics with full scan.
During the course of the day the server is working along nicely using roughly 48GB RAM and all is well but when the maintenance plan kicks in 3AM every morning the SQL Server consumes all available memory but does not release the memory after it is done and leaving nothing for the LOB app after that and we are experiencing slowdowns.
I am not 100% convenienced that SQL is that bad managing memory and I am trying to convey that message to the management but they are not just looking at the task manager and saying all RAM is gone so SQL is the bottleneck. I have suggested that I can limit the memory to satisfy their curiousity and rule out any possibility.
I propose to set 64GB as Maximum server memory value but I have couple of questions before I do that.
* Does this change apply to the SQL Server Service or all other SQL Server Services such as Reporting Service, SQL Agent and so on?
* Is SQL Server that bad eating all memory and not releasing back to the OS? (All research I have done so far proves that it is very good at managing the memory).
TA 🙂
WRACK
CodeLake
February 18, 2012 at 6:30 pm
SQL Server wants to take all the memory it needs and can be slow to release it back to the OS. That is the way it's designed, which is why dedicated hosts are recommended.
Look at your target server memory, which gives an idea of what SQL wants. 64GB might be good. If 48GB works, I might pad that a little and set it there. You don't want to starve the OS or LOB apps either.
This setting applies to the database engine buffer pool. That are other requirements outside this for the db engine that are not necessarily huge, but they can be. You can't control those as well, but that memory usually is released quicker to the OS. AFAIK, SSIS, SSRS, use separate memory since they are separate services.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 18, 2012 at 8:36 pm
Thanks Steve.
Yes I know that SQL Server should be on a separate host but we are stuck with it for now.
I have checked and the Target Memory for SQL Server is 63.04518127441406 GB. So I guess 64 GB isn't that bad either. I know for the fact the LOB applications need at max 2 GB so I don't think it will have any issues since assigning 64GB to SQL Server will leave 8 GB for the OS (Server 2008 R2) and LOB apps.
I guess I can also go for 60GB and leave 12 GB for others.
WRACK
CodeLake
February 19, 2012 at 3:07 pm
I might start gathering some metrics, get an idea how long some reindexing takes, some important queries/reports, and document. Then go to 60GB.
Easier to go up than down, so I'd ramp it up a bit, see what happens, and then decide if I need to go up again.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 19, 2012 at 3:26 pm
The maintenance task takes around 110 Minutes to finish but it does a lot more than just Reindexing. We have also collected a massive work load files using profiler and have them run through the performance tuning and have found no missing index or statistics.
I will recommend to cap it at 56 GB and go from there.
Oh and I should mention, we haven't got the Service Pack 1 installed. Should we do it now or wait a little longer.
Any other suggestions would be welcome too.
WRACK
CodeLake
February 19, 2012 at 3:50 pm
Just a point, if SQL is not on a dedicated server, then setting a sensible max server memory is almost essential. It's recommended even on a dedicated server, especially of locked pages is enabled (to make sure it can't starve the OS)
On a 72GB dedicated server, I would go no higher that 62-64GB for SQL Server's max server memory. For a non-dedicated server where there are other apps, subtract their requirement from that figure and then monitor. If there's a lot of memory free at the busiest times (perfmon available memory > 1GB) then you can adjust the max server memory upwards. Always safe to start conservative and increase later.
p.s. Task Manager is a bad place to check SQL's memory usage, if locked pages is enabled it will show a completely wrong figure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2012 at 4:29 pm
I'd install SP1, if for no other reason than if I have issues, MS support will want it. It appears to be very stable. However make sure you test it well first.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 19, 2012 at 4:32 pm
WRACK (2/19/2012)
but it does a lot more than just Reindexing.
Exactly what does it do?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 19, 2012 at 6:06 pm
GilaMonster (2/19/2012)
Just a point, if SQL is not on a dedicated server, then setting a sensible max server memory is almost essential. It's recommended even on a dedicated server, especially of locked pages is enabled (to make sure it can't starve the OS)On a 72GB dedicated server, I would go no higher that 62-64GB for SQL Server's max server memory. For a non-dedicated server where there are other apps, subtract their requirement from that figure and then monitor. If there's a lot of memory free at the busiest times (perfmon available memory > 1GB) then you can adjust the max server memory upwards. Always safe to start conservative and increase later.
p.s. Task Manager is a bad place to check SQL's memory usage, if locked pages is enabled it will show a completely wrong figure.
Locked Pages are NOT Enabled. AWE to allocate memory option is NOT ticked.
WRACK
CodeLake
February 19, 2012 at 6:09 pm
Perry Whittle (2/19/2012)
WRACK (2/19/2012)
but it does a lot more than just Reindexing.Exactly what does it do?
* Sets some database to Simple Recovery Model
* Sets AUTO UPDATE STATISTICS on some databases
* Sets AUTO CREATE STATISTICS on some databases
* Sets AUTO CREATE STATISTICS ASYNC on some databases
* Shrinks some databases
* Rebuilds All Index
* Update All Stats with FULL SCAN
WRACK
CodeLake
February 19, 2012 at 7:29 pm
WRACK (2/19/2012)
Perry Whittle (2/19/2012)
WRACK (2/19/2012)
but it does a lot more than just Reindexing.Exactly what does it do?
* Sets some database to Simple Recovery Model
* Sets AUTO UPDATE STATISTICS on some databases
* Sets AUTO CREATE STATISTICS on some databases
* Sets AUTO CREATE STATISTICS ASYNC on some databases
* Shrinks some databases
* Rebuilds All Index
* Update All Stats with FULL SCAN
Shrinking databases causes fragmentation.
Rebuilding indexes causes databases to grow.
Performing both is counterproductive.
Shrinking databases in a maintenance plan is the wrong thing to do.
February 19, 2012 at 8:39 pm
SpringTownDBA (2/19/2012)
Shrinking databases in a maintenance plan is the wrong thing to do.
I am aware of that but due to the nature of LOB application and some tasks that processes millions of transactions leave the database quite fragmented anyways and the database size grows a little.
Shrinking the database is happening before the Rebuild task so that is not an issue. Also after rebuilding index task the final size of the database is same as what was a day before since the database size is pretty nicely managed. Honestly I am not fussed that much about it as this works and the timing is not an issue either as it takes very little time.
WRACK
CodeLake
February 20, 2012 at 12:35 am
WRACK (2/19/2012)
Perry Whittle (2/19/2012)
WRACK (2/19/2012)
but it does a lot more than just Reindexing.Exactly what does it do?
* Sets some database to Simple Recovery Model
* Sets AUTO UPDATE STATISTICS on some databases
* Sets AUTO CREATE STATISTICS on some databases
* Sets AUTO CREATE STATISTICS ASYNC on some databases
* Shrinks some databases
* Rebuilds All Index
* Update All Stats with FULL SCAN
Wow, you do like to make your server work for a living don't you 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 20, 2012 at 2:36 am
WRACK (2/19/2012)
GilaMonster (2/19/2012)
Just a point, if SQL is not on a dedicated server, then setting a sensible max server memory is almost essential. It's recommended even on a dedicated server, especially of locked pages is enabled (to make sure it can't starve the OS)On a 72GB dedicated server, I would go no higher that 62-64GB for SQL Server's max server memory. For a non-dedicated server where there are other apps, subtract their requirement from that figure and then monitor. If there's a lot of memory free at the busiest times (perfmon available memory > 1GB) then you can adjust the max server memory upwards. Always safe to start conservative and increase later.
p.s. Task Manager is a bad place to check SQL's memory usage, if locked pages is enabled it will show a completely wrong figure.
Locked Pages are NOT Enabled. AWE to allocate memory option is NOT ticked.
My point still stands, if SQL is on a non-dedicated server, then setting a sensible max server memory is almost essential
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply