SQLÐÔÓÐÓÅ»¯
ת×Ô http://database.ctocio.com.cn/222/9068222.shtml
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%' ²éѯʱ£¬²éѯºÄʱºÍ×Ö¶ÎÖµ×ܳ¤¶È³ÉÕý±È,
Ïà¹ØÎĵµ£º
--²éѯӦÓóÌÐòµÄµÈ´ý
SELECT TOP 10
wait_type,waiting_tasks_count AS tasks,
wait_time_ms,max_wait_time_ms AS max_wait,
signal_wait_time_ms AS signal
from sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
--²éѯÔÚÈÎһʱ¿ÌËùÓÐÊÚȨ¸øµ±Ç°Ö´ÐÐÊÂÎñ»òµ±Ç°Ö´ÐÐÊÂÎñµÈ´ýµÄËø
SELECT
request_session_id A ......
1.Union
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Á¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£
UNION µ ......
1.JOINºÍUNIONÇø±ð
join ÊÇÁ½Õűí×ö½»Á¬ºóÀïÃæÌõ¼þÏàͬµÄ²¿·Ö¼Ç¼²úÉúÒ»¸ö¼Ç¼¼¯£¬
unionÊDzúÉúµÄÁ½¸ö¼Ç¼¼¯(×Ö¶ÎÒªÒ»ÑùµÄ)²¢ÔÚÒ»Æ𣬳ÉΪһ¸öеļǼ¼¯ ¡£
JOINÓÃÓÚ°´ÕÕONÌõ¼þÁª½ÓÁ½¸ö±í£¬Ö÷ÒªÓÐËÄÖÖ£º
INNER JOIN£ºÄÚ²¿Áª½ÓÁ½¸ö±íÖеļǼ£¬½öµ±ÖÁÉÙÓÐÒ»¸öͬÊôÓÚÁ½±íµÄÐзûºÏÁª½ÓÌõ¼þʱ£¬ÄÚÁª½Ó²Å·µ»ØÐС£ÎÒÀí½âµÄ ......
1.ʹÓÃCTE¼ò»¯Ç¶Ì×sql
ÏÈ¿´ÏÂÃæÒ»¸öǶÌ׵IJéѯÓï¾ä£º
select * from person.StateProvince where CountryRegionCode in
(select CountryRegionCode from person.CountryRegion where  ......