August 21, 2008 at 8:07 pm
Hi Folks,
I seem to be having paging problems after installing several instances on my sql server. I hope I am giving enough info to get some good advice cos I'm stumped.
I am working off a dual quad core processor (2.4ghz) with 16 GB 64 bit machine. there are 10 instances on this machine but only 4 get any serious use at present. The other 6 are just got maintenance plans for checkdb, backup, index defrag and db shrink at night. SQL is 2005 64 bit
When I got in this morning Operations Manager sent me an alert that Committed Bytes in use was too high. It was above 80% and the page file was 16GB
The instances I have installed have all the services - but some have been disabled. Disabled are:
reporting services
only 3 installed have full text search
With max memory server option the total max memory for all instance is 14.5 GB. This is a dedicated SQL server
Anyway I knew previously that this was a problem so I did some memory logging last night - with the following counters
Memory - % committed bytes in use
Memory - % Avialable bytes
Physical disk - Disk time (total)
Process - Page File Bytes (for each sql instance)
After some more investigation I noticed a increase in %committed %disk time and page file bytes (for particular instances) when the maintenance plan was kicking in (as referred earlier) - in particular the checkdb was happening. The thing I don't understand is even though there was an increase in paging there was still several GB of avialable memory. This is one entry when there was a sharp increase of committed bytes
Time: "23:54:51.642",
Committed in use % :"60.5310425495163",
Memory Avialable:"7125131264",
Disk Time :"792.23080722099905",
Page File Bytes (for problem instance at this point): 1265430528 was (280400000 15 seconds before)
I just found out dbcc Checkdb causes stress to the system
I have set lock page in memory user right but it doesn't seem to have registered in the log.
Has anyone got any advice help for me?
August 25, 2008 at 4:29 am
bodhilove (8/21/2008)
I have set lock page in memory user right but it doesn't seem to have registered in the log.
Are all your instances Enterprise Edition ? Because for standard edition "lock pages in memory" will be ignored.
Do you see any messages in SQL Errorlog like "A significant part of sql server process memory has been paged out. ..."
See this KB-Article http://support.microsoft.com/kb/918483
I also wonder if 10 instances is such a good idea on a server just 16 Gb RAM. You should leave at least 2 Gb for the OS, that leaves only 1,4 GB on average per instance, which for a database server is not very much.
[font="Verdana"]Markus Bohse[/font]
August 26, 2008 at 12:26 am
thanks Marcus.. you are probably right about the 10 instances.. but I am not convinced that this is the problem in this case because I turned off the services for 5 instance and ran a dbcc checkdb and it still went crazy.
I did find something interesting though. After setting the user right to lock pages in memory I found doco to tell me to verify it in the sql log. I could not see it in there so I ran RSOP and found the policy didn't seem to be applied. Looking further at mailing list someone suggested to reboot the machine after the GPO change so I will give that a go tonite.
Crossing my fingers and toes!
August 26, 2008 at 12:34 am
Are the instances all enterprise edition? Standard edition ignores locked pages.
What's the max memory set to on each of the instances?
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
August 26, 2008 at 3:30 am
rebooting was no good - seems to not want to apply the lock pages in memory GPO as it doesn't register in RSOP or sql server logs.
the instances are all enterprise
Max memory for my instances are
instance 1 3500 MB
instance 2 1000 MB
instance3 1024 MB
instance 4 3000 MB
instance 5 4000 MB
instance 6 1000 MB
instance 7 1000 MB
instance 8 1000 MB
instance 9 1000 MB
instance 10 1000 MB
I know that adds up to 17.5 GB and I only have 16 GB but I have turned off all the instances from instance 5 (services are turned off)
using 12.5 GB
August 26, 2008 at 5:00 am
Do all of the instances use the same service account? What accounts have the lock pages priviledge?
I would suggest you reduce the max memory for some of the instances. With 16GB memory, you should be leaving 2 GB free for the OS. Also, max memory sets the size of the buffer pool, and SQL does allocate 300-400 MB over and above that (the MemToLeave area)
Can you run perfmon for a while, trace the Process: Page Faults/sec for different processes and see which ones are contributing to the paging?
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
August 26, 2008 at 4:52 pm
thanks for your assistance Gail.
All the instances are using the same service account - svc_sqlserver which has the lock pages priviledge. RSOP is not registering this GPO as being applied and neither is the sql server log
I will take the advice about reducing max memory but I need to ask a dumb question- If I turn off the services for an instance is there any way the instance can still use memory?
I have run perfmon for several days and I can replicate the paging on demand. It happens when a dbcc checkdb happens
(which is a nightly process). For instance. My page file was 2.5 GB after running checkdb on a 8 GB db the page file grew to 6.5 gb
August 26, 2008 at 7:46 pm
I was wrong the edition is standard for the instances. Is there any advice dealing with paging with this?
August 27, 2008 at 1:32 am
Unfortunately there's no real fix for this issue when you use Standard Edition. Even though the KB article suggests a couple of workarounds, i my experience they don't really help.
What I've done once is to remove the pagefile completely. That worked but if you do that make sure that you leave enough RAM availbale for the OS and evtl. other apps. In our case we had a total 0f 32 Gb and reserver 6 Gb for OS and everything else.
[font="Verdana"]Markus Bohse[/font]
August 27, 2008 at 1:44 am
bodhilove (8/26/2008)
I was wrong the edition is standard for the instances. Is there any advice dealing with paging with this?
Standard edition ignores the locked pages setting. I would suggest, as a long term fix, move about half of the instances off onto another server and set the max memory appropriatly for the others.
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
August 27, 2008 at 4:28 am
I am tending to side with Marcus about removing the page file because. When this happens (the paging) there is still plenty of RAM avialable (several GB) and I have most half of the instances turned off anyway so I am not convinced moving them will fix anything. Am I sounding half sane?
August 27, 2008 at 11:31 pm
I spoke to a few people about remving the page file. We agreed it was probably not the best due to not being able to check memory if the system crashes. My question now is performance wise is it better to have a single instance over multiple instance on a 64 bit machine?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply