April 30, 2009 at 5:04 am
We use two SQL-Server 2005. One for Tests and the other one for production.
The production System has 32 GB Main Storage, the test System 16 GB.
To Input the Data from the Windows Filesystem into a relational Database on the SQL-Server, we use bcp.
Bcp is in a SSIS-Package, and two bcp run simultaneously.
One bcp loads a very large file (more then 3 GB), the other bcp loads more then 100 small files between 1KB and 2 GB.
Now to the problem.
On our Test-Server this process runs 41 minutes for bcp (with the large File) and 63 minutes for the other one.
On our Production-Server this process runs 66 minutes for bcp (with the large File) and 98 minutes for the other one.
The processess run during the night and are the only processess that are active.
Do you probably have a idea why the the process on the prod-Server is slower than the prozess on the test-Server ?
best regards
DVE
April 30, 2009 at 6:02 am
can provide the hardware specification for both systems ?
RAM , CPU, etc...
Also the sp_configure server settings for both systems ?
-:-)
Cheer Satish 🙂
April 30, 2009 at 6:18 am
The values sp_config are on both systems identicly (see below).
[font="Courier New"]
MinMax Config_ValueRun_Value
allow updates 0100
clr enabled0100
cross db ownership chaining 0100
default language 0999911
max text repl size (B) 021474836476553665536
nested triggers 0111
remote access 0111
remote admin connections 0100
remote login timeout (s) 021474836472020
remote proc trans 0100
remote query timeout (s) 02147483647600600
server trigger recursion 0111
show advanced options 0100
user options 03276700
[/font]
RAM is 32 GB (prod) 16 GB (test)
CPU: I think they are on both systems identicly, but for a correct answer I have to ask the experts for the server.
Regard
April 30, 2009 at 9:15 am
Here are some points to help you get started:
Do you have the Transaction log with too many VLFs ?
Is the recovery more of the DB set to BULK_LOGGED at bcp time?
Are you experiencing autogrows ?
* Noel
May 4, 2009 at 4:15 am
I now have looked for the CPU
On Production-Server: Intel Xeon(R) CPU 5130 2 GHz
On Test-Server: Intel Xeon(TM) 2,8 GHz
The properties of the Database are on both Systems identical.
The recovery is for both system set to "SIMPLE" (no bulk_logged).
I have found no information about VFL, where can I see more about this (you see, I am a rookie)
regards
DVE
May 4, 2009 at 12:47 pm
Here is some info about verifying/correcting the number of Virtual Log Files:
http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx
* Noel
May 5, 2009 at 4:21 am
Thanks for the Link.
I'll checked the numbers of VLF and it's 585.
I will shrink this on Friday (next offline-Time) and will reply the effect on next Monday.
regards
May 11, 2009 at 3:31 am
Last Friday I have following the steps for shrinking the VLF-Logging and to change the increase-parameter for autogrow. Changing from 10% to 1GB (because the LOG-File is at the End more then 20 GB).
But now i see no effect. The bcp is so slow as it was before I made the changes.
After this, I think that the CPU is the Key. The CPU for our production-Server has 2 GHz and for the test-Server it's 2.8. So I think that this make parallel processing on the test-server faster. Even the rate 2GHz to 2.8 GHZ = 40% is quiet the same in minutes 60 Minutes on the test-server and 90 Minutes on the production-server = 50 %.
Am I wrong, or are there more information where I can see, why our production-server ist slower then our test-server.
regards
DVE
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply