g***l
发帖数: 18555 | 1 MAX I/O WAIT ON TEMPDB为什么总是很高呢，SQL 2008, SAN, 30G内存，是有很多
STORED PROCEDUR RUNNING,不过TEMPDB 的I/O总是在30 MS以上，一天平均下来好几百
，微软说5+就算高了。 |
i****a
发帖数: 36252 | 2 is your tempdb on the same spindle as data and log files? see if you can
separate them. I know with SAN it's hard to tell/manage but that's one
thing to look at.
another thing to try is multiple tempdb files.
【在 g***l 的大作中提到】
: MAX I/O WAIT ON TEMPDB为什么总是很高呢，SQL 2008, SAN, 30G内存，是有很多
: STORED PROCEDUR RUNNING,不过TEMPDB 的I/O总是在30 MS以上，一天平均下来好几百
: ，微软说5+就算高了。
|
a9
发帖数: 21638 | 3 把tempdb搞到内存里算了。
几百
or
【在 i****a 的大作中提到】
: is your tempdb on the same spindle as data and log files? see if you can
: separate them. I know with SAN it's hard to tell/manage but that's one
: thing to look at.
: another thing to try is multiple tempdb files.
|
S***k
发帖数: 370 | 4 How to do it?
Could you please give some pointers?
【在 a9 的大作中提到】
: 把tempdb搞到内存里算了。
:
: 几百
: or
|
g***l
发帖数: 18555 | 5 TEMPDB和MDF在一个RAID上，LOG在一个RAID上，老板做了16个TEMPDB FILE,一个CPU一
个，我知道不对，应该是1/2或1/4，不过也不能这么高吧，我测了侧，主要是WRITE慢
，READ没有问题。
【在 i****a 的大作中提到】
: is your tempdb on the same spindle as data and log files? see if you can
: separate them. I know with SAN it's hard to tell/manage but that's one
: thing to look at.
: another thing to try is multiple tempdb files.
|
a9
发帖数: 21638 | 6 虚拟硬盘，呵呵。
【在 S***k 的大作中提到】
: How to do it?
: Could you please give some pointers?
|
i****a
发帖数: 36252 | 7 Just thougt of something... You said you were looking at max wait. Did you
look at average or monitor real time wait? may need to look at few more
counters for analysis.
【在 g***l 的大作中提到】
: MAX I/O WAIT ON TEMPDB为什么总是很高呢，SQL 2008, SAN, 30G内存，是有很多
: STORED PROCEDUR RUNNING,不过TEMPDB 的I/O总是在30 MS以上，一天平均下来好几百
: ，微软说5+就算高了。
|
y****w
发帖数: 3747 | 8 是什么性质的应用呢？oltp 5%很高，但是olap的话5%很多时候都可以说挺理想了，
【在 g***l 的大作中提到】
: MAX I/O WAIT ON TEMPDB为什么总是很高呢，SQL 2008, SAN, 30G内存，是有很多
: STORED PROCEDUR RUNNING,不过TEMPDB 的I/O总是在30 MS以上，一天平均下来好几百
: ，微软说5+就算高了。
|
g***l
发帖数: 18555 | 9
【在 i****a 的大作中提到】
: Just thougt of something... You said you were looking at max wait. Did you
: look at average or monitor real time wait? may need to look at few more
: counters for analysis.
|
g***l
发帖数: 18555 | 10 我用DMV做了TEMPDB AVG IO STALL TIME,还是很高，平均在200MS以上，有时候一天平
均下来在1000以上，怎么会这么慢，READ IO STALL TIME没有问题，都是WRITE的问题
，RAID是10，也不会慢这么多。MDF的数据文件有时候会有MAX IO HIGH，但平均一天不
会超过50的。
【在 i****a 的大作中提到】
: Just thougt of something... You said you were looking at max wait. Did you
: look at average or monitor real time wait? may need to look at few more
: counters for analysis.
|
|
|
w****b
发帖数: 118 | 11 1.Check if your tempdb lun is srdf. If yes, You do not need srdf for tempdb
lun.
if this is not cluster server, You might consider put tempdb in local.
2.Check the raid type, raid10 is better. Storage guy will argue raid 50 and
raid 10 are the same.
3.You might want to enable T1118 flag if you see a lot of waitresource on
PFS or sgam page.
4.Add more spindles to the tempdb lun to improve the i/o performance.
【在 g***l 的大作中提到】
: MAX I/O WAIT ON TEMPDB为什么总是很高呢，SQL 2008, SAN, 30G内存，是有很多
: STORED PROCEDUR RUNNING,不过TEMPDB 的I/O总是在30 MS以上，一天平均下来好几百
: ，微软说5+就算高了。
|
g***l
发帖数: 18555 | 12 *是OLTP的SERVER，不过网站用很多的STORED PROCEDURE PULL DATA，因为每一CLICK都
要RUN SP，BUSINESS SERVER CACHE好多,每隔一个小时REFRESH一次
*2 nodes cluster
*去看了一下，没有什么设置可以调，用的是DELL MODULAR DISCK STORAGE MANAGER
显示ARRAY IS OPTIAML
你说的这些都不知道哪里去改，T1118 flag force to use uniformed extent,我觉得
帮助不大，Add more spindles to the tempdb 还是多弄几个FILE吧，我已经有16个
FILE了，MS有的说一定要1 CPU-1TEMP FILE,还有说最多8个的。
tempdb
and
【在 w****b 的大作中提到】
: 1.Check if your tempdb lun is srdf. If yes, You do not need srdf for tempdb
: lun.
: if this is not cluster server, You might consider put tempdb in local.
: 2.Check the raid type, raid10 is better. Storage guy will argue raid 50 and
: raid 10 are the same.
: 3.You might want to enable T1118 flag if you see a lot of waitresource on
: PFS or sgam page.
: 4.Add more spindles to the tempdb lun to improve the i/o performance.
|
w****b
发帖数: 118 | 13 srdf is on san side, check with your storage admin, you do not need srdf for
temp.
2005/2008 has some enhancement, you do not need 1 file per cpu, generally 1/
2 or 1/4 cpu cores are good. In a rare scenario, add more files might help (
for example 2files per cpu). However, the scenario is rare and you need test
it in dev/qa first.
How many concurrent session you have?
【在 g***l 的大作中提到】
: *是OLTP的SERVER，不过网站用很多的STORED PROCEDURE PULL DATA，因为每一CLICK都
: 要RUN SP，BUSINESS SERVER CACHE好多,每隔一个小时REFRESH一次
: *2 nodes cluster
: *去看了一下，没有什么设置可以调，用的是DELL MODULAR DISCK STORAGE MANAGER
: 显示ARRAY IS OPTIAML
: 你说的这些都不知道哪里去改，T1118 flag force to use uniformed extent,我觉得
: 帮助不大，Add more spindles to the tempdb 还是多弄几个FILE吧，我已经有16个
: FILE了，MS有的说一定要1 CPU-1TEMP FILE,还有说最多8个的。
:
: tempdb
|
g***l
发帖数: 18555 | 14 我们这管STOREAGE就是看看SETTING OPTIMAL就不管了，说没问题
IO一天高一天低也看不出规律。明显地WRITE要慢很多，TEMPDB DAILY AVG IO WAIT
一直很高，有个别天都上好几千，也没订单进来，1/2号根本就没有几个ORDER
max number of concurrent connections =0
default connction options nothing
cast threshold for parallellism =5
max degre of parallelism=0
server 64 bit 2008 version 10.0.2351
memory min 1G max 30G
use AWE to allocate memory
for
1/
(
test
【在 w****b 的大作中提到】
: srdf is on san side, check with your storage admin, you do not need srdf for
: temp.
: 2005/2008 has some enhancement, you do not need 1 file per cpu, generally 1/
: 2 or 1/4 cpu cores are good. In a rare scenario, add more files might help (
: for example 2files per cpu). However, the scenario is rare and you need test
: it in dev/qa first.
: How many concurrent session you have?
|
z***y
发帖数: 7151 | 15 I had the same issue with one of my client. THere's hotfix for SQL Server
2008 on CDC. Do you have CDC on? |
g***l
发帖数: 18555 | 16 CDC是啥啊，我不知道啊，Change Data Capture (CDC)?怎么ENABLE啊
【在 z***y 的大作中提到】
: I had the same issue with one of my client. THere's hotfix for SQL Server
: 2008 on CDC. Do you have CDC on?
|
n*w
发帖数: 3393 | 17 64位用awe有什么好处吗？
【在 g***l 的大作中提到】
: 我们这管STOREAGE就是看看SETTING OPTIMAL就不管了，说没问题
: IO一天高一天低也看不出规律。明显地WRITE要慢很多，TEMPDB DAILY AVG IO WAIT
: 一直很高，有个别天都上好几千，也没订单进来，1/2号根本就没有几个ORDER
: max number of concurrent connections =0
: default connction options nothing
: cast threshold for parallellism =5
: max degre of parallelism=0
: server 64 bit 2008 version 10.0.2351
: memory min 1G max 30G
: use AWE to allocate memory
|
g***l
发帖数: 18555 | 18 有些东西还是要用AWE的，保险起见TURN IT ON
http://support.microsoft.com/kb/918483
【在 n*w 的大作中提到】
: 64位用awe有什么好处吗？
|
