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

jl162401

lZ问题解决了没有，我等着看下文呢，呵呵！

招财猫

没有，还没找到慢的原因

大飞熊

下载service pack,update到最新.我在sql2000碰到过类似的问题

jl162401

在serverA上创建新库，加载的时间也很长吗？

招财猫

原帖由 jl162401 于 2007-2-13 13:00 发表
在serverA上创建新库，加载的时间也很长吗？

从A COPY这些TABLE到A的另一个INSTANCE，只需要半小时

北风

我觉得可能是server b 的硬盘的问题，安装 / 重装一下主板的SATA驱动试试看？

招财猫

现在的情况是：
从A数据库到B的FLAT FILE---5分钟
从A数据库到A数据库的另一个INSTANCE --30分钟
从A数据库到B数据库--6小时

jl162401

希望明天可以解决，祝你好运！

招财猫

原帖由 jl162401 于 2007-2-13 16:31 发表
希望明天可以解决，祝你好运！

有啥建议没？

jl162401

要说建议，太多要写得了，我觉得楼上几个说的Replicated的方式不知道你觉得如何，如果可以自动取增量，是个好办法。

如果不能采用上述办法，我还是建议回头关注ServerB的性能问题，看看是否是硬件配置问题，还是数据库参数设置等方面找原因，
其实如果Server B 上有1G数据，不妨一试，呵呵。

要下班了，看来只能明天搞了，这边不好OT的，要在国内，我肯定找几个兄弟加班搞定了，呵呵，咱们在这边要慢慢来的。

江苏小伙子

原帖由 招财猫 于 2007-2-13 16:34 发表


有啥建议没？

One thing to add:

Tuning is a process. If 6 hours is totally acceptable in the foreseeable future, then leave it.
As I can see, you need just to do report once a day. 6 hours is still quick compared to 24 hours a day.

招财猫

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


One thing to add:

Tuning is a process. If 6 hours is totally acceptable in the foreseeable future, then leave it.
As I can see, you need just to do report once a day. 6 hours is still qui ...

问题是这只是很小的一部分数据，今后还要继续往里加，时间会越来越长，而且这么长的时间显然是不正常的

袋鼠

原帖由 招财猫 于 2007-2-12 22:20 发表
1，加trigger我也考虑过，但是怕影响performance，因为公司的数据库比较大，有100多个db，基本上同时有300多人在线，传输到dw的70个table,基本上时时都在更新，如果使用trigger，会大大损害performance.

2，22 ...

大的数据库应该是比较重要的, 为什么record update date没有呢? 这个update date几乎一般重要一点的table都有. 不然怎么知道谁什么时候改过数据?

seth2000

Try some thing you could do.

1. Drop all index before you upload to server B.   
2. Drop all table before you upload to server B.
3. Make sure that server B database auto shrink  uncheck, give 2G to database and log file.
4. Use  Profile monitor server B, you could find what happend or just send the result to MS.
5. Use backup and restore. Just copy the whole server A to server B. It should be less than 1 hour.

招财猫

试过了，从B到B大约10+小时，看来问题在B上，B的内存和CPU都比A少一半，但速度会差这么多吗？

江苏小伙子

原帖由 招财猫 于 2007-2-14 09:53 发表
试过了，从B到B大约10+小时，看来问题在B上，B的内存和CPU都比A少一半，但速度会差这么多吗？

Since you don't have system performance issue.
(1) CPU usage should not be very high.
(2) Check the Memory allocation for the database.
(3) Check the layout your disks. Do they use raid? How have they been set up? Are they local disks or NAS.
(4) Split your data files and log files into seperate disk controllers.

By the way, it's running SQL Server 2000 or SQL Server 2005. What's OS? What's file system being used?

招财猫

The system is win server 2003 (NTFS) + sql server 2005

CPU activity is relatively low and well within acceptable limits (~30%)

Using dbcc command to check memory, Available Paging File is 309797 (~2G), Target is 83352 (~0.64G), compare with total memory usage (1.61G), its a bit low. But Page Life Expectancy is alway higher than 1000, that's why I reckon the system doesn't have memory pressure.

江苏小伙子

原帖由 招财猫 于 2007-2-14 13:32 发表
The system is win server 2003 (NTFS) + sql server 2005

CPU activity is relatively low and well within acceptable limits (~30%)

Using dbcc command to check memory, Available Paging File is 309 ...

Is your server A also running SQL Server 2005? If yes, are both SQL Server 2005 dbs on server A and B at same patch level?
Are there 32 bit or 64 bit SQL Server 2005?

What's output of DBCC MEMORYSTATUS?

招财猫

Yes, both servers are 32 bit with win 2003 & SQL Server 2005+sp1
the result of dbcc memorystatus is as following:
Buffer Counts                  Buffers
------------------------------ --------------------
Committed                      23306
Target                         36262
Hashed                         13964
Stolen Potential               238024
External Reservation           0
Min Free                       64
Visible                        36262
Available Paging File          258474

江苏小伙子

原帖由 招财猫 于 2007-2-14 14:06 发表
Yes, both servers are 32 bit with win 2003 & SQL Server 2005+sp1
the result of dbcc memorystatus is as following:
Buffer Counts                  Buffers
------------------------------ ------ ...

Should be a whole lot more than this. What about underlying disk layout.

江苏小伙子

原帖由 招财猫 于 2007-2-14 14:06 发表
Yes, both servers are 32 bit with win 2003 & SQL Server 2005+sp1
the result of dbcc memorystatus is as following:
Buffer Counts                  Buffers
------------------------------ ------ ...

How much memory on Server A and B?
How much memory you allowed for databases on Server A and B?

what's output of the following query?

Select  wait_type,  waiting_tasks_count,  wait_time_ms
from    sys.dm_os_wait_stats   
where    wait_type like 'PAGEIOLATCH%'   
order by wait_type

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

招财猫

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


Should be a whole lot more than this. What about underlying disk layout.

how to check underlying disk layout, i don't think it's in the memory status result

江苏小伙子

原帖由 招财猫 于 2007-2-14 16:20 发表


how to check underlying disk layout, i don't think it's in the memory status result

No it's not. A quick way is to check whoever built this machine. You should have documents on this as well.

If not. Depending on whether it's hardware RAID or software RAID if it's RAID, you can check software RAID in computer manangement of control panel. Hardware RAID is a bit hard to read. The only way I know is when booting the machine, log into RAID controller BIOS.

I took it that the hard drives involved are not network drives.

Can you please provide the result of the query I provided?

招财猫

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


How much memory on Server A and B?
How much memory you allowed for databases on Server A and B?

what's output of the following query?

Select  wait_type,  waiting_tasks_count,  wait_time_ ...

Server B has 2G Ram, I didn't do anything to push SQL to use more memory.
Server A has 4G Ram,  AWE has been enabled, so SQL is able to use up to 3G

the result of the query on each server:

Server A
wait_type                                                    waiting_tasks_count  wait_time_ms
------------------------------------------------------------ -------------------- --------------------
PAGEIOLATCH_DT                                               0                    0
PAGEIOLATCH_EX                                               339578               1365890
PAGEIOLATCH_KP                                               0                    0
PAGEIOLATCH_NL                                               0                    0
PAGEIOLATCH_SH                                               1578950              5732625
PAGEIOLATCH_UP                                               12637                28234

Server B
wait_type                                                    waiting_tasks_count  wait_time_ms
------------------------------------------------------------ -------------------- --------------------
PAGEIOLATCH_DT                                               0                    0
PAGEIOLATCH_EX                                               4174                 87671
PAGEIOLATCH_KP                                               0                    0
PAGEIOLATCH_NL                                               0                    0
PAGEIOLATCH_SH                                               3864                 26734
PAGEIOLATCH_UP                                               4961                 98921


There is so much to learn.....

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

江苏小伙子

原帖由 招财猫 于 2007-2-15 09:12 发表



Server A has 2G Ram, I didn't do anything to push SQL to use more memory.
Server B has 4G Ram,  AWE has been enabled, so SQL is able to use up to 3G

the result of the query on each server ...

I would reckon Server A is your taget DB host since you mentioned that its RAM is half of source server.

"High values for both PageIOLatch_ex and PageIOLatch_sh wait types can indicate IO subsystem issues".

I would recommend:
(1) Run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) on target server
     This will clear counters in sys.dm_os_wait_stats
(2) Run the job to copy 70 tables from source server.
(3) Run the query and record it.
(4) If PageIOLatch_EX and PageIOLatch_SH are still high. You need to know how the disks are built. You will
     probably need to download some I/O benchmark tools to compare your source and taget servers. Certain
     RAID will have detrimental effect on concurrent writes.

招财猫

Yeah, It's in wrong order. I've amended my post and hope maken it clear.

On target server B,  a few mins after job running, PageIOLatch_EX & PageIOLatch_SH increased to ~6k & 3k respectively, and is continuing to grow. Does it indicate the IO problem?

btw, on Server A, IO is also averaging an extremely high value, does it show a serious problem on the system.

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

江苏小伙子

原帖由 招财猫 于 2007-2-15 10:43 发表
Yeah, It's in wrong order. I've amended my post and hope maken it clear.

On target server B,  a few mins after job running, PageIOLatch_EX & PageIOLatch_SH increased to ~6k & 3k respect ...

a few min --> ~6k & 3k           It should be OK.
But if possible, seperate your data files, log files and tempdb into differenct disk controllers.

Can you please also run following query on source and target DBs?

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]

招财猫

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


a few min --> ~6k & 3k           It should be OK.
But if possible, seperate your data files, log files and tempdb into differenct disk controllers.

Can you please also run following ...

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.

江苏小伙子

原帖由 招财猫 于 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.

Just reckon! Also on your target server, do you have virus scan software running at the backend? If you have, disable it and try.

招财猫

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


Just reckon! Also on your target server, do you have virus scan software running at the backend? If you have, disable it and try.

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?