Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-12-16 : 12:36:37
|
| We are receiving a lot of complaints about poor performance with an application. The only possible issue I see on SQL Server is memory. I ran perfmon for the following counters over a 30 minute window. While perfmon was running, the users said performance dropped during a 10 minute portion of this window. Below are the results.Counter AveragePage Faults/sec 6966.941Page Reads/sec 59.555Page Writes/sec .857Pages Input/sec 952.435Pages Output/sec 13.712Pages/sec 966.146We have a 2GB pagefile and only about 50MB is being used, which isn't bad. 2 - 4% paging appears to be about normal.Physical memory is 4GB, with 3GB allocated to SQL Server via the /3GB switch. The OS is Windows Server 2003 SE, SP1 and SQL is SQL Server 2000 EE with SP3a.I'm not sure how to interpret the counters, specifically Page Faults/sec. The count appears very high, yet the page file usage is very small.What are your thoughts?Thanks, Dave |
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-12-18 : 23:06:19
|
| Further investigation revealed the page faults occur every 15 minutes while a transaction log backup job is running. The backup job copies the T-Log file to a server located at our alternate data center. The job step to copy the file is what appears to be causing the page faults. Perhaps a high amount of page faults during a file copy are normal.Any thoughts?Thanks, Dave |
 |
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2005-12-19 : 07:38:35
|
quote:
Originally posted by DBADave
We are receiving a lot of complaints about poor performance with an application. The only possible issue I see on SQL Server is memory. I ran perfmon for the following counters over a 30 minute window. While perfmon was running, the users said performance dropped during a 10 minute portion of this window. Below are the results.Counter AveragePage Faults/sec 6966.941Page Reads/sec 59.555Page Writes/sec .857Pages Input/sec 952.435Pages Output/sec 13.712Pages/sec 966.146We have a 2GB pagefile and only about 50MB is being used, which isn't bad. 2 - 4% paging appears to be about normal.Physical memory is 4GB, with 3GB allocated to SQL Server via the /3GB switch. The OS is Windows Server 2003 SE, SP1 and SQL is SQL Server 2000 EE with SP3a.I'm not sure how to interpret the counters, specifically Page Faults/sec. The count appears very high, yet the page file usage is very small.What are your thoughts?Thanks, Dave
For me these numbers seem very high and strange.I am writing a newbie guide to SQL server admin atm (coming here by the end of the year) and I pencilled in: Memory: Pages/SecOn peak times the average value should not be more than 15. Normally very close to zero.You are get nearly a 1000 this seem very high.How often is your information staying in the buffer?What aboutBuffer Manager: Cache Hit RatioThis needs to be as near to 100% as possible. Above 99% is good. Buffer Manager: Page Life ExpectancyNumber of seconds a page will stay in the buffer pool. We should be looking at about 1000 seconds for our environment.(obviously the value give are from my experience and the environment I am in at the moment. You *could* in theory get more out but often not seen more and different specs/setups I imagine have different values. )Maybe that info will point you in the right direction. Although I think for these above buffer counter figures it uses averages from the last reset of SQL Server. |
|
|
|
Rovastar
Starting Member
38 Posts |
Posted - 2005-12-19 : 07:58:23
|
| Oh forgot to say the Page FAults/Sec can be a misleading value. Soft page faults can occur very oten and nothing to really worry about and the Page Faults/sec value is made up of hard and soft page faults.But the Paages/sec - the hard faults - is way too high anyway thinking about this some more. Anything over 100 is pretty insane. :-) Most admin would like it below 15-20. Something I think is wrong. Maybe even hardware.What are your Available Bytes? |
|
|
|
bakerjon
Posting Yak Master
145 Posts |
Posted - 2005-12-19 : 17:54:54
|
| Rovastar is right. Pages/secs should be below 20. I would say you have memory pressure. I have seen problems with page faults when copying files, but they are generally nothing to worry about unless you are swamping the NIC or the disk bus. I have seen copying large files cause network bottleneck before.Jon-Like a kidney stone, this too shall pass.http://www.sqljunkies.com/weblog/outerjoin |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-12-20 : 12:08:47
|
| I'll take a look at the NIC and research the disk bus for the server. I found out we are sending an appended transaction log backup every 15 minutes. The appended file is about 500MB in the morning and grows throughout the day. This process was implemented for DR prior to the company implementing a product called Doubletake. I'm going to comment-out the copy steps. They're no longer needed. I am still curious as to what is the actual bottleneck so I will look into your suggestions.Thanks, Dave |
|
|
|
|
|
|
|
|
Help understanding page faults/sec results