+ Reply to Thread
Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 25

RAMDRIVE for better performance ?

  1. RAMDRIVE for better performance ?

    Hi,

    when i am executing my long running calculation depending of a lot of
    tables and procedures (needs about 15 minutes), i can see heavy harddisk
    led activity all the time. Because i have only one disk in my pc i am sure
    it is the sql server producing this activity. But when i check the memory
    sql server is using only around 80 Mb (configuration is with unlimited
    memeory) and i always have a lot of memory free.
    So i thougth to create a ramdrive in my 2 Gb Mem and put the datase files
    (mdf/ldf) in this ramdrive - but no luck. Still heavy harddisk activity and
    maybe 1 minute faster then before.
    Then i moved my tempdb on ramdrive ("Ramdisk 9"). For your information:
    free ramdrives are not recommended for tempdb, because with them i was not
    able to start my sql server (so be carefull and think on the -f startflag
    if you try this).
    But the same behaviour. Maybe one minute faster, not more. And still heavy
    harddisk activity all the time.
    Now i wonder how to find out what really happens. Maybe i stored the wrong
    files on the ramdrive. And had somebody big performance boosts with sql
    server on a ramdrive?

    thanks,
    Helmut

  2. Re: RAMDRIVE for better performance ?

    Helmut Woess wrote:

    > when i am executing my long running calculation depending of a lot of
    > tables and procedures (needs about 15 minutes), i can see heavy harddisk
    > led activity all the time. Because i have only one disk in my pc i am sure
    > it is the sql server producing this activity. But when i check the memory
    > sql server is using only around 80 Mb (configuration is with unlimited
    > memeory) and i always have a lot of memory free.


    What if you configure it to use a fixed large amount?

    Can you post the specs for the calculation? Maybe it can be
    logically optimized.

  3. Re: RAMDRIVE for better performance ?

    Helmut Woess (user22@inode.at) writes:
    > when i am executing my long running calculation depending of a lot of
    > tables and procedures (needs about 15 minutes), i can see heavy harddisk
    > led activity all the time. Because i have only one disk in my pc i am sure
    > it is the sql server producing this activity. But when i check the memory
    > sql server is using only around 80 Mb (configuration is with unlimited
    > memeory) and i always have a lot of memory free.


    Sounds like it's something else that is hammering your disk. The first
    thing to check is maybe that your Antivirus is not trying to disinfect
    your database files...

    If that was not the ticket, get Process Monitor from
    http://technet.microsoft.com/en-us/s.../bb896645.aspx
    and examine what is going on.

    I had some problems with fairly high activity on my disk, and by using
    Process Explorer I found that the culprit was Mgmt Studio that was checking
    the server status far too often.

    Which version of SQL Server and which OS do you run?


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

  4. Re: RAMDRIVE for better performance ?

    Am Mon, 18 Feb 2008 11:05:02 -0800 schrieb Ed Murphy:

    > Helmut Woess wrote:
    >
    >> when i am executing my long running calculation depending of a lot of
    >> tables and procedures (needs about 15 minutes), i can see heavy harddisk
    >> led activity all the time. Because i have only one disk in my pc i am sure
    >> it is the sql server producing this activity. But when i check the memory
    >> sql server is using only around 80 Mb (configuration is with unlimited
    >> memeory) and i always have a lot of memory free.

    >
    > What if you configure it to use a fixed large amount?
    >
    > Can you post the specs for the calculation? Maybe it can be
    > logically optimized.


    Sorry, can't post the source. It is about 6.000 lines of code in 50 stored
    procs and UDF's and makes the monthly commission calculation for an
    insurance broker. I am sure there would be some points for optimization,
    but i just wanted to see how much a ramdrive could help. In the moment the
    result is very frustrating ...

    bye,
    Helmut

  5. Re: RAMDRIVE for better performance ?

    Another option is to capture file statistics (if 2000 then
    fn_virtualfilestats) and you ca see which files have the most reads / writes
    and I/O stalls. It's hard to believe sql server is only using 80MB, how
    large is the db? With one hard drive you can never separate the data files
    from the log files. I would try putting the tempdb log and database log
    files on the ram drive and leave the rest on the original one.

    --
    Andrew J. Kelly SQL MVP
    Solid Quality Mentors


    "Helmut Woess" wrote in message
    news:1vcqc0s9eejhc$.1bnhdrp534pme$.dlg@40tude.net...
    > Hi,
    >
    > when i am executing my long running calculation depending of a lot of
    > tables and procedures (needs about 15 minutes), i can see heavy harddisk
    > led activity all the time. Because i have only one disk in my pc i am sure
    > it is the sql server producing this activity. But when i check the memory
    > sql server is using only around 80 Mb (configuration is with unlimited
    > memeory) and i always have a lot of memory free.
    > So i thougth to create a ramdrive in my 2 Gb Mem and put the datase files
    > (mdf/ldf) in this ramdrive - but no luck. Still heavy harddisk activity
    > and
    > maybe 1 minute faster then before.
    > Then i moved my tempdb on ramdrive ("Ramdisk 9"). For your information:
    > free ramdrives are not recommended for tempdb, because with them i was not
    > able to start my sql server (so be carefull and think on the -f startflag
    > if you try this).
    > But the same behaviour. Maybe one minute faster, not more. And still heavy
    > harddisk activity all the time.
    > Now i wonder how to find out what really happens. Maybe i stored the wrong
    > files on the ramdrive. And had somebody big performance boosts with sql
    > server on a ramdrive?
    >
    > thanks,
    > Helmut



  6. Re: RAMDRIVE for better performance ?

    Am Mon, 18 Feb 2008 14:46:01 -0800 schrieb Erland Sommarskog:
    ....
    > Sounds like it's something else that is hammering your disk. The first
    > thing to check is maybe that your Antivirus is not trying to disinfect
    > your database files...
    >
    > If that was not the ticket, get Process Monitor from
    > http://technet.microsoft.com/en-us/s.../bb896645.aspx
    > and examine what is going on.
    >
    > I had some problems with fairly high activity on my disk, and by using
    > Process Explorer I found that the culprit was Mgmt Studio that was checking
    > the server status far too often.
    >
    > Which version of SQL Server and which OS do you run?


    Hello Erland,

    i checked the activity with FileMon and ProcMon, both showed me all the
    time heavy activity in writing to the database and also to tempdb, nearly
    no reads.
    I don't understand why there is so less difference in time if i run it on
    harddsik or on ramdrive (15 minutes to 14 minutes).
    Maybe with all the overhead to emulate a real harddisk, write is not really
    much faster on a ramdrive compared to a harddisk?

    bye,
    Helmut

  7. Re: RAMDRIVE for better performance ?

    Helmut Woess wrote:

    > Am Mon, 18 Feb 2008 11:05:02 -0800 schrieb Ed Murphy:
    >
    >> Helmut Woess wrote:
    >>
    >>> when i am executing my long running calculation depending of a lot of
    >>> tables and procedures (needs about 15 minutes), i can see heavy harddisk
    >>> led activity all the time. Because i have only one disk in my pc i am sure
    >>> it is the sql server producing this activity. But when i check the memory
    >>> sql server is using only around 80 Mb (configuration is with unlimited
    >>> memeory) and i always have a lot of memory free.

    >> What if you configure it to use a fixed large amount?
    >>
    >> Can you post the specs for the calculation? Maybe it can be
    >> logically optimized.

    >
    > Sorry, can't post the source. It is about 6.000 lines of code in 50 stored
    > procs and UDF's and makes the monthly commission calculation for an
    > insurance broker. I am sure there would be some points for optimization,
    > but i just wanted to see how much a ramdrive could help. In the moment the
    > result is very frustrating ...


    At least run Profiler during a run, looking for when each stored proc
    starts and stops; the ones that take the most time are the ones most
    worth attempting to improve. Also, I think things tend to bog down
    badly if you call a UDF from a WHERE clause.

  8. Re: RAMDRIVE for better performance ?

    Am Tue, 19 Feb 2008 21:39:41 -0800 schrieb Ed Murphy:

    > At least run Profiler during a run, looking for when each stored proc
    > starts and stops; the ones that take the most time are the ones most
    > worth attempting to improve. Also, I think things tend to bog down
    > badly if you call a UDF from a WHERE clause.


    Hello Ed,

    i know that UDF's are sometimes bad, but my question is not how to
    improve my stored procs than what should a ramdrive bring, if the
    performance monitor shows that in this 15 minutes 90% of the time i have
    harddisk activity always between 80% and 100%, cpu constantly around 50%
    and memory activity always nearly 0%.
    Maybe the (in XP integrated) performance monitor shows not the reality, but
    best would be if you try it yourself with the 15-day-trial from here:
    http://www.superspeed.com/ramdisk.php

    I would be very interested in your personal experience.

    thanks and bye,
    Helmut

  9. Re: RAMDRIVE for better performance ?

    I didn't catch the beginning of this thread, but perhaps waitstats can shine some light on where the
    waits are? Along with file I/O stats.

    --
    Tibor Karaszi, SQL Server MVP
    http://www.karaszi.com/sqlserver/default.asp
    http://sqlblog.com/blogs/tibor_karaszi


    "Helmut Woess" wrote in message news:3cjo3338ubf4.1entzy3lkfd0h$.dlg@40tude.net...
    > Am Tue, 19 Feb 2008 21:39:41 -0800 schrieb Ed Murphy:
    >
    >> At least run Profiler during a run, looking for when each stored proc
    >> starts and stops; the ones that take the most time are the ones most
    >> worth attempting to improve. Also, I think things tend to bog down
    >> badly if you call a UDF from a WHERE clause.

    >
    > Hello Ed,
    >
    > i know that UDF's are sometimes bad, but my question is not how to
    > improve my stored procs than what should a ramdrive bring, if the
    > performance monitor shows that in this 15 minutes 90% of the time i have
    > harddisk activity always between 80% and 100%, cpu constantly around 50%
    > and memory activity always nearly 0%.
    > Maybe the (in XP integrated) performance monitor shows not the reality, but
    > best would be if you try it yourself with the 15-day-trial from here:
    > http://www.superspeed.com/ramdisk.php
    >
    > I would be very interested in your personal experience.
    >
    > thanks and bye,
    > Helmut



  10. Re: RAMDRIVE for better performance ?

    Helmut Woess wrote:

    > i know that UDF's are sometimes bad, but my question is not how to
    > improve my stored procs than what should a ramdrive bring,


    Asking how to improve your stored procs is always a good thing. If
    you've already asked that question, and decided that that there are
    no further improvements worth the time you'd spend to implement them,
    then it makes sense to look at the hardware.

    > if the
    > performance monitor shows that in this 15 minutes 90% of the time i have
    > harddisk activity always between 80% and 100%, cpu constantly around 50%
    > and memory activity always nearly 0%.


    That CPU usage is rather higher than I'm used to, and supports (though
    does not prove) the theory that looking for low-hanging fruit in stored
    proc improvement may be worthwhile.

+ Reply to Thread
Page 1 of 3 1 2 3 LastLast