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.

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

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

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

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

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.

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

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

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.