September 30, 2008 at 12:42 pm
We have two nodes in our cluster. Page faults are high on both of them regardless if it is the active node or not. Here is some information on the active node:
Hardware
RAM: 8GB
CPU: EM64T Family 15 Model 4 Stepping 3 GenuineIntel 2793 Mhz (x2)
Total Virual Memory: 19.56GB
Page File Space: 11.98GB (didnt know this could be higher than RAM)
OS: Windows Server 2003 Enterprise x64 Edition SP2
SQL 2005 Standard
AWE: Off
Process User PF Delta
wmiprvse.exe Network Services Saw readingas as high as 6500
Can anyone help a newbe out
October 3, 2008 at 6:19 pm
Page faults happen when a thread being processed doesnt find the required data in the physical memory and has to page it into the memory.
Your problem looks like a memory pressure issue.
Check the PageFaults/sec and Pages/Sec counters in the Perfmon. Values should be less than 50 or 100.
Run Sql Profiler and see if you can find queries with CPU count > 1000. Try to optimize these
Also refer to this forum discussion for page faults ...
October 8, 2008 at 12:24 pm
Nikhil Shikarkhane (10/3/2008)
Page faults happen when a thread being processed doesnt find the required data in the physical memory and has to page it into the memory.Your problem looks like a memory pressure issue.
Check the PageFaults/sec and Pages/Sec counters in the Perfmon. Values should be less than 50 or 100.
Run Sql Profiler and see if you can find queries with CPU count > 1000. Try to optimize these
Also refer to this forum discussion for page faults ...
Hi,
I'm a bit stuck on this issue.
I wasn't collecting pagefaults/sec and pages/sec at the time that I wrote this message. I started using page output/sec per this article: http://support.microsoft.com/kb/555223 which is really low (0.16).
However, Committed Pages stays about 7GB while Memory: Available Bytes is about 0.5GB.
Key Locks and RID Locks are extremely high (60,000 during one point).
Full Scans are very low.
Page Life Expectancy drops when the locks go up.
Disk Time on the array where the production DB lives has reached peaks of 275.
I ran a query show what the wait types in SQL Server and pageiolatch_sh, pageiolatch_dp and pageiolatch_up kept showing up.
This database is using NetApp. According to the consultant it can handle 7500 IOPS. I haven't been able to figure out if we're exceeding this extremely large threshold. It seems like this is root of our performance issue but I'm not 100% sure.
As the system waits on the disk, locks are held longer...
BTW, we tuned all of the queries that consume the most CPU. Some of the queries that show up have not had any issues in the past. I'd like to say before Netapp was installed but I'm not quite sure and I don't want to go down that path if I'm not sure.
October 8, 2008 at 12:37 pm
I checked back with the counters that I'm grabbing and I do have pages/sec (spoke too soon). I just had to add it to my report.
Pages/sec on average is about 4.5.
October 8, 2008 at 8:25 pm
Going by High Keylocks and RID,
1. check if your stored procs are explicitly using ROWLOCK table hints while accessing the tables ( tip : search syscomments or generate script for SPs and find ).
2. check if the indexes on your tables have ALLOW_PAGE_LOCK enabled. If this is disabled then it interferes with Lock escalation and queries have to grab row level locks.
Questions:-
1. what is the size of databases ?
I am thinking on the lines that if you have big tables and big joins happening then with 8GB of memory its quite obvious that you will get page faults as the server has to page out to make space for internal tables while joining. Hence later causing page faults.
October 8, 2008 at 9:03 pm
Nikhil Shikarkhane (10/8/2008)
Going by High Keylocks and RID,1. check if your stored procs are explicitly using ROWLOCK table hints while accessing the tables ( tip : search syscomments or generate script for SPs and find ).
2. check if the indexes on your tables have ALLOW_PAGE_LOCK enabled. If this is disabled then it interferes with Lock escalation and queries have to grab row level locks.
Questions:-
1. what is the size of databases ?
I am thinking on the lines that if you have big tables and big joins happening then with 8GB of memory its quite obvious that you will get page faults as the server has to page out to make space for internal tables while joining. Hence later causing page faults.
The production DB is 30GB.
I do see some tables with Allow Page Lock disabled. I started to enable them.
I'll also see if any of the sp's are using the rowlock hint.
Thanks,
James
October 9, 2008 at 4:14 am
I enabled the allow page locks on all of the indexes.
I searched sys.syscomments and I couldn't find any sp's that used the rowlock hint.
I'm gathering the performance counters again to see if I see some improvements.
October 9, 2008 at 7:34 am
I have seen a number of my client's servers where the wmiprvse.exe process routinely exhibits excessive paging. If anyone can tell me why this may be occurring (or a way to monitor/analyze WHY it is paging) PLEASE let me know!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 9, 2008 at 9:33 am
TheSQLGuru (10/9/2008)
I have seen a number of my client's servers where the wmiprvse.exe process routinely exhibits excessive paging. If anyone can tell me why this may be occurring (or a way to monitor/analyze WHY it is paging) PLEASE let me know!!
It looks like I was having issues on multiple fronts. I got some info about this process from here: http://www.computing.net/answers/windows-xp/wmiprvseexe-is-killing-my-pc/101319.html
I brought this to the attention of our system's admin who in turn installed the latest NIC drivers and sure enough the page faults virtually disappeared. There were multiple wmiprvse.exe processes running but the one that showed the extreme page faults was using the NETWORK SERVICES user name which is what lead me to believe that there was some truth in that article.
I'm still having trouble with extensive key locks. I made sure that all indexes had the use page locks check enabled. This eliminated the RID locks but increased the amount of page locks. I can't find any row lock hints except within the build in MS Report Server sp's.
Disk time is still at levels beyond comprehension.
October 9, 2008 at 3:20 pm
Well the network card thingi was good to know.
Now you have 2 problems
1. Lotsa key locks
2. High Disk Reads
If your processes are acquiring locks and keeping them for long time then you need to revisit the stored proc code to make the transactions as small as possible. With DML statements getting executed on the server, you are bound to see key locks but that should be momentarily and should disappear. I would pass on it if its not causing any blocking issues.
Secondly, run profiler to see disk read offenders.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply