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+就算高了。
【在 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.
【在 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.
b*n
19 楼
谢谢楼上帮忙的同学!
a9
20 楼
虚拟硬盘,呵呵。
【在 S***k 的大作中提到】 : How to do it? : Could you please give some pointers?
i*a
21 楼
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
22 楼
是什么性质的应用呢?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
23 楼
【在 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.
【在 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
25 楼
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
26 楼
*是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
27 楼
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
28 楼
我们这管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
29 楼
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
30 楼
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
31 楼
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