Performance degradation writing to a SAN / building a cube

Is anyone aware of performance degradation writing to a SAN over fibre optic network? We r using SQL Server 2000 (Enterprise version) with Compaq Proliant DL580 G2, writing to Compaq SAN. Is any network tuning needed? A gigabit ethernet card pushes the data over to the SAN.

Also, we r taking about 37 hours building a cube for a database barely 15GB. Database is shipped by Microsoft and is used by their product Microsoft Proect Server. So, database level tuning is out of question.

Any help would greatly be appreciated.

Thanx in advance,

BhushanYou should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...

*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?

basically it comes down to finding the bottleneck and then fixing it.|||Originally posted by Pigeon
You should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...

*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?

basically it comes down to finding the bottleneck and then fixing it.

Thanx a lot, Pigeon. I did check the Queue length originally; but was told by Compaq that the Queue length reported by Windows 2000 (for Compaq hardware) is not reliable for various reasons.

Anway! I personally think NIC card is slow and thats why I am seeing NETWORKIOWAIT within SQL Server. Just wanted to confirm, if I am not missing anything...

Thanx again.

- Bhushan|||The NIC can be a big bottleneck. WHat protocol does it use to send the data across the network? UDP, TCP, is it using a SCSI miniport driver (Encapsulating scsi calls into a udp or tcp packet) Many times, if this driver or protocol is not working, it will cause an increase in processor utilization, or the disk queue will be high. No matter what compaq tells you, if your queue is high, something is stopping the traffic from getting to the disk. Good Luck with it. I know these types of problems can sometimes be a real pain to find.

Performance collapse on writing to database

Hello!
We have an application that stores online measurement data in a MSDE
database.
Some statistics: 20 x 1kb per minute.
We had no problems for several months at many locations.
On some of our systems we saw that the SQL Server took very much of the
memory and some of the customers complaint about this. So we limitted the
maximum server memory of SQL Server to half of the available RAM memory
(most of the machines had 1GB RAM).
After running the SQL Server then for a while we saw that the writing to the
database got slower and slower and in the end it took about 30 Seconds and
more for a single INSERT. After stopping and restarting the SQL Server
everything worked fine again.
The time for reading out of the database was never influenced.
Can anybody explain this to me?
Is there a way to avoid it?
I hope on many useful answers!
Thanks!
what is the sqlserver instance doing in 30 seconds? Is it waiting on IO? Or
blocked by something? select from sysprocesses as a start. There are some
good information on the web for analyzing blocking issues.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:OGWumWSgFHA.2700@.TK2MSFTNGP15.phx.gbl...
> Hello!
> We have an application that stores online measurement data in a MSDE
> database.
> Some statistics: 20 x 1kb per minute.
> We had no problems for several months at many locations.
> On some of our systems we saw that the SQL Server took very much of the
> memory and some of the customers complaint about this. So we limitted the
> maximum server memory of SQL Server to half of the available RAM memory
> (most of the machines had 1GB RAM).
> After running the SQL Server then for a while we saw that the writing to
the
> database got slower and slower and in the end it took about 30 Seconds and
> more for a single INSERT. After stopping and restarting the SQL Server
> everything worked fine again.
> The time for reading out of the database was never influenced.
> Can anybody explain this to me?
> Is there a way to avoid it?
> I hope on many useful answers!
> Thanks!
>

Performance collapse on writing to database

Hello!
We have an application that stores online measurement data in a MSDE
database.
Some statistics: 20 x 1kb per minute.
We had no problems for several months at many locations.
On some of our systems we saw that the SQL Server took very much of the
memory and some of the customers complaint about this. So we limitted the
maximum server memory of SQL Server to half of the available RAM memory
(most of the machines had 1GB RAM).
After running the SQL Server then for a while we saw that the writing to the
database got slower and slower and in the end it took about 30 Seconds and
more for a single INSERT. After stopping and restarting the SQL Server
everything worked fine again.
The time for reading out of the database was never influenced.
Can anybody explain this to me?
Is there a way to avoid it?
I hope on many useful answers!
Thanks!what is the sqlserver instance doing in 30 seconds? Is it waiting on IO? Or
blocked by something? select from sysprocesses as a start. There are some
good information on the web for analyzing blocking issues.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:OGWumWSgFHA.2700@.TK2MSFTNGP15.phx.gbl...
> Hello!
> We have an application that stores online measurement data in a MSDE
> database.
> Some statistics: 20 x 1kb per minute.
> We had no problems for several months at many locations.
> On some of our systems we saw that the SQL Server took very much of the
> memory and some of the customers complaint about this. So we limitted the
> maximum server memory of SQL Server to half of the available RAM memory
> (most of the machines had 1GB RAM).
> After running the SQL Server then for a while we saw that the writing to
the
> database got slower and slower and in the end it took about 30 Seconds and
> more for a single INSERT. After stopping and restarting the SQL Server
> everything worked fine again.
> The time for reading out of the database was never influenced.
> Can anybody explain this to me?
> Is there a way to avoid it?
> I hope on many useful answers!
> Thanks!
>sql

Performance collapse on writing to database

Hello!
We have an application that stores online measurement data in a MSDE
database.
Some statistics: 20 x 1kb per minute.
We had no problems for several months at many locations.
On some of our systems we saw that the SQL Server took very much of the
memory and some of the customers complaint about this. So we limitted the
maximum server memory of SQL Server to half of the available RAM memory
(most of the machines had 1GB RAM).
After running the SQL Server then for a while we saw that the writing to the
database got slower and slower and in the end it took about 30 Seconds and
more for a single INSERT. After stopping and restarting the SQL Server
everything worked fine again.
The time for reading out of the database was never influenced.
Can anybody explain this to me?
Is there a way to avoid it?
I hope on many useful answers!
Thanks!what is the sqlserver instance doing in 30 seconds? Is it waiting on IO? Or
blocked by something? select from sysprocesses as a start. There are some
good information on the web for analyzing blocking issues.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:OGWumWSgFHA.2700@.TK2MSFTNGP15.phx.gbl...
> Hello!
> We have an application that stores online measurement data in a MSDE
> database.
> Some statistics: 20 x 1kb per minute.
> We had no problems for several months at many locations.
> On some of our systems we saw that the SQL Server took very much of the
> memory and some of the customers complaint about this. So we limitted the
> maximum server memory of SQL Server to half of the available RAM memory
> (most of the machines had 1GB RAM).
> After running the SQL Server then for a while we saw that the writing to
the
> database got slower and slower and in the end it took about 30 Seconds and
> more for a single INSERT. After stopping and restarting the SQL Server
> everything worked fine again.
> The time for reading out of the database was never influenced.
> Can anybody explain this to me?
> Is there a way to avoid it?
> I hope on many useful answers!
> Thanks!
>

perform all the actions in one pass rather than take serveral call

You're going to spend far less time writing a stored procedure that creates
one account and iterating through your array in your client application
calling the stored proc.
"E B via webservertalk.com" wrote:

> My application has one web form where admin can add as many users as
> desired and at the end when he press SaveChanges I want to add all of them
> to database, There should be some way to pass arrays to stored procedure..
I
> am looking for it or something similar to this...
> I want to pass an array into a stored procedure so that it can perform all
> the actions in one pass rather than take serveral calls to the server.
> I'm working with (SQL Server, .NET)
> --
> Message posted via http://www.webservertalk.com
>Alien2_51 wrote:
> You're going to spend far less time writing a stored procedure that
> creates one account and iterating through your array in your client
> application calling the stored proc.
>
bs
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||to Bob Barrows [MVP] what does it mean ?
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> to Bob Barrows [MVP] what does it mean ?
Oh, I'm sorry. it's short for:
I don't believe that what you said is necessarily correct.
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Ok, however i think i solve the problem , i used the link:
http://www.sommarskog.se/arrays-in-sql.html
what do u think'
Message posted via http://www.webservertalk.com|||E B via webservertalk.com wrote:
> Ok, however i think i solve the problem , i used the link:
> http://www.sommarskog.se/arrays-in-sql.html
> what do u think'
Well, there are several options in that link. Hopefully you satisfied
yourself that you picked the optimum one for your situation.
BTW, my comment was not directed at you. it was directed at the comment that
looping through the array in the client and making multiple calls to a
procedure ... across processes ... would be quicker than making a single
call to a stored procedure.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"