ProLiant Servers (ML,DL,SL)
1748165 Members
3866 Online
108758 Solutions
New Discussion юеВ

Re: SQL 2000 Low Perfomance

 
Yurgin
New Member

SQL 2000 Low Perfomance

New installetion of MSSQL 2000 server
on ML370 G5 finished with trable

MSSQL 2000 server have a low perfomance
(e.g. 3000 transactions(simple INSERT) doing
above 20 sec)

Version Of SQL 8.00.2187(SP4 with hot fix)

How fix this trable?
11 REPLIES 11
Craig Wedge
Occasional Advisor

Re: SQL 2000 Low Perfomance

What is your OS?

How do you have the Disk setup? Types of RAID? how many drives? How are your databases setup? Are they on seperate Drives?
Ryan Goff
Valued Contributor

Re: SQL 2000 Low Perfomance

What kind of controller are you using? Do you have the BBWC addon? Not having a BBWC module can slow down your disk access.
Yurgin
New Member

Re: SQL 2000 Low Perfomance

OS: Window Server 2003 Standard Edition
RAID 5 Level(Speed
Read 400 MB/s Write 60 MB/s)

Test Script write in Query Analyzer
(Login: sa)
-------------------------------------------
declare @count int
select @count =3000

Test_Next:

INSERT INTO [TestTable] (Col1,Col2) VALUES (1000,'156')
select @count =@count - 1
if @count <> 0 GOTO Test_Next
--------------------------------------------
doing above 20 sec.
Why have Long Parse and Compile Time ?


Pieter 't Hart
Honored Contributor

Re: SQL 2000 Low Perfomance

No other respons yet ?
as of view for performance raid-5 is not the obvious choise. Especially if also the transactionlog is on this volume.
NB! you didn't answer the previous question about writeback cache present or not on the controller.
Many threads on this forum can be found where bbw-cache option is suggested (as first option!) for improvement.
Th system has to wait for the transction to be written to disk, without cache this isnt very fast especially with raid5, for with the controller has to perform multiple i/o.

You could also improve by using separate raid-1 (1+0) volumes for data and transactionlog.

More configuration details are missing (number of processors, memory, sql standard or enterprise)

Pieter
_Yurgin
New Member

Re: SQL 2000 Low Perfomance

CPU 2 Xeon (core dou)
RAM 4GB
SQL 2000 Standard

I do experiment: create database on IDE hard
on ML370 G5. MSSQL 2000 server work perfect!!!

You are right, may be problem in cache ratio
Into ArrayConfigaration Tools i found only one values of ratio: 100% read and 0% write
Why?

Smart Array Controller P400 have 256MB into
my configuration (FirmWare 2.08)

_Yurgin
New Member

Re: SQL 2000 Low Perfomance

BBWC is Battery Backed Write Cache Right?

Pieter 't Hart
Honored Contributor

Re: SQL 2000 Low Perfomance

That's right.
If this option is not present you only have read cache.
Pieter 't Hart
Honored Contributor

Re: SQL 2000 Low Perfomance

as in document http://h18004.www1.hp.com/products/quickspecs/12400_div/12400_div.html
the 256 MB model is not equipped with BBWC

HP Smart Array P400 Controller HP Smart Array P400/256MB Controller 405132-B21
HP Smart Array P400/512MB Controller with BBWC 411064-B21

_Yurgin
New Member

Re: SQL 2000 Low Perfomance

Thanks

Can BBWC module installation on SmartArray Controller
raise low perfomance of SQL Server 2000 Standard?

PS
Perfomance of MSSQL 2000 server on RAID 0
increase at 35% in comparison of RAID 5
(3000 transactions[simple INSERT] doing
above 14 sec).

Have SA p400 special tuning (except cache ratio)?