Disk Time 100% during Insert Data

Hi ,
I am using SQL 2000 SP3 2 CPU (Intel II ~1230) with 4 GB RAM .
The server is data warehouse server that load data and served reports.

I am loading data every 15 minute with Balk Insert command into temporarty tables and than insert the data to two fact tables with logic implemented by store procedure.

Each time I am insert new data ( every 15 minutes , for around ~ 4 minute of ~ 250,000 lines) the Disk time is 100% .

I am using RAID 5 with logical partitions for system ,SQL Data file and SQL transaction log file .

In order to solve the Disk Time bottleneck ( the CPU is normal during the insert ~ 40 % but still the duration is too long due to the disk time problem) I though to create separate file group to each fact table and change the Disk configuration from RAID 5 to double RAID 1 ,each one for each File group ( and other disks for the transaction log and system ) .I though that in this way I will be able to use each of the physical disks rather than current RAID 5 .

Any idea ? do you know other option to solve this problem ? what is the reaon for the fact that the Data file that store on RAID 5 do not use the 5 physical Disk headers ?

Thanks in advance
EyalYou didn't mention the "C" word...but I imagine it's in play...where's the data coming from? INSERTS are a logged operation...and 250k of rows, every 15 minutes...is a lot of data...

I would try to figure out a way to perform a nonlogged operation (bulk insert?) and apply the logic against the database...

My own opinion (MOO)

What type of data is this?|||Howdy

Worth removing any indexes on the load tables & then recreating the indexes once data has been loaded?

FWIW

Cheers,

SG.|||Hi no one response regard the Disk configuration .This is my best solution to the Disk time - separate the two Fact table to different phiscal disk means working with two disk header in parallel . !!??|||And you mentioned that you're interested in speed...if you're doing insert a row at a time then that'll be slow, if you're using a cursor, that'll be slow, if your modifying data on import, that'll be slow...

If you want to split the data across attached drives, go ahead, make a patitioned view and go nuts...

Is that the root cause of your problem?

Hard for us to tell until the mind reading machine comes back online...|||I agree with Brett 100%...more info required please

Cheers,

SG

Disk capacity and CPU/memory utilisation question?

Folks,
This is a dumb question I know but I'm a newbie and require your
help. Can anyone answer the following 2 questions concerning SQL
Server 2000:
1. How do you check for disk capacity on a SQL Server 2000 box?
2. How do you look at a log of CPU/memory utilisation and the
processes running at that time?
Any commetns/ideas/suggestions - much appreciated.
Thank you,
ColmYou check disk capacity the same as with any other box...you can use
perfmon, etc... To see how much logical space is available INSIDE the
physical files, sp_spaceused will do it...
In order to save and look at processes that were running at a particular
time, you would run perfmon and save the output. Then you may go back and do
reports on specific time periods... There are also 3rd party monitoring
tools available to you..
Microsoft also has MOM. I forget what the acronym stands for, but it is an
operational tool for monitoring windows based machines. AND there is an
add-in for SQL Server as well... This is a sku'd product which you must
purchase.
If you wish to get into this in a BIG, do it yourself kind of way, take a
look at WMI. A microsoft scripting language which allows you to do many,
many different kinds of things..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.com...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm|||In addition to Waynes wonderful response you might find these useful:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.com...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm|||Al,
In addition to Andrew's and Waynes replies, you may wish to have a look
at some SQL Server stored procedures.
exec master..xp_fixeddrives
exec master..sp_monitor
I've found these useful in the past.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Al Murphy wrote:
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm

Disk capacity and CPU/memory utilisation question?

Folks,
This is a dumb question I know but I'm a newbie and require your
help. Can anyone answer the following 2 questions concerning SQL
Server 2000:
1. How do you check for disk capacity on a SQL Server 2000 box?
2. How do you look at a log of CPU/memory utilisation and the
processes running at that time?
Any commetns/ideas/suggestions - much appreciated.
Thank you,
Colm
You check disk capacity the same as with any other box...you can use
perfmon, etc... To see how much logical space is available INSIDE the
physical files, sp_spaceused will do it...
In order to save and look at processes that were running at a particular
time, you would run perfmon and save the output. Then you may go back and do
reports on specific time periods... There are also 3rd party monitoring
tools available to you..
Microsoft also has MOM. I forget what the acronym stands for, but it is an
operational tool for monitoring windows based machines. AND there is an
add-in for SQL Server as well... This is a sku'd product which you must
purchase.
If you wish to get into this in a BIG, do it yourself kind of way, take a
look at WMI. A microsoft scripting language which allows you to do many,
many different kinds of things..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.c om...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm
|||In addition to Waynes wonderful response you might find these useful:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.c om...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm
|||Al,
In addition to Andrew's and Waynes replies, you may wish to have a look
at some SQL Server stored procedures.
exec master..xp_fixeddrives
exec master..sp_monitor
I've found these useful in the past.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Al Murphy wrote:
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm

Disk capacity and CPU/memory utilisation question?

Folks,
This is a dumb question I know but I'm a newbie and require your
help. Can anyone answer the following 2 questions concerning SQL
Server 2000:
1. How do you check for disk capacity on a SQL Server 2000 box?
2. How do you look at a log of CPU/memory utilisation and the
processes running at that time?
Any commetns/ideas/suggestions - much appreciated.
Thank you,
ColmYou check disk capacity the same as with any other box...you can use
perfmon, etc... To see how much logical space is available INSIDE the
physical files, sp_spaceused will do it...
In order to save and look at processes that were running at a particular
time, you would run perfmon and save the output. Then you may go back and do
reports on specific time periods... There are also 3rd party monitoring
tools available to you..
Microsoft also has MOM. I forget what the acronym stands for, but it is an
operational tool for monitoring windows based machines. AND there is an
add-in for SQL Server as well... This is a sku'd product which you must
purchase.
If you wish to get into this in a BIG, do it yourself kind of way, take a
look at WMI. A microsoft scripting language which allows you to do many,
many different kinds of things..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.com...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm|||In addition to Waynes wonderful response you might find these useful:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Al Murphy" <almurph@.altavista.com> wrote in message
news:a23daf3f.0411240429.3f373918@.posting.google.com...
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colm|||Al,
In addition to Andrew's and Waynes replies, you may wish to have a look
at some SQL Server stored procedures.
exec master..xp_fixeddrives
exec master..sp_monitor
I've found these useful in the past.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Al Murphy wrote:
> Folks,
> This is a dumb question I know but I'm a newbie and require your
> help. Can anyone answer the following 2 questions concerning SQL
> Server 2000:
> 1. How do you check for disk capacity on a SQL Server 2000 box?
> 2. How do you look at a log of CPU/memory utilisation and the
> processes running at that time?
>
> Any commetns/ideas/suggestions - much appreciated.
> Thank you,
> Colmsql