SQLÓÅ»¯
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
4¡¢ÄÚ´æ²»×ã
5¡¢ÍøÂçËÙ¶ÈÂý
6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó£¨¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿£©
7¡¢Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
8¡¢sp_lock,sp_who,»î¶¯µÄÓû§²é¿´,ÔÒòÊǶÁд¾ºÕù×ÊÔ´¡£
9¡¢·µ»ØÁ˲»±ØÒªµÄÐкÍÁÐ
10¡¢²éѯÓï¾ä²»ºÃ£¬Ã»ÓÐÓÅ»¯
¿ÉÒÔͨ¹ýÈçÏ·½·¨À´ÓÅ»¯²éѯ :
1¡¢°ÑÊý¾Ý¡¢ÈÕÖ¾¡¢Ë÷Òý·Åµ½²»Í¬µÄI/OÉ豸ÉÏ£¬Ôö¼Ó¶ÁÈ¡Ëٶȣ¬ÒÔÇ°¿ÉÒÔ½«TempdbÓ¦·ÅÔÚRAID0ÉÏ£¬SQL2000²»ÔÚÖ§³Ö¡£Êý¾ÝÁ¿£¨³ß´ç£©Ô½´ó£¬Ìá¸ßI/OÔ½ÖØÒª.
2¡¢×ÝÏò¡¢ºáÏò·Ö¸î±í£¬¼õÉÙ±íµÄ³ß´ç(sp_spaceuse)
3¡¢Éý¼¶Ó²¼þ
4¡¢¸ù¾Ý²éѯÌõ¼þ,½¨Á¢Ë÷Òý,ÓÅ»¯Ë÷Òý¡¢ÓÅ»¯·ÃÎÊ·½Ê½£¬ÏÞÖƽá¹û¼¯µÄÊý¾ÝÁ¿¡£×¢ÒâÌî³äÒò×ÓÒªÊʵ±£¨×îºÃÊÇʹÓÃĬÈÏÖµ0£©¡£Ë÷ÒýÓ¦¸Ã¾¡Á¿Ð¡£¬Ê¹ÓÃ×Ö½ÚÊýСµÄÁн¨Ë÷ÒýºÃ£¨²ÎÕÕË÷ÒýµÄ´´½¨£©,²»Òª¶ÔÓÐÏ޵ļ¸¸öÖµµÄ×ֶν¨µ¥Ò»Ë÷ÒýÈçÐÔ±ð×Ö¶Î
5¡¢Ìá¸ßÍøËÙ;
6¡¢À©´ó·þÎñÆ÷µÄÄÚ´æ,Windows 2000ºÍSQL server 2000ÄÜÖ§³Ö4-8GµÄÄÚ´æ¡£ÅäÖÃÐéÄâÄڴ棺ÐéÄâÄÚ´æ´óСӦ»ùÓÚ¼ÆËã»úÉϲ¢·¢ÔËÐеķþÎñ½øÐÐÅäÖá£ÔËÐÐ Microsoft SQL Server? 2000 ʱ£¬¿É¿¼Âǽ«ÐéÄâÄÚ´æ´óСÉèÖÃΪ¼ÆËã»úÖа²×°µÄÎïÀíÄÚ´æµÄ 1.5 ±¶¡£Èç¹ûÁíÍâ°²×°ÁËÈ«ÎļìË÷¹¦ÄÜ£¬²¢´òËãÔËÐÐ Microsoft ËÑË÷·þÎñÒÔ±ãÖ´ÐÐÈ«ÎÄË÷ÒýºÍ²éѯ£¬¿É¿¼ÂÇ£º½«ÐéÄâÄÚ´æ´óСÅäÖÃΪÖÁÉÙÊǼÆËã»úÖа²×°µÄÎïÀíÄÚ´æµÄ 3 ±¶¡£½« SQL Server max server memory ·þÎñÆ÷ÅäÖÃÑ¡ÏîÅäÖÃΪÎïÀíÄÚ´æµÄ 1.5 ±¶£¨ÐéÄâÄÚ´æ´óСÉèÖõÄÒ»°ë£©¡£
7¡¢Ôö¼Ó·þÎñÆ÷CPU¸öÊý;µ«ÊDZØÐëÃ÷°×²¢Ðд¦Àí´®Ðд¦Àí¸üÐèÒª×ÊÔ´ÀýÈçÄڴ档ʹÓò¢Ðл¹ÊÇ´®ÐгÌÊÇMsSQL×Ô¶¯ÆÀ¹ÀÑ¡ÔñµÄ¡£µ¥¸öÈÎÎñ·Ö½â³É¶à¸öÈÎÎñ£¬¾Í¿ÉÒÔÔÚ´¦ÀíÆ÷ÉÏÔËÐС£ÀýÈçµ¢¸é²éѯµÄÅÅÐò¡¢Á¬½Ó¡¢É¨ÃèºÍGROUP BY×Ö¾äͬʱִÐУ¬SQL SERVER¸ù¾ÝϵͳµÄ¸ºÔØÇé¿ö¾ö¶¨×îÓŵIJ¢Ðеȼ¶£¬¸´ÔÓµÄÐèÒªÏûºÄ´óÁ¿µÄCPUµÄ²éѯ×îÊʺϲ¢Ðд¦Àí¡£µ«ÊǸüвÙ×÷UPDATE,INSERT£¬DELETE»¹²»Äܲ¢Ðд¦Àí¡£
8¡¢Èç¹ûÊÇʹÓÃlike½øÐвéѯµÄ»°£¬¼òµ¥µÄʹÓÃindexÊDz»Ðеģ¬µ«ÊÇÈ«ÎÄË÷Òý£¬ºÄ¿Õ¼ä¡£ like 'a%' ʹÓÃË÷Òý like '%a' ²»Ê¹ÓÃË÷ÒýÓà like '%a%' ²éѯʱ£¬²éѯºÄʱºÍ×Ö¶ÎÖµ×ܳ¤¶È³ÉÕý±È,ËùÒÔ²»ÄÜÓÃCHARÀàÐÍ£¬¶øÊÇVARCHAR¡£¶ÔÓÚ×ֶεÄÖµºÜ³¤µÄ½¨È«ÎÄË÷Òý¡£
9¡¢DB Server ºÍAPPLication
Ïà¹ØÎĵµ£º
ʹÓÃÕûÊýÊý¾ÝµÄ¾«È·Êý×ÖÊý¾ÝÀàÐÍ¡£
bigint ´Ó -2^63 (-9223372036854775808) µ½ 2^63-1 (9223372036854775807) µÄÕûÐÍÊý¾Ý£¨ËùÓÐÊý×Ö£©¡£
´æ´¢´óСΪ 8 ¸ö×Ö½Ú¡£
int ´Ó -2^31 (-2,147,483,648) µ½ 2^31 - 1 (2,147,483,64 ......
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
---------------
while Ìõ¼þ
begin
Ö´ÐвÙ×÷
set @i=@i+1
end
WHILE
ÉèÖÃÖظ´Ö´ÐÐ SQL Óï¾ä»òÓï¾ä¿éµÄÌõ¼þ¡£Ö»ÒªÖ¸¶¨µÄÌõ¼þΪÕ棬¾ÍÖظ´Ö´ÐÐÓï¾ä¡£¿ÉÒÔʹÓà BREAK ºÍ CONTINUE ¹Ø¼ü×ÖÔÚÑ»·ÄÚ²¿¿ ......
ÏÖÔÚ´ó¶àÊýÃèÊöSQL Server 2005ÐÂÌØÐÔµÄÎÄÕ¶¼¹Ø×¢ÓÚ»ª¶ø²»ÊµµÄÌØÐÔ£¬ÈçSQLCLR»òXMLÊý¾ÝÀàÐÍ£¬¶ø¶ÔÐí¶àºÜºÃµÄÀϵÄT-SQLÓïÑԵĸĽøûÓеõ½Ó¦ÓеĹØ×¢¡£ÎÒÔø¾´ÓÐí¶àDBA¿ÚÖÐÌýµ½ÁîËûÃǸüÐ˷ܵÄÊÇ¿´µ½T-SQLÓïÑԵĸĽø,¶ø²»ÊÇÄÇЩгöÏֺͷ¢²¼µÄ¹¦ÄÜ¡£¶ÔÓÚSQLÓïÑÔµÄÒ»¸öºÜÓÐÓõÄʵ¼Ê¸Ä½øÊÇOUTPUT×Ӿ䣬ËüÔÊÐí²éѯһ¸öÊý¾ ......