| Author |
Topic |
|
quocvuong2005
Starting Member
8 Posts |
 Posted - 2007-12-24 : 04:00:39
|
| Hi everybody !!! I have a problem with database SQL server 2000, and I need anyone help me. I have PC machine with hardware configuration following: + RAM : 512M + CPU : 2.4 GHz (intel Pentium 4) + System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 1 (this is call Machine1 ) and other Server machine with hardware configuration the following : + RAM : 2G + CPU : 3.6 GHz( intel Xeon) +System : Microsoft Windows Server 2003 Enterprice Edition , Service Pack 2 (use RAID 5) (this is call Machine2 ) .And SQL Server 2000 is installed in these machine. I created one table and one script to insert several records into this table. and script for inserting: declare @count int , @max int set @count =1 set @max = 5000 WHILE (@count < @max or @count = @max) BEGIN insert into TBAT_STOCKEOD(TRDT,SEQNO,COMPID,TRANSTYPE) values('20071219',cast(@count as varchar(5)),'13215','1') set @count = @count +1 END I execute this script on Machine1 and Machine2 : + Machine1 : spent 3 seconds. + Machine2 : spent 30 seconds. I don't understand why Machine2 is slower than Machine1 ( while configuration of Machine2 is better than Machine1's ). I don't know what happen . I hope that anyone help me. Thanks!!! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-24 : 16:22:22
|
| Did you compare resource usages between them? |
 |
|
|
quocvuong2005
Starting Member
8 Posts |
Posted - 2007-12-24 : 21:19:07
|
quote:
Originally posted by rmiao
Did you compare resource usages between them?
Thank you for your reply!!!Machine1 and Machine2 are executed the same script. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-24 : 21:36:31
|
| How about cpu and memory usage? Checked disk i/o? What are total db size and user connections on those servers? |
|
|
|
quocvuong2005
Starting Member
8 Posts |
Posted - 2007-12-24 : 21:55:19
|
quote:
Originally posted by rmiao
How about cpu and memory usage? Checked disk i/o? What are total db size and user connections on those servers?
Now, Both Machine1 and Machine2 are used for Test. So, there is only one user connect to Machine2. Default configuration SQL server of them is same.I used commands sp_helpstats N'TBAT_STOCKEOD', 'ALL'ALTER DATABASE LINKSVRORA SET AUTO_CREATE_STATISTICS ON to view and auto create statistics on table Machine2 , it work fine.But then, I restart SQL server , Machine2 is to be insert slowly. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-24 : 22:37:47
|
| You should compare resoure usages. By the way, do they have same edition of sql server and sp level? |
|
|
|
quocvuong2005
Starting Member
8 Posts |
Posted - 2007-12-24 : 22:46:14
|
quote:
Originally posted by rmiao
You should compare resoure usages. By the way, do they have same edition of sql server and sp level?
Both Machine1 and Machine2 are installed SQL Server 2000 Enterprices and Service Pack 4. |
|
|
|
quocvuong2005
Starting Member
8 Posts |
Posted - 2007-12-25 : 03:29:39
|
quote:
Originally posted by rmiao
You should compare resoure usages. By the way, do they have same edition of sql server and sp level?
I used commandSET STATISTICS TIME ONto count execution time of script.And result following : +Machine1 :SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 1 ms.SQL Server Execution Times:CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms,elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(1 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(1 row(s) affected)...+Machine2:SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(1 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 8 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected)SQL Server Execution Times: CPU time = 0 ms, elapsed time = 5 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.(1 row(s) affected)...I don't know what happen with Machine2. and How do I configurate CPU to Machine2 work fine ? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-25 : 14:33:00
|
| Should check resource usages in perfmon. |
|
|
|
quocvuong2005
Starting Member
8 Posts |
Posted - 2007-12-25 : 20:30:02
|
quote:
Originally posted by rmiao
Should check resource usages in perfmon.
I checked resource usages in perfmon when SQL server execute script and result following :Machine1: Proccessor used 100%whereas,Machine2 used physical Disk 100%Is there the way to configurate SQL server to improve speed writting of hard disk? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-25 : 22:54:08
|
| How do you put db files on disk on machine 2? Did you separate them in their own disk array? |
|
|
|
quocvuong2005
Starting Member
8 Posts |
Posted - 2007-12-25 : 23:27:17
|
quote:
Originally posted by rmiao
How do you put db files on disk on machine 2? Did you separate them in their own disk array?
Machine2 containt two logic disk (C & D) :C : use RAID1D : use RAID5and Data Files and Log File is at D. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-26 : 22:46:38
|
| To get better performance, should separate db data files from log files and put tempdb on its own disk array. |
|
|
|
quocvuong2005
Starting Member
8 Posts |
Posted - 2007-12-27 : 20:29:52
|
quote:
Originally posted by rmiao
To get better performance, should separate db data files from log files and put tempdb on its own disk array.
Thanks for your reply !!!I moved Log File,tempdb to C( use RAID1) and Data File to D(use RAID5).I used 'HP Array Diagnostic Utility' sofware to diagnostic configuration hardware and result following :Read Cache Size: 32 MBytes ....SCSI Port 2, Drive ID 0 Vendor Id: COMPAQ Product Id: BD0728A4C4 Product Rev: HPB4 Vendor Specific: 3KT4KXWK Serial Number: 3KT4KXWK00007643M1N8 SCSI Inquiry Header: 00 00 03 12 8b 00 01 3e Device Supports: Tagged Command Queueing Linked Commands Synchronous Data Transfer 16-bit Wide Data Transfer Drive Type: 0x00 Parallel SCSI Block Size: 512 bytes/sector Total Blocks: 142255808 sectors/disk Reserved Blocks: 1088 reserved sectors/disk SCSI Inquiry Bits: 0x3E Stamped for M&P: yes Last Failure Reason: 0x00 (Drive has not failed) Phys Drive Flags: 0xcd 0x25 0x9d 0x07 Drive present and operational Wide SCSI transfers Enabled Ultra320 SCSI Enabled S.M.A.R.T. Supported S.M.A.R.T. Enabled Quick Arbitration and Selection Capable Quick Arbitration and Selection enabled Arbitration Fairness enabled Configured as part of Logical Drive Drive write cache is currently disabled Drive write cache setting is changeable and safe SCSI LUN: 0 Spi Speed Rules: 0x64a10800 Physical Connector: J2 (controller connector attached to drive) Physical Box on Bus: 0 (number of the physical enclosure in which drive resides) Physical Bay in Box: 0 (number of the physical drive bay in the enclosure) .....Does write cache effect to speed writting of hard disk ? |
|
|
|
|
Slow insert in SQL Server 2000