Database Problems after Moving to other Server
-
Hi,
Being new to administration I've a big problem. Our developer system (WIN 2003 Server/SQL2000) is configured thesame way our testsystem is. Hardware is nearly the same.
Importing data to the database takes about 1 hour on dev system, but 20 hours on test system.
All operations take about 2000% of the time it should take. The test system is not under heavy load or something like that. Databases and configuration is the same.
Any idea why it slows down so heavily?
Thx for any answer.
-
I/O ? Disks,..
How about the filesizes for the databases, are they the same, do they extend the same.
Has the new db been allocate at least the same size as the current old db ?
After the load you should dbcc dbreindex all loaded objects !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Database filesize is the same on both system, they extend in the same way.
I think dbcc dbreindex can't be the way for me, because of the follwoing situation:
I create a couple of empty temp tables, fill them with data from an external import process. After finishing the import, i drop the live tables and replace them with the temp tables. This is what i do on both systems, dev and test, so i think dbreindex won't bring much performance.
-
... Hardware is nearly the same ...
- What are the HW differences ? (ram, disk,..)
- how about filefragmentation (disk level) ?
- If you just create empty objects and load with data, then a dbreindex will not do any good because there are no indexes to be optimized and a heap is just a pile of pages....
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Ok, I'll give you an overview of the hardware currently used...
Dev System:
- Windows Server 2003 Standard Edition
- SP 1
- 1.5 Gb RAM
- 3.00 GHz DualCore
- 100 MBit Network
Test System:
- Windows 2003 Standard Edition
- SP 1
- 2.0 Gb RAM
- 3.2 GHz DualCore
- 100 MBit Network
On both systems i've running SQL Server 2000 in the following version:
- SQL Server Developer Edition
- Version 8.00.2039 (SP4)
No fragmentation of Databasefile/Log in dev system and test system. Overall Fragmentation is on both system 80%. After running defragmentation, the time for import data in the "slow" test system is as it was before
Do you need any other informations from me?
-
So far, so good
how about disksubsystems ?
number of disks ?
type of disks ?
cache ?
rpm ?
write performance ? (ms)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
"fast" dev system:
- single disk, contains only sql databases
- Seagate ST3808110AS Barracuda 80 GB
- 7200 RPM
- 8,5 ms
- S-ATA II
- 8 MB Cache
"slow" test system:
- contains only sql databases
- SATA Raid1, 2 disks
- same as above
May the raid be the reason for the decreasing performance? As it looks, this seems to be the only big difference between the two systems.
-
Is that software or hard raid?
Only time I have seen that radical difference in performance between two very similar systems someone had turned on software compression on the slow system in the OS...
-
It's a hardware raid, no software is used...
-
Have you compared the write cache options on both servers?
Use Device Manger, Disk Drives, select a disk, right click ==> properties and then select the "Disk Properties" tab. Is "write caching enabled" on ?
Note that you MUST disable "write caching enabled" or your database may become corrupt and not recoverable.
See "Using hard disk controller caching with SQL Server" at http://support.microsoft.com/kb/46091/
Once you disable write caching on ATA or SATA drives, you will see thruput drop by a factor of between 10 to 50.
Time to get some SCSI disks.
SQL = Scarcely Qualifies as a Language
-
Writing cache is enabled on the "fast" developer system, so it is on the "slow" system. I'll check if there is an performance increase when disabling write cache on the raid of the "slow" system...
thx so long for our answers...
-
Have you compared query plans on something that performs differently?
-
I found checking the query plans that the INSERT's seem to cause the trouble.
On "fast" devsystem, i've 0-1 duration and 0-1 reads
On "slow" testsystem, 've about 16-20 duration and ~20 reads
So it looks as if the INSERT takes much more time to complete an the testsystem. But i cant find a bottleneck anywhere.
-
Does your table has indexes ? If yes, drop them. After the table is loaded, then create the indexes.
-
as well as indexes, make sure your stats are up to date on the slow server.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply