Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Optimal pagefile.sys for running SQL Server 2000

    We have four workstations with SQL Server 2000 running on WinXP. The four have 256 MB RAM and the current pagefile.sys file is set to 372 MB (about 1.5 times the RAM, the minimum size allowed, I think). I was wondering if it would be any use upgrading this to for example 900 MB. What do you think? Would it help to have SQL Server finish everlasting queries faster?
    The 900 MB are a quick guess, but if you have a better suggestion please drop a line.

    Thank you in advance
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Optimal pagefile.sys for running SQL Server 2000

    256 MB RAM is barely enough to run Windows XP, with SQL Server running on it it's definitely too little. Increasing the page size won't help much, it'll just mean more swapping between RAM and disk. I'd strongly suggest getting more RAM - say 1 GB per machine, at the least.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    Cincinnati, Ohio, USA
    Posts
    7,089
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    I seem to recall that SQL does not use the paging file, but I may be way off base too. Either way, Hans is correct: SQL would benefit from RAM, not page file space. If you want to change the page file settings, be careful that you don't limit the size of them too much - 1.5 times installed RAM is not necessarily the best fit. I doubt you would see any tangible benefit to performance by changing the page file settings, anyway.
    -Mark

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    I admit I don't understand much of SQL Servers, but if you wanted your queries to complete faster, wouldn't it be better to make the Server itself faster?

    Or, find some way to redesign the query so it runs faster?

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    And yes, you do need more memory in those windows XP machines.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 2000

    Hello Hans,

    We'll try to get some more memory for these boxes. Thanks for the advice!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  7. #7
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    <hr>I admit I don't understand much of SQL Servers, but if you wanted your queries to complete faster, wouldn't it be better to make the Server itself faster?<hr>
    Glad if I could. How would you go about that? If it's a matter of upgrading hardware, we're stuck. This is a very small organization and even getting RAM upgrades can be an issue.

    <hr>Or, find some way to redesign the query so it runs faster?<hr>
    Sometimes they can be redesigned, but sometimes the problem is not the design itself but the volume of data to process. I don't know what is considered "normal": our databases have tables with 100.000 to 5.000.000 records.
    The "everlasting queries", as I called them in my first post, aren't really so, but if there were a way to reduce the half hour it takes to complete the average query I would be very happy <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  8. #8
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,585
    Thanks
    5
    Thanked 1,059 Times in 928 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    Does each workstation have one or more databases stored on the workstation? What kind of disk storage is on each workstation? Are there queries running against networked databases?

    Joe
    Joe

  9. #9
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    Yes, we have databases stored on each workstation (they are not duplicates). The HDs are 38 GB big.
    Sometimes the need arises to run a query against a networked database in another workstation. However, the time to completion I mentioned is related to queries to local databases, so network speed shouldn't be interfering here.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  10. #10
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,585
    Thanks
    5
    Thanked 1,059 Times in 928 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    Are there multiple HDs per workstation?

    Joe
    Joe

  11. #11
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    No, each workstation has a single HD <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  12. #12
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,585
    Thanks
    5
    Thanked 1,059 Times in 928 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    Since you can't really tweak the setup to optimize disk access, then as was stated before RAM is your best bet. With any XP system 512MB is the minimum to do anything worthwhile. More likely with SQL server running 1 GB would be a better bet.

    Joe
    Joe

  13. #13
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Optimal pagefile.sys for running SQL Server 20

    Yes, everything seems to point to an RAM upgrade.

    Thanks for your help!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •