SQL 2000 Low Perfomance

[Yurgin]

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?

[Craig Wedge]

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]

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]

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]

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]

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]

BBWC is Battery Backed Write Cache Right?

[Pieter 't Hart]

That's right.
If this option is not present you only have read cache.

[Pieter 't Hart]

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]

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)?

[Pieter 't Hart]

see ftp://ftp.compaq.com/pub/supportinformation/papers/tc020604tb_rev0_us.pdf

The performance of a RAID array can be subdivided into read performance and write performance, which vary based on the RAID level. For example, in RAID 1 (mirroring), the data and redundant data must be written to two separate drives. The data is normally read from one drive, so the read performance is much faster than the write performance; however, data can be read from both drives to increase the read performance. RAID 0 uses striping to improve performance by splitting up files into smaller portions (stripes) and distributing them to multiple hard disks; however, RAID 0 has no fault tolerance. RAID 5 and RAID ADG also use striping, but their write performance is significantly affected by the multiple reads and writes needed to perform the parity calculations prior to updating the array. The write performance of RAID ADG is less than that of RAID 5 because RAID ADG has dual parity overhead. The read performance of RAID 5 and RAID ADG is very good and may be improved by tweaking the stripe size.

[Pieter 't Hart]

Thats where the writeback cache comes in.
When data is written to cache. as for the OS the i/o is finished. The raid controller still has to perform the write to disk(s) in the background, but the OS (database) can submit a new write operation.

To guard for power failures before the cache is written to disk it has it's own battery. So when power is restored to the controller data can still be read from cache and written to physical disk.

A database heavily relies on "knowing" the certainty that the transactionlog record is written to "disk", but it sees no difference between disk and cache.