请数据库高手帮忙（第４页ｕｐｄａｔｅｄ）

jl162401

楼上果然好强，呵呵！

典

原帖由 招财猫 于 2007-2-15 13:20 发表


you reckon it's an index problem? it took only half hour transfering all data from source server to my pc (not a server) or to itself.

You didn't disable the index in the target server? It's the first thing you should do.

江苏小伙子

原帖由 招财猫 于 2007-2-15 14:01 发表


No, there's no anti-virus running on target server. Network admin just told me target server uses RAID 5, while source server uses RAID 10 & RAID 6.  Would it be the reason?

Are they both using hardware RAID? Source server has 2 disks controllers. I would reckon the log files and data files are seperated into different controllers. Target server has only one disk controller. Also RAID 5 is not a good choice for write-intensive environment, especially when data files, tempdb and log files are on the same disk group.

When you copy tables using the tool, will the tables in target DB be truncated before copying?

招财猫

Yes, both server use RAID controller, and also all tables been truncated before the job running

[ 本帖最后由 招财猫 于 2007-2-15 15:20 编辑 ]

招财猫

原帖由 jl162401 于 2007-2-15 14:02 发表
楼上果然好强，呵呵！

I totally agree with you.

jl162401

兄弟，你这个问题处理得怎么样了？

招财猫

原帖由 jl162401 于 2007-2-15 15:28 发表
兄弟，你这个问题处理得怎么样了？

I hope I can work it out by next week...

招财猫

Just got these from network admin, does it make any sense?

Server B (target server)
Single 3.4 Ghz Xeon CPU
2 Gb RAM memory
Hardware Raid 5
On board 6i controller
4 x 72 Gb 10k U320 disks

Server A (source server)
Dual 3.6 Ghz Xeon CPU
4 Gb Ram memory
Hardware raid 5
6400 controller
6 x 148 Gb 15k U320 disks

Portal

关看硬件不行,让你们admin跑几个performance counter吧

江苏小伙子

原帖由 招财猫 于 2007-2-15 15:39 发表
Just got these from network admin, does it make any sense?

Server B (target server)
Single 3.4 Ghz Xeon CPU
2 Gb RAM memory
Hardware Raid 5
On board 6i controller
4 x 72 Gb 10k U320 disks
...

Wish you can resolve this issue soon. Cannot believe your company buy a computer with less CPU and less RAM for BI application.
Anyway

<1>
Have you check system logs on target server? Any information there?
Besides database, what else big applications are installed on source and target servers? Are there much difference?
As for as OS services, do source server and target server run similar services? Are there much difference?

<2>
Please download the IO benchmark tool from Microsoft, install and post the results?
http://www.microsoft.com/downloa ... &displaylang=en

<3>
How about my query? Please run it in target DB
select
    [name],
    type_desc,
    space_used_in_kb = (page_count * 8.0),
    space_used_in_mb = (page_count * 8.0 / 1024.0)
from
    sys.indexes I inner join sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) P
    on I.[object_id] = P.[object_id] and I.[index_id] = P.[index_id]

<4>
The raid configuration is different from previous post?

jl162401

从你这个配置上看，ServerＡ至少要比ＳｅｒｖｅｒＢ快４到６倍

内存小，硬盘转速慢，容量大，呵呵

典

原帖由 Portal 于 2007-2-15 16:01 发表
关看硬件不行,让你们admin跑几个performance counter吧

I don't think so.

It's should notbe  a hardware issue.

1 G is a small database.
I guess even with personal PC (with 512M RAM around), you can run the import very fast ( I guess normally not more than 30 Mins)

jl162401

就是，一定要从软件参数配置上找找原因，再烂也是个server啊

江苏小伙子

原帖由 jl162401 于 2007-2-15 16:52 发表
就是，一定要从软件参数配置上找找原因，再烂也是个server啊

It looks like.

And the server is Less powerful than my own PC. What a server!

典

原帖由 江苏小伙子 于 2007-2-15 17:05 发表


It looks like.

And the server is Less powerful than my own PC. What a server!

You PC is too strong

Any way, learn from your guys...hope you can solve the problem soon.

Because I am building a similar system with 15G data initiately. ( and will increase 5 G per year).

招财猫

原帖由 jl162401 于 2007-2-15 16:52 发表
就是，一定要从软件参数配置上找找原因，再烂也是个server啊

How?

招财猫

Wish you can resolve this issue soon. Cannot believe your company buy a computer with

less CPU and less RAM for BI application.

== Actually, source server A is a production server & target server B is a test server,

thus all applications installed on A is also installed on B, their software are totally

same. I ralised B would be slower than A, just cann't believe it can be slow like that --

takes ~7 hours to transfer 1 G data in the midnight when there's no any other application

or program running on the server.  

<1>
Have you check system logs on target server? Any information there?
Besides database, what else big applications are installed on source and target servers?

Are there much difference?
As for as OS services, do source server and target server run similar services? Are there

much difference?

== cann't see any error or warning in event log. Everything looks fine.

<2>
Please download the IO benchmark tool from Microsoft, install and post the results?
http://www.microsoft.com/downloa ... &displaylang=en

== I will post it shortly.

<3>
How about my query? Please run it in target DB
select
    [name],
    type_desc,
    space_used_in_kb = (page_count * 8.0),
    space_used_in_mb = (page_count * 8.0 / 1024.0)
from
    sys.indexes I inner join sys.dm_db_index_physical_stats(db_id(), object_id('.'),

null, null, null) P
    on I.[object_id] = P.[object_id] and I.[index_id] = P.[index_id]

== it returns ~1500 records, half of them are heap, combined with another half of

clustered index & non-clustered index.

<4>
The raid configuration is different from previous post?

== ignore the previous one please.

招财猫

原帖由 goldensun 于 2007-2-15 17:17 发表



You PC is too strong

Any way, learn from your guys...hope you can solve the problem soon.

Because I am building a similar system with 15G data initiately. ( and will incr ...

Are you a DBA as well?

典

I am Not DBA,

In the target server
How about the file size setting? ( I may set 2G as the initiate value)
How about the fill factor setting? ( I may want to set 80%)
How about the Auto Update Statistics ?You may change it and see how

江苏小伙子

<3>
How about my query? Please run it in target DB
...
== it returns ~1500 records, half of them are heap, combined with another half of clustered index & non-clustered index.

==> Are these indexes in any chance related to the tables you copied?

招财猫

原帖由 江苏小伙子 于 2007-2-16 11:14 发表
<3>
How about my query? Please run it in target DB
...
== it returns ~1500 records, half of them are heap, combined with another half of clustered index & non-clustered index.

==> ...

i think they are all related to.

江苏小伙子

原帖由 招财猫 于 2007-2-16 11:48 发表


i think they are all related to.

When you test by copying data from source DB to source DB or from source DB to your PC,
does your target DB (in this case, your source DB as well or your PC) have these indexes in the metadata?
If not, that could be the cause of your slow performance.

招财猫

-kw -t1 -s30 -b2 -i64 -bn
output:
IOs/sec:  3006.8
MBs/sec:     5.87

-kr -t1 -s30 -b2 -i64 -bn
output:
IOs/sec:  136.93
MBs/sec:    0.26

江苏小伙子

原帖由 招财猫 于 2007-2-16 12:42 发表
-kw -t1 -s30 -b2 -i64 -bn
output:
IOs/sec:  3006.8
MBs/sec:     5.87

-kr -t1 -s30 -b2 -i64 -bn
output:
IOs/sec:  136.93
MBs/sec:    0.26

Well this explains why one is much slower. That's almost 20 times slower.

Can you please on your target server check hardware manager, check the disk drives and find if write cache is disabled, if yes, please tick it to enable write cache?

[ 本帖最后由 江苏小伙子 于 2007-2-16 13:05 编辑 ]

招财猫

yes, it's unticked. however, I cannot tick it until get the permission from network admin who is away this afternoon.

招财猫

Thanks to all the replies. Although I've been struggling with this problem for the whole week, I learnt a lot of knowledge from this post & research as a reward.  Happy in hope & patient in pain, that's my one year's DBA experience.

Anyway, wish all of you guys have a nice weekend and happy chinese new year.

典

learnt a lot

草垛下的柴和鱼

原帖由 招财猫 于 2007-2-16 16:48 发表
Thanks to all the replies. Although I've been struggling with this problem for the whole week, I learnt a lot of knowledge from this post & research as a reward.  Happy in hope & patient in ...

Please let us know if "write cache" will fix your problem.

tracy@syd

我看到了真正的SQL Server高手啊，呵呵，强，江苏小伙子

seth2000

学习。

[ 本帖最后由 seth2000 于 2007-2-17 00:04 编辑 ]