Pages/sec really high, slow performance

Question

Post reply

Pages/sec really high, slow performance

Luke79

SSCrazy

Points: 2571
More actions
June 6, 2007 at 8:04 am

#173974

Recently we noticed that sometimes during the day the pages/sec counter hits really high (around 1100). During this time some of the queries time out or run for much longer than expected (10 times longer). The disk counters on the other hand remain really low. I was wondering what the issue might be. Also available memory is ok. Statistics were updated.
Does this mean that our HD config is bad? It is RAID5, two volumes, one for the OS the other one for the db. Tempdb is running on the same volume as the main database, which is having issues

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

you could try moving the tembdb to another volume. RAID5 takes a hit on writing and this could be the issue

Luke79 SSCrazy Points: 2571 More actions · Answer 2

That's what we've been planning on doing. Perhaps it will happen soon. Another question is: why would it stop suddenly? In other words the problem goes away after an hour. The traffic on the server remains to be about the same throughout the day, except for the nights, early morning and evenings.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

There are a vast array of things that can be causing occassional paging, including driver/os bugs (of which I ran into one of each recently!). A bit tough to solve this one via a forum unfortunately.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Are you looking at sp_Who2 during these times? You need to find the code or process that's grabbing resources and see if you can change it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Hammar Ten Centuries Points: 1084 More actions · Answer 5

"Also available memory is ok."

How do you know that? More importantly do you know if SQL Server has enough RAM?

Luke79 SSCrazy Points: 2571 More actions · Answer 6

SQL server can go up to 6.5GB of RAM, but was using around 6 at the time. The server has 8GB total

Linda Wenglikowski Old Hand Points: 381 More actions · Answer 7

We have a 64bit 2003 sp1 and SS2K5 Enterprise w/ sp2 running. We too saw pages/sec exceeding Microsoft's recommend 20 pages/sec average.

We've identified two problems that are contributing to this. Terminal Service connections has a known bug when connections are open on a 64bit server and our HP server utilities were from an older release. After running all the updates Microsoft recommended, page/sec is averaging between 12 - 16 unless a Terminal Services session is open.

Prior to the installation of recommended windows patches, we were seeing errors in the application event log saying something was stealling more pages than normal from SQL Server. So check you event logs and have your SEs open a case to make sure your server is running the latest patches and any known applications that drive up the number of pages/sec.

Good Luck!

Linda

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

1) if you are running 64 bit HP boxes, they have a VERY nasty bug in their integrated Lights Out stuff that will flush RAM.

2) Win2003 server 64 bit also has a similar condition where copying large files from one drive to a slower one will flush RAM.

One of my clients got absolutely crushed by both of these on one box. Putting the two patches in place was like a magic bullet for performance!!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Linda Wenglikowski Old Hand Points: 381 More actions · Answer 9

You hit the nail on the head. Many of the HP utilities are only 32 bit and will cause a x64 bit SQL Server to page wildly. An update of the HP utilities fixed most of the problem.

Note: everyone who is running an x64 bit instance of SQL Server should be aware that just opening a Terminal Services session to the box will cause your server to page 200 - 300 pages / sec without any other activity. Microsoft Tech support is who identified this issue and I've now been able to identify anytime an operator remotes into our 64 bit SQL Servers.

Any remain 32 bit application might be the source of paging on our 64 bit SQL Servers. I've even found Emulex 32 bit drivers in use that mulitplied the typically paging you'd expect to find on a healthy SQL Server.

Thanks,

Linda - dba@cfl.rr.com

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 10

I've read (http://www.sqlservercentral.com/articles/Administering/performancemonitoringbasiccounters/1348/) that the value 20 for pages/sec is relative to the individual system. So, I guess you've got to have some baseline to which you compare the "high" values.

Chris

Luke79 SSCrazy Points: 2571 More actions · Answer 11

We had this problem some time ago. It was being caused by one of the CLR procs written by another company. We are now in the process of moving away from it.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 12

if you have sufficient memory on an x64 box you can run without a page file.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/