SQL Server Performance

Pages/Sec , general performance troubleshooting

Discussion in 'Performance Tuning for Hardware Configurations' started by Joel_GCC, Aug 22, 2007.

  1. Joel_GCC New Member

    Hi all, i've recently taken over a role of managing a SQL server. So am new to it and trying to get on top of it all.
    The first objective is performance problems. I have started to do some logging and noticed that the pages/sec seem high.
    It normally averages around 25 but just recently today has jumped to averaging over 1000.
    In task manager I can see that svchost.exe is causing a lot of page faults. But not sure if that is the same as pages/sec?
    Any help on finding what is causing the high page/sec would be much appricated. Once I get this sorted I have a few other questions :) but I think this can be a good starting priority.
    Cheers
    Joel
  2. ghemant Moderator

  3. satya Moderator

  4. Joel_GCC New Member

    Thanks for the links to read, i'll go through each of them.
    The server has 8gig of ram, SQL server is configured to use 7gb, but i've noticed that it seems stuck on 4gb.
    It mainly runs a Database for our EDMS (Avand Dataworks). Performance issues are a big problem. I've noticed the locks - average wait time averages around 800ms for a day, but spikes up to over 30,000ms sometimes.
    Few things to work through just trying to tackle one thing at a time.
    Cheers
    Joel
  5. satya Moderator

    What is the SQL Server service pack build?
  6. Joel_GCC New Member

    It's currently on SP1.
    I rebooted the SQL server last night hoping that might bring it back down, as it never used to be that high. But it's straight back up after the reboot.
    I tried taking all the other databases offline apart from the main production database incase something was running against those but that made little change.
  7. Joel_GCC New Member

    I should also mention that in perfmon the page file % used is averaging at 0.7%
    Avaliable bytes is around 2.5gb.
    That seems the concern that there is avaliable ram, the page file is hardly being used.. But heaps of paging is going on. It's pages input also, not output. If that helps.
    Cheers
    Joel
  8. Luis Martin Moderator

    I presume you have SQL server 2000.
    If I'm right, first you have to install SP4 and fix 2040.
    After that back with the news.
    BTW: Is SQL server dedicated?
    Some program is coping something to other place?


  9. Joel_GCC New Member

    Thats correct, SQL server 2000 enterprise.
    I'll apply SP4 and fix 2040 this weekend.
    It is a dedicated SQL server but its a pretty open environment here and I wouldn't be suprised if someone is running something against the server. I'm trying to lock it down but its hard when in the past lots of people have had access to it. One problem i've determined is the amount of crystal reports being ran against it during business hours and locking the tables. But that is easy to determine as it shows in current activity. Harder to know if someone is running copys etc.
  10. Luis Martin Moderator

    Please apply SP4 with 2040, and back with the situation.
    We can't said nothing with SP1.
    I insist, back again.
    Thanks,
  11. satya Moderator

  12. Joel_GCC New Member

    Hi all, ok i've applied the SP4 and hotfix mentioned.
    I also found that a user was running something against the filestore for the db and indexing all the files to a portable usb drive. So that was causing a lot of the pages/sec.
    But I just wanted to throw some current stats out there and see if it seems acceptable. If I should be measuring anything else let me know.
    These are averages
    Available MBytes - 445 (I have another 4gb of memory on order for the server)
    Pages/sec - 34
    % Disk Time - total - 8.3
    % Processor Time - 32.5
    Page Splits/sec - 3.3
    Buffer cache hit ration - 99.8
    User connections - 329
    Average wait time - 90ms
    Total server memory - 6305904kb (this is the new value I set after hotfix 2040 was applied).
    Thanks
    Joel
  13. satya Moderator

    That is the generic behaviour while doing any backup and restore opeartions on the database you would observe. Apart from this period if you see high pages/sec then you should consider checking those processes running at that time.

Share This Page