November 20, 2015 at 4:11 am
Hi,
Production DB server is showing 98 percent always.How to bring it down.
http://s392.photobucket.com/user/Twothousand_BTHIS IS CLEARLY SPAMdia/Capture_zps1khl0ra8.jpg.html
November 20, 2015 at 4:22 am
SQL Server tries to use all the available RAM by default. This is expected and desirable in a dedicated SQL Server box.
If you have other applications running on the same server, you may want to lower the "MAX Server Memory" setting on the instance.
-- Gianluca Sartori
November 20, 2015 at 4:30 am
DB server consists of Inter Xeon processor(2 Processors) with 40 cores and a memory size of 64 GB. I have allocated 61 GB for sql server and the rest for windows server 2008. There is no other application running on the server other than sql server 2008
http://s392.photobucket.com/user/Twothousand_BTHIS IS CLEARLY SPAMdia/Capture1_zpsgr558n6a.jpg.html
November 20, 2015 at 4:35 am
OK, so what's the problem?
-- Gianluca Sartori
November 20, 2015 at 4:42 am
Since I have allocated 98 percent of memory for Sql server.Could it be the reason I am getting page_io_Latches
frequently
November 20, 2015 at 4:53 am
rameelster (11/20/2015)
DB server consists of Inter Xeon processor(2 Processors) with 40 cores and a memory size of 64 GB. I have allocated 61 GB for sql server and the rest for windows server 2008. There is no other application running on the server other than sql server 2008
Quick thought, in my experience 3Gb for the OS is too little even if nothing else is running on the server other than SQL Server. For an OLTP database server with these specs I would start with 20% for the OS and 80% for the SQL, then monitor and adjust if necessary.
😎
November 20, 2015 at 5:54 am
but the database instance contain 2 databases each with size 240 GB and 714 GB respectively. Dont you think that 80 percent memory is very less.
November 20, 2015 at 6:29 am
rameelster (11/20/2015)
but the database instance contain 2 databases each with size 240 GB and 714 GB respectively. Dont you think that 80 percent memory is very less.
No I don't think so and as I said previously, monitor the server and adjust if necessary. Two things to take into consideration, firstly if the OS is pressured for memory i.e. paging/swapping memory constantly then the SQL Server is going to feel that pressure as the OS will be reclaiming memory back from it. Secondly the sizes of the databases are almost irrelevant here, more useful information would be number of concurrent users/connections, transactions p. second, type and volume of activity etc.
😎
November 20, 2015 at 7:26 am
rameelster (11/20/2015)
Since I have allocated 98 percent of memory for Sql server.Could it be the reason I am getting page_io_Latchesfrequently
IO Latches are about reading and writing to disk. It's possible that it's related to memory, but it could just simply be a disk issue. Don't just focus on the waits, understand why you're getting the waits, which processes are specifically getting the waits. That will help you understand what the root cause is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 20, 2015 at 12:28 pm
rameelster (11/20/2015)
Production DB server is showing 98 percent always.How to bring it down
Why? What good is free memory that's not being used?
Surely you want all the resources in the server actually being used?
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
November 20, 2015 at 3:23 pm
rameelster (11/20/2015)
but the database instance contain 2 databases each with size 240 GB and 714 GB respectively. Dont you think that 80 percent memory is very less.
How much data are the problem queries moving?
November 21, 2015 at 3:54 am
Memory conjuction is happening when a job which was set to run daily to groom data older than 40 days .
November 21, 2015 at 3:56 am
Daily 2447639 records are getting groomed to an archive table located in the same database.
November 21, 2015 at 3:59 am
Memory conjuction is happening when a job which was set to run daily to groom data older than 40 days .
Daily 2447639 records are getting groomed to an archive table located in the same database.
November 21, 2015 at 9:54 am
So it's a giant batch process. You could try breaking up the batch into smaller pieces. It might run longer overall that way, but it'll use fewer resources while it's running. Make sure the query is tuned.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply