Performance Problems after Porting Database
-
Hi,
I'm new to SQL Server Administration and i've got a big problem.
I've designed a database on Windows 2003 Server/SQL Server 2000 with a lot of stored procedures used by an application over network.
First i had absolute no performance problems importing data to the database on our developer system. After i moved the database to another SQL server with exactly the same setup, my import time increases by 2000%. On dev system, the import takes about 1 hour, on the other system it's about 20 hours.
The setup and configuration of the database is exactly the same, the hardware is nearly the same. I can't find the problem...
Anyone an idea or ever had the same problem?
Thx for every answer
-
If everything truely is the same from the old server to the new, the first thing I would check is the physical connection of the server to the network among a few other things. It is quite possible that there are settings, configurations, etc, that aren't part of SQL server that could affect performance. but check for simple things like:
1) Is the new server plugged into the same switch that the old one was?
2) Are the hard drives configured in the same manner as on the old server? (raid config, speed, etc)
3) Are the data files in the same locations (tempdb separated from your other data files, data files separated from your log files, etc)
Forgive me if these are steps you've already confirmed, but it is always best to start with making sure the server is plugged in and then work up from there.
-G
-
1) Yes, both servers are plugged into the same switch
2) Hard drives configuration is different. It looks this way :
"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
3) all data/log files are on that disc at both systems
-
I'd check the network card settings, speed and duplex/half duplex.
are the o/s and sql editions identical, cpu same + speed. memory same, server configs the same?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
How did you move the database?
Did you backup and restore over to the new server?
Or did you detach, copy the files and attach them on the new server?
I did the latter 2 years ago and was told by Microsoft that you need to update statistics if you detach and attach. It solved my problem.
-
I'll echo Al, if you're moving databases about it's always a good move to run through at least a statistics update and a dbcc updateusage. I'll sometimes rebuild all indexes - depends on time etc. but always stats and usage.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
colin has the best advice I've seen. Usually things aren't quite the same when there's a restore. I'd definitely rebuild indexes to defragment things and be sure you get the optimum query plans.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Hmm,
I detached, moved and attached the database. I'll try to rebuild indexes and update statistics. Hope this will help me a bit.
I'll give feedback if i've success, thanks so far for the replies.
-
No success. But I don't think rebuilding indexes or update statistics will help me, because of the import process.
Every day there starts an .NET application, reading data from an access database and importing it into fresh and new temptables in the SQL server. Having finished the complete import, the "old" and out of date tabels will be dropped and the temptables will be renamed.
What i've seen so far, the import application itself is about 2 times slower reading data from the access database. Could it be an out of date component? I'am using OleDB to read out the data.
Furthermore the connection to the SQL database is realized with SQLConnection object. It's about 10 times slower an the server than on the developer system.
But the real time sink is in executing 3 stored procedures which manipulate the importet data in the SQL server.
So far it seems everything runs much slower on the SQL server, the stored procedures and the import application itself.
-
Anyone has some ideas ?
-
I'll repeat myself - at a bare minimum update stats and run dbcc updateusage after restoring/attaching a database. Other than thta I'd check network bandwidth , ntfs fragmentation, I'd also compare the query plans.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Updatet stats, dbcc updateusage after restoring....no effect.
in the query plans i found the following:
"slow" system : each INSERT duration 25-30, reads ~20
"fast" system : each INSERT duration 0-1, reads 0
so it looks as if the INSERT's cause the trouble. bandwith of network is ok, ntfs fragmentation too.
How can i check where the bottleneck is in the INSERT's? I import then data via .NET application and send them over network to a simple stored procedure which does the INSERT.
The crazy thing is, that the hole import works identically on both systems except performance...
I really don't know what to do...
-
Problem solved....It looks as if the INSERT takes more time to process because of the hardware raid controller cache. Now i encapsulate the queries (always 100) in transaction blocks and performance increased by 2000%.
Maybe this helps someone with the same problem...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply