Pages/sec really high, slow performance

  • 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

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

  • 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.

  • 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

  • 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)

  • "Also available memory is ok."

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

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

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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/

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

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