May 29, 2003 at 10:53 am
Hi
I've just moved a 3rd party system from SQL 6.5 on Win NT to SQL 7 on Win 2000, on a shiny new server with bags of extra processing power, memory, etc.
Our main procedure, copying whole database from ISAM to SQL now takes about 2-3 times longer than on the old system.
We recently did a similar transport to a backup server running SQL 7/Win NT on a poky old server with very little of anything, and that runs about 3 times faster than the new one.
Oddly, the reverse process of copying from SQL to ISAM does seem to run faster on the new system.
Task manager shows SQL is hardly touching memory or processing power during these procedures. Have I overlooked something in the setup that is limiting access to resources, or otherwise slowing down writes?
Be grateful for any suggestions.
May 29, 2003 at 11:50 am
Firstly, Have you run dbcc dbreindex and statistics update to your new database?
Waht is database Compatibility Level? Upgrading existing systems with existing applications at SQL Server 7.0 ompatibility level may cause your database behaviors different than before.
Check your SQL Server configuration especially in memory configuration and the setting of max degree of parallelism.
Try to identify the indexes those procedures use and see whether correct indexes are being used.
Can you also post both old and new server hardware information such as CPUs, Memory and how do you configure disks.
May 30, 2003 at 11:29 am
Thanks for suggestions
New server as follows:
Compaq ML370G3
1 x 2.4Ghz Xeon
2.5Gb RAM
Disks—RAID5 (hardware RAID) about 70 gb available
Running Win 2000 with SQL Server 7
Old server as follows:
x86 family 6 model 5 stepping 2
AT/AT compatible
320 mb memory (may be inc page file)
Disks- Raid 5, about 20 gb available
Running WinNT4 with SQL Server 6.5
Backup Server as follows:
x86 family 6 model 7 stepping 3
AT/AT compatible
512 mb memory (may be inc page file)
Running WinNT4 with SQL Server 7
(not as poky as I thought)
Note backup server performs better than old server in all respects. New server performs better except for this one job which truncates all data and repopulates the database from a flat file. Approx times populating SQL db from flat file, and extracting flat file from SQL db are as follows:
Old Backup New
populate 1hr n/a 15 min
extract 1hr 45 min 1hr50m
I've discounted for present SQL Server settings, indexes, etc, that reflect differences between 6.5 and 7 because the backup and new servers have identical (default) SQL 7 configurations and backup server doesn't show the same problem. I assume we're looking for a cause in the differences between these 2.
Parallelism was set to 0, looking at 2 apparent processors (single hyperthreaded Xeon) - tried setting this to 1, but had no effect. (Old and Backup servers are single proc).
Our IT guy will check RAID config for Read/Write preferences setting next week - this looks like a possible cause, but I don't have access.
It's been suggested that SQL 7 might have a problem with RAID, but the example quoted involved software raid rather than hardware raid - is there likely to be an issue here?
It's Friday night here, so I'll keep looking on Monday, & let you know if we get a solution.
Thanks again
May 30, 2003 at 11:57 am
quote:
Our IT guy will check RAID config for Read/Write preferences setting next week - this looks like a possible cause,
I think you are in right direction. Another question here is you didn't mention about Disks configuration about your backup server, Is it RAID 5 too?
quote:
It's been suggested that SQL 7 might have a problem with RAID, but the example quoted involved software raid rather than hardware raid - is there likely to be an issue here?
I am not aware of about this.
How do you configure memory for new SQL Server? As you have much large memory in the new server than the backup server, suggest to allocate more memory to SQL Server in your new box.
Edited by - Allen_Cui on 05/30/2003 11:58:16 AM
May 30, 2003 at 2:36 pm
since it sounds like copying is what you do the most the raid configuration can make quite a difference especially if you are using raid 5 now. I had a similar problem.
The raid controllers have to write the data across all disks which can take longer.
June 3, 2003 at 10:53 am
Hi - back again
Thanks again for your thoughts. We've been checking out the RAID - new server has RAID5 but with no battery on the cache memory - able to accelerate reads but not writes. Old server has battery backed RAID5 with read and write accelleration equal, so we're installing a new card and will try that tomorrow. (Backup server has no raid, so maybe that's why it's faster?)
Will let you know how we get on tomorrow.
June 5, 2003 at 3:13 am
Success!
Thanks again for your help, we've now got our 2 hrs down to 16 mins using the battery/cache/accelleration. This is excellent.
Couple of academic questions:
Does anyone know why Raid5 has such a drastic effect on writing to SQL Server? Exporting the same volume of data from SQL Server to an outside flat file seems not to be affected. In fact, I notice that the export process is totally unaffected by accelleration.
Also, I noticed that we got all the speed benefits from setting read/write accelleration to 50/50. There was no additional benefit setting it to 100% write accelleration. Presumably there's some other limiting factor at work?
These are not important questions, but might be useful to know one day. Is there any literature that might cover this sort of question?
Thanks
Gibbers
June 5, 2003 at 5:30 am
Decent Q&A is given at the following link (may be information here as well)
http://www.sql-server-performance.com/q&a38.asp
You may also want to have the Server Engineer check the stripe size and make sure that it is set to 64K. Not usually the default.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
June 12, 2003 at 3:36 am
Delayed thanks to David - I've been busy getting the new server into production. Your ref was very useful, I'll explore that site further ...
Over and out!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply