z***y
发帖数: 7151 | 1 An company did the following in order to migration database from old server
to new server:
1. On old server, detach all users databases.
2. Then detach SAN from old server.
3. On new server, attach this SAN.
4. Then run script to attach databases.
Around 4:00AM today, they started getting memory dump every a few minutes.
Error Message:
SQL Server Assertion: File: , line = 385 Failed Assertion = '0'
Too many parallel nested transactions. This error may be timing-related. If
the error persists after rerunning the statement, use DBCC CHECKDB to check
the database for structural integrity, or restart the server to ensure in-
memory data structures are not corrupted.
OLD server:
Quad Quad core, 64GB RAM
NEW SERVER:
Quad 8-core, 256GB RAM
By looking at old settings, I saw one setting was changed:
the old MAXDOP is 1 now it's zero.
Also, the max memory setting on new server is 256GB
Was it the root cause? 要完整回答。大家投票 回答最好的， 我给100wb |
g***l
发帖数: 18555 | 2 attach了还要DBCC CHECKDB之类的吧，是2008 ENTRERPISE R2吗？ |
g***l
发帖数: 18555 | 3 我GOOGLE了一下，是这个
There are a couple of parallization bugs in SQL server with abnormal input.
OPTION(MAXDOP 1) will sidestep them. |
z***y
发帖数: 7151 | 4 兄弟， 都系捏了， 这种回答不讲究啊。
.
【在 g***l 的大作中提到】
: 我GOOGLE了一下，是这个
: There are a couple of parallization bugs in SQL server with abnormal input.
: OPTION(MAXDOP 1) will sidestep them.
|
g***l
发帖数: 18555 | 5 我觉得长QUERY的瓶颈基本上在IO，SAN的速度是很慢的，有时候还不如REGULAR HARD
DRIVE. QUERY PARALLISM帮助并不大，反而容易出错
【在 z***y 的大作中提到】
: 兄弟， 都系捏了， 这种回答不讲究啊。
:
: .
|
a9
发帖数: 21638 | 6 但原来用san没问题的吧。
【在 g***l 的大作中提到】
: 我觉得长QUERY的瓶颈基本上在IO，SAN的速度是很慢的，有时候还不如REGULAR HARD
: DRIVE. QUERY PARALLISM帮助并不大，反而容易出错
|
g***l
发帖数: 18555 | 7 原来设的不是1么，SUPRESS parallel plan generation了
0 - Uses the actual number of available CPUs depending on the current system
workload. This is the default value and recommended setting. 1 - Suppresses
parallel plan generation. The operation will be executed serially. 2-64 -
Limits the number of processors to the specified value. Fewer processors may
be used depending on the current workload. If a value larger than the
number of available CPUs is specified, the actual number of available CPUs
is used.
【在 a9 的大作中提到】
: 但原来用san没问题的吧。
|
i****a
发帖数: 36252 | 8 不東. 圍觀...
dump file 發給微軟 tech support?
server
'
If
【在 z***y 的大作中提到】
: An company did the following in order to migration database from old server
: to new server:
: 1. On old server, detach all users databases.
: 2. Then detach SAN from old server.
: 3. On new server, attach this SAN.
: 4. Then run script to attach databases.
: Around 4:00AM today, they started getting memory dump every a few minutes.
: Error Message:
: SQL Server Assertion: File: , line = 385 Failed Assertion = '0'
: Too many parallel nested transactions. This error may be timing-related. If
|
B*****g
发帖数: 34098 | 9 曾哥是看你们谁是可造之材，然后收了当小弟
【在 i****a 的大作中提到】
: 不東. 圍觀...
: dump file 發給微軟 tech support?
:
: server
: '
: If
|
z***y
发帖数: 7151 | 10 Anyone can give an complete answer? |
|
|
g***l
发帖数: 18555 | 11 还有一个问题SQL Server 2008 R2, Standard Edition maxes out at 64GB of memory
.Windows Server 2008 R2 Standard只支持32GB，最好查清楚是什么版本，以前的公司
就出过这种事，弄了个STANDARD的WINDOWS，结果上了MEMORY没用。LOL |
i****a
发帖数: 36252 | 12 我知道
"dump file 發給微軟 tech support" 就是我的答案, haha
【在 B*****g 的大作中提到】
: 曾哥是看你们谁是可造之材，然后收了当小弟
|
g***l
发帖数: 18555 | 13 出错的好像都是SQL SERVER 2005,说SP3就FIX了，不知真假，APPLY LATEST SERVICE
PACK，哈哈 |
z***y
发帖数: 7151 | |
y****w
发帖数: 3747 | 15 谁说san很慢阿。
【在 g***l 的大作中提到】
: 我觉得长QUERY的瓶颈基本上在IO，SAN的速度是很慢的，有时候还不如REGULAR HARD
: DRIVE. QUERY PARALLISM帮助并不大，反而容易出错
|
s**********o
发帖数: 14359 | 16 是很慢，现在都是SSD DATABASE MIRRORING取代SAN的CLUSTER，或者干脆把TEMPDB放入
内存 |
z***y
发帖数: 7151 | 17 The root cause were the MAXDOP setting and the max memory setting.
The new box has more CPU cores. MAXDOP was set to 0 which means SQL Server
can use up to all cores, it's enterprise edition btw. Due to using multiple
cores, many memory was consumed for holding interim result for each thread.
When checked the wait status we can see huge amount of suspended sessions
with CXPACKET wait type.
To get things worse, the max memory is the same as physical memory size. So
memory was kept allocated until there's no memory left. Then the whole sql
server get hung. Critical processes cannot get enough memory.
server
'
If
【在 z***y 的大作中提到】
: An company did the following in order to migration database from old server
: to new server:
: 1. On old server, detach all users databases.
: 2. Then detach SAN from old server.
: 3. On new server, attach this SAN.
: 4. Then run script to attach databases.
: Around 4:00AM today, they started getting memory dump every a few minutes.
: Error Message:
: SQL Server Assertion: File: , line = 385 Failed Assertion = '0'
: Too many parallel nested transactions. This error may be timing-related. If
|
s**********o
发帖数: 14359 | 18 没经过TEST乱改PRODUCTION参数的DBA会被FIRE掉的 |
