΢ͻӦ
WEIXINGJI YU YINGYONG
19993 18 3 vol.18 No.3



SQL ServerݿŻ
ǮĲл
ժҪӰSQL ServerݿܵһЩؼSQL ServerŻԭһЩָԵԭŻݿܡ
ؼʣSQL Serverݿ⡡Żѯ
1ӦϵͳƺѣҪʹϵͳﵽŻܲһ׵¡ڿߡݿơӦóĽṹѯơӿѡȷжѡȡضӦԼļܡSQL ServerΪӺ̨ݿĽǶӦóŻɣҸһЩĽ顣
1ݿ
ҪõSQL ServerʵŵܣؼҪ1ܺõݿƷʵʹУSQL ServerݿƵòõܺܲԣҪʵõݿƾͱ뿼Щ⡣
1.1߼淶
һ˵߼ݿƻ淶ǰ3׼
1.1淶ûظֵС
2.2淶ÿǹؼֶαؼ֣1ʽؼֵĳЩɲ֡
3.3淶1ǹؼֶβ1ǹؼֶΡ
Щƻٵк͸ıҲͼ࣬Ҳڴ洢ݵҳϵҲҪͨӵĺϲήϵͳܡĳ̶ֳϵķǹ淶Ըϵͳܣǹ淶̿Ըܷ治ͬĿöֲͬķУ·ʵ֤ܡ
1.淶Ʋ4··ϲϵͿԿݿʵ()мظ()
2.õļֶ(ܼơֵ)ԿǴ洢ݿʵС
ĳһĿļƻϵͳмƻֶΪĿšƻμƻƻмƻƻ(ƻ+μƻ+ƻ+мƻ)ûҪڲѯͱõģڱļ¼ܴʱбҪѼƻΪ1ֶμ뵽СԲôڿͻ˱ݵһԡ
3.¶ʵԼⲿݻݵĿ֧Ӧķǹ淶ǣ
(1)1ʵ()ָ2(еԷֳ2)ͰƵʵͬٱʵݷֿˡַҪÿиҪؼ֡ڲдٵı
(2)1ʵ()ָ2(езֳ2)ַЩݵʵ()ӦгҪʷ¼ʷ¼õ˿԰ƵʵͬٱʵʷݷֿΪӼ߼(š۷)ʵģôַҲǺкôġ
1.2ݿ
Ҫȷѡʵֲԣ붮ݿʸʽӲԴĲص㣬ҪڴʹϵͳI/OһΧ㷺Ļ⣬µ׼ܻ
1.ÿصӦ÷ӳС洢ռ䣬رǶڱиˡʹsmallint;ͲҪintegerֶͣοԱضȡҿ1ҳϷøУҲͼI/O
2.1ĳ豸ϣͨSQL ServerΰĲִ1ͬ豸ϣܡϵͳ˶ʹ̿ݷ뼼£ĺôԡ
3.SQL ServerΰһƵʹõĴָ2ʹ̿ݿ豸ϣҲܡΪжͷڲңݷҲܡ
4.SQL Serverΰıͼеݴ1豸Ͽܡ1רõ͵Ŀܽһܡ
2SQL ServerصӲϵͳ
SQL ServerйصӲưϵͳڴ桢ϵͳ磬4ֻϹӲƽ̨Windows NTSQL Serverϡ
2.1ϵͳ(CPU)
ԼľҪȷCPUṹĹ̾ǹӲƽ̨ռCPUĹĹ̡ľ鿴CPUӦ180586/100ֻ23û㹻ˣָ֧û͹ؼӦãƼPentium ProPCPU
2.2ڴ(RAM)
ΪSQL ServerȷʵڴöʵõҪġSQL Serverڴ̻桢ݺ桢̬֧ÿ֧SQL Server2GBڴ棬Ҳֵһ뿼ǵWindows NTصķҲҪռڴ档
Windows NTΪÿWIN32Ӧóṩ4GBַռ䡣ַռWindows NTڴ(VMM)ӳ䵽ڴϣĳЩӲƽ̨ϿԴﵽ4GBSQL ServerӦóַֻ֪Բֱӷڴ棬VMMƵġWindows NTõڴַռ䣬SQL ServerڴڿõڴʱήSQL Serverܡ
ЩַռרΪSQL ServerϵͳõģͬһӲƽ̨ϻ(ļʹӡӦó)УôӦÿǵҲռһڴ档һ˵Ӳƽ̨Ҫ32MBڴ棬УWindows NTҪռ16MB1򵥵ķǣÿһû100KBڴ档磬100ûҪ32MB+100û*100KB42MBڴ棬ʵʵʹҪеʵ˵ڴϵͳܵõ;
2.3ϵͳ
1õĴI/OϵͳʵõSQL ServerһҪķ档۵Ĵϵͳ1̿豸1Ӳ̵ԪжԴúļϵͳĿǡSCSI-2̿ǲѡص£
(1)ٻ档
(2)дԼٶϵͳCPUжϡ
(3)첽д֧֡
(4)32λRAID֧֡
(5)SCSI2
(6)ǰٻ(1ŵ) 
3
ھѡӲƽ̨ʵ1õݿⷽҾ߱ûӦ÷֪ʶӦƲѯˡ2SQL ServerȡõĲѯʮҪģ1ǸSQL ServerŻ֪ʶɲѯ2SQL Serverص㣬ǿݷʲ
3.1SQL ServerŻ
Microsoft SQL Serverݿں1ڷõĲѯŻԶŻSQLύݲѯݲѯָ֧SQLؼWHEREHAVINGĲѯSELECTDELETEUPDATEڷõĲѯŻͳϢӾķù㡣
˽Żݴ̵ļ򵥷ǼSHOWPLANûַĹ(isql)ͨSHOW SHOWPLAN ONõSHOWPLANʹͼλѯSQL Enterprise ManagerеĲѯ߻isql/w趨ѡṩһϢ
SQL ServerŻͨ3׶ɣѯѡ񡢺ϲѡ
1.ѯ
ڲѯ׶ΣSQL ServerŻ鿴ÿһѯӾ䣬жǷܱŻSQL ServerһᾡŻЩɨӾ䡣磬/ϲӾ䡣ǲкϷSQL﷨ԷֳɿŻӾ䣬纬SQLȹϵ<>Ӿ䡣Ϊ<>1ųԵĲ1ԵĲɨ֮ǰ޷ȷӾѡΧж󡣵1ϵͲѯквŻӾʱִмƻñɨʲѯ֣ڲѯпŻSQL ServerӾ䣬Żִѡ
2.ѡ
ÿŻӾ䣬Ż鿴ݿϵͳȷǷصڷݡֻеее1ǰ׺ѯӾеȫƥʱűΪõġΪǸе˳ģҪƥǾȷƥ䡣ڷִԭҲǸ˳ġĴҪзݣڵ绰вΪĳϵĿһûʲôãΪ㻹ǵò鿴ÿһȷǷ1ӾпõôŻͻΪȷѡԡ
ƹУҪݲѯ׼ϸеĲѯԲѯŻصΪ
(1)ȽխбȽϸߵЧʡڱȽխ˵ÿҳܴŽ϶УļҲ١ԣܷøҳҲI/O
(2)SQL ServerŻܷͺϲԡٵĿȣ϶խŻṩѡ񡣵ǲҪҪΪǽӴ洢άĿ֧ڸSQL ServerŻֻҪеķֲͳϢĵ1Ӧкܴѡԡ
(3)ϵӰUPDATEINSERTDELETEܣΪеӦĵ⣬еķҳ¼־УҲI/O
(4)1µнӰܡ
(5)ڴ洢֧I/Oԭ򣬽СȽϴܸһЩȱҪάС
(6)ÿһҪѯʹƵȣҳʹȻȶЩʵŻ
(7)ѯеWHEREӾеκжܿǸУΪŻص㴦Ӿ䡣
(8)С1ΧСͱǲģΪС˵ɨҷõ͡
(9)롰ORDER BYGROUP BYһʹõһORDER BYõзִôͲ1ˣΪѾˡGROUP BYһ1
(10)ִӦùھ仯ϣΪеƶʵִͽ״ϵͳʱҪעһ㣬ΪЩϵͳƵ仯ġ
3.ϲѡ
ѡеӾ䶼һǵķʼƻĴʱŻʼִкϲѡ񡣺ϲѡҳһںϲӾʼƻЧ˳Ϊһ㣬ŻȽӾĲͬȻѡI/OĽǶȿ͵ĺϲƻΪӾϵŲѯĸӶȼSQL ServerѯŻʹ֦ЩȽĿ֧ϲѡ׶νʱSQL ServerѯŻѾ1ڷõĲѯִмƻƻ˿õСϵͳ֧õִܷԭݡ
3.2ЧĲѯѡ
ϲѯŻ3׶βѿƳI/O߼I/OٵķպôʱI/Oʱƽ⣬ǸЧѯƵҪĿꡣҲ˵ϣƳĲѯ̶д١ЧڴCPUԴ
½ǴSQL ServerŻŻܽģƸЧĲѯǺаġ
1.жصôСWHEREӾãǷյ(Χ)ǿŵ䡣
2.ݿʵĽǶȿвӴ(ORIN)WHEREӾһ˵̫ܲáԣŻܻRԣֲԻ1кÿƥִеıʶŻЩб־(ҳźк)ָ1ƥеġ̬Żֻɨ蹤ȡÿһб־ٴݱȡӦУRԵĴɹ
3.NOT<>!=WHEREӾŻѡ˵ûʲôôΪӾųԵģǰԵģɨԭݱ֮ǰ޷ȷӾѡԡ
4.תʹŻһ㲻WHEREӾеıʽתʽѡ磺
paycheck * 12>36000 or substring(lastname,1,1)=L
ñpaychecklastnameͲŻԸдʽΪ
paycheck<36000/12 or lastname like L%
5.WHEREӾеıرΪǲŻ֪ͿǵģǶΪı
6.ûаϲӾôŻ1ԴźϲСıеСȻϹ1ִһЧĺϲĴǹɺķɣ̽REFORMATTINGӦעRAMлϵݿtempdbĴС(SELECT INTO)⣬Щ͵ĲǺܳģôtempdbRAMжǺкôġ 
4Ż
гӰSQL ServerһЩҪأʵԶֹЩϵͳӰҲܴWindows NT£ļϵͳѡЭ顢ķSQL ServerȼѡҲ̶ͬӰSQL Serverܡ
Ӱܵ˵Ķ࣬Ӧָͬҳ1ͨõŻǲʵģϵͳάĹбеϼԵʵϣ󲿷ֵŻ͵ͻ˶ķϽеģҲʵеġ
ߵλ:ϺͨѧϢ(200030)
ο
1Schneider R DС.滮뽨ܵSQL Server6.5ݿ.еҵ磬1997
(ոڣ1998-09-11)
