Mystified by Perfmon and Task Mgr

  • OK - I'm currently admitting complete bewilderment about what I'm seeing on a database system, so I'm hoping that if I post a few facts and observations, someone may be able to offer an opinion or two ...

    Problem:

    =======

    Poor overall performance from a VLDB.

    Poor windows-level file copy performance (which is preventing me re-distributing databases to other disks)

    Architecture:

    ============

    Server:

    Windows 2003 Standard Edition - 8Gb RAM

    2 x 3.2 GHz dual-core CPU

    SQLServer:

    SQL2005 Enterprise Ed - default memory settings

    Disks:

    Physical - 1 x 16Gb local disk (C:\), plus

    NetApp SAN Storage Array (52 physical drives).

    12 Gb pagefile.

    Logical - F:\, G:\ and J:\ drives, 1Tb, 2Tb and 1Tb LUNs respectively

    Observations:

    ============

    When copying (via Windows) a 4.8 Gb data file with SQLServer running:

    1) Local copy from F: to F:

    - Copy time = 11 mins

    - Pages/sec counter = 1500 to 2500

    - % Pagefile Usage = 1.9%

    - CPU counter = 85-90%

    - Free memory (Task Mgr) = 90 Mb

    2) Local copy from F: to G:

    Same as above

    When copying (via Windows) a 4.8 Gb data file with SQLServer STOPPED:

    1) Local copy from F: to F:

    - Copy time = 11 mins

    - Pages/sec counter = 1500 to 2500

    - % Pagefile Usage = 1.9%

    - CPU counter = 85-90%

    - Free memory (Task Mgr) = 7.5 Gb

    2) Local copy from F: to G:

    Same as above

    So, stopping SQLServer has no practical effect on the efficiency of the Windows filecopy.

    What I do not understand, is WHY the file copy will not make use of the 7.5 Gb of free memory when SQLServer is stopped? Why does it carry on hard page-faulting, and since the pagefile usage seems so low, just where, exactly, is it writing the pages to disk?

  • Have you tried running the copy in safe mode??

     

    Maybe that'll allow you to finish this task ASAP.

  • Hmmm - no

    Forgive me, but this sounds a little impractical, unless you were just suggesting this as a test to prove a point? Clearly I wouldn't be able to run SQLServer in safe mode at the same time.

    If I get the opportunity, I'll give it a whirl, just out of sheer curiosity.

    I should have mentioned, BTW, that the network connections to the SAN filer all use iSCSI - I'm not actually sure whether the iSCSI initiator will work in safe mode either.

  • Looks like I read too fast... I were under the impression that you were in a rebuild of the server or something of the sort... That could have helped but obviously not in this case.

     

    Good luck !

  • Hi There,

    Not sure if this helps. Im assumeing your using windows 2003? The page file is kept on the same drive as the OS by Default and its called Pagefile.sys. On a default install it is hidden and can be shown by going to

    Tools->Folder Options->View->Hide Protected Operating System Files.

    To Change where your storing the page file go to:

    Control Panel-> System -> Advanced Tab-> settings in performace sub group-> Advanced-> Change in the virtual memory sub group.

    From here you can define the page file and where it lives.

    Im no guru on page files. but i belive that the page file is better if its on it own drive. (like most things in windows) in that dialog box you also have the option to disable the page file entirly (be careful :crazy

    All i can suggest is you have a play with a bit of trial and error from what I can tell its a bit of a black art

    Another thing is SCSI can be a fickle creature is suggest looking into it to see if there are any bottle necks.

    Also there are some odd things about the way windows will prefer to use pagefile rather than memory when the file being copied is > X size (not 100% sure about it tho)

    Thanks

    A

Viewing 5 posts - 1 through 4 (of 4 total)

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