Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Weird physical disk access

    Hi,

    I have an MS SQL 2000 server running on a Proliat with two CPUs and 2GB RAM (the process actually uses 1.6GB).

    For some reason, the same queries (which return the same data) run sometimes very fast and sometimes very slow.

    I used the performance counter to find out that when the queries run fast there is little disk i/o and when they run slowly there is a lot of i/o.

    I thought it happens because the DB keeps swapping tables in and out of RAM. The problem is that these things happen in periods. I can have a few hours of slow access and then a few hours of fast access. This makes me believe that it doesn't actually put the table in the cache untill it is requested enough times. Very weird.

    HELP?

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Posts
    58

    Re: Weird physical disk access

    Originally posted by Umbro
    Hi,

    I have an MS SQL 2000 server running on a Proliat with two CPUs and 2GB RAM (the process actually uses 1.6GB).

    For some reason, the same queries (which return the same data) run sometimes very fast and sometimes very slow.

    I used the performance counter to find out that when the queries run fast there is little disk i/o and when they run slowly there is a lot of i/o.

    I thought it happens because the DB keeps swapping tables in and out of RAM. The problem is that these things happen in periods. I can have a few hours of slow access and then a few hours of fast access. This makes me believe that it doesn't actually put the table in the cache untill it is requested enough times. Very weird.

    HELP?

    Thanks.

    No bright ideas right away, but first thing I'd do is turn on SHOW EXECUTION PLAN and make sure these runs are always using the same plan. Just how complex is this query?

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Weird physical disk access

    It also depends on what's running in the machine when the query was executing.

  4. #4
    Join Date
    Jan 2004
    Posts
    2

    Well...

    I've done some more work on the matter and found out the disk activity is the DB swapping data pages between memory and HD.

    The reason it does this is because it ran out of available RAM (this phenomena does not occur when the SQLServer still has more available RAM to use).

    The DB is run on a dedicated server, nothign else running at the time.

Posting Permissions

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