Reduce Disk I/O when writing to MS-SQL Server database

Hi All,
An application is writing to the SQL Server database for every few
millisec. The disk I/O LED is continuously glowing when writing to the
database. Is there a problem with the configuration of the database ?.
How do i reduce the disk i/o when writing to the database. the
application is writing to the database using the 'insert' sql statement.Hi
Just a guess.
Check out that dusring the inserting you don't have page splits.
I think you have RAID5 disk configuration which is probably not the best
choice for SQL server because it imposes
a significant IO hit on write performance (both data and parity info must be
written)
<vanisathish@.gmail.com> wrote in message
news:1125888842.735920.102570@.z14g2000cwz.googlegroups.com...
> Hi All,
> An application is writing to the SQL Server database for every few
> millisec. The disk I/O LED is continuously glowing when writing to the
> database. Is there a problem with the configuration of the database ?.
> How do i reduce the disk i/o when writing to the database. the
> application is writing to the database using the 'insert' sql statement.
>|||I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
Harddisk. There are so many other threads running on the system. And
the application that writes to the sql database use the 'insert' sql
statement. do i need to use SQL stored procedure for this ?
Normally what is the approach that will be used to write to the sql
database from a VC++ application using ODBC|||Hi
SQL Server's perfomance will benefit from using stored procedures instead of
'insert' statements from the client
http://www.sql-server-performance.c..._procedures.asp
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||Every transaction need to do physical I/O, otherwise, you couldn't trust the
data to be persisted
when you do commit. You can reduce the amount of I/O by combining several mo
difications in one
transaction, but you also need to consider transaction semantics as well as
blocking.
Make sure you don't have ODBC tracing turned on, btw...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||How much data are you writing? And how long does it take? Adding another
drive might help if you put the log on it, but running heavy operations on a
single processor, single disk, low memory system with lots of other stuff
running is not likely to run better. This can be made even worse if you
happen to have a very fragmented disk or (and this is the killer) you need
more than a GB of RAM and you have to do virtual memory paging.
As far as stored procedures, if you are doing many insert statements, a
stored procedure might help, but if it is just a single statement taking a
long time, then it is not likely.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||Actually i am not writing to much of data to the database. I will be
writing around 50 - 100 bytes of data to 9 tables. This writing happens
very often to some of the tables of the order of few millisec.
Currently i'm running a IIS web-server and some other application
servers which continuously polls some devices for every 250 msec
I see the harddisk LED glowing continuously, when the data logging is
ON ie. when writing to database happens.
As i said earlier the desktop PC is having 1GB of RAM and 60 GB
harddisk and is running win XP
I'm thinking of running the database server on a separate PC. Will that
solve my problem ?|||On 5 Sep 2005 00:59:38 -0700, vanisathish@.gmail.com wrote:
>Actually i am not writing to much of data to the database. I will be
>writing around 50 - 100 bytes of data to 9 tables. This writing happens
>very often to some of the tables of the order of few millisec.
SQLServer will insist on doing at least TWO physical writes to the
disk for every transaction, one for the log, the other for the real
database.

>Currently i'm running a IIS web-server and some other application
>servers which continuously polls some devices for every 250 msec
>I see the harddisk LED glowing continuously, when the data logging is
>ON ie. when writing to database happens.
Try using perfmon to get more accurate data, the LED stays on long
enough for you to see, even if the disk is busy only for a fraction of
that time.

>As i said earlier the desktop PC is having 1GB of RAM and 60 GB
>harddisk and is running win XP
Best SQLServer performance requires putting the log on a separate
physical disk, perhaps you begin to see why!

>I'm thinking of running the database server on a separate PC. Will that
>solve my problem ?
It may, but you should also consider getting a big, fat server-class
machine and not a desktop.
You might also consider writing some middleware that accumulates your
data for a couple of seconds at a time before writing it.
You haven't said anything about the schema of the tables you're
writing to, do you mean you always write to 9 tables? Do they have
PKs? What kind of PKs? Any other indexes? Any other DRI?
All that said, what the heck, let the little light stay on, that's
what it's there for!
But again, get some better numbers with perfmon and profiler.
J.|||On 5 Sep 2005 00:59:38 -0700, vanisathish@.gmail.com wrote:
(snip)
>I'm thinking of running the database server on a separate PC. Will that
>solve my problem ?
Hi vanisathish,
What exactly IS your problem? The only thing I've seen so far is that
your Disk I/O LED is glowing continuously - frankly, I'd be much more
worried if it did not glow! Why do you think that this is a problem?
SQL Server writes modifications to disk in two ways. The actual data in
the tables is cached - changes are written to the internal memory
structures only; the changes are flushed to disk at regular intervals or
when the server is shut down. But in order to prevent data loss on power
outage and other disasters, all changes are also written (in a compact
format) to the transsaction log - and these writes are NOT cached (and
they should not be, because you'd otherwise still lost this data on a
power failure).
If your database processes changing data at millisecond intervals,
you'll be writing to the log file in millisecond intervals.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Can you please help me to find out what information should i collect
using the perform and profiler.
I collected the Avg Disk Queue length and the % processor time. the
Avg.Disk Queue length is always on top of the graph(100%). The %
processor time is also high around 95 in the graph. most of the time it
stays at 30 - 35.
I'm trying to figure out why this is happening. please bear with me.
i'm quite new to database
About my schema, actually the 9 tables are independent tables. and each
of them have a primary key. And the server application will keep
writing to these tables