SQLÐÔÄܵ÷УÃüÁî
DBCC DROPCLEANBUFFERS --Çå³ý»º³åÇø£¬±ãÓڶԱȲéѯʱ¼äºÍÐÔÄÜ£¬²»ÊÕ»º´æµÄÓ°Ïì
SET STATISTICS TIME ON --ÏÔʾ·ÖÎö¡¢±àÒëºÍÖ´Ðи÷Óï¾äËùÐèµÄºÁÃëÊý
sp_spaceused NSDoctorAdvice0705 -- ²é¿´±í¿Õ¼ä´óС
ÎÊÌâÌÖÂÛ£º
ÊÇ·ñÔö¼ÓÒ»¸ö¶ÀÁ¢µÄÎļþ×飬ÓÃÀ´´æ·ÅË÷Òý¡£Ä¿Ç°Êý¾Ý¿âµÄÖ»ÓÐÒ»¸öÎļþ×飬Îļþ·Ç³£´óµÄ»°£¬²éѯ»á±È½ÏÂý¡£
ÏÖÔÚÖ»Óõ½´´½¨Ë÷Òý£¬Ìá¸ßÐÔÄÜ¡£ÔÙ¿¼ÂÇ´´½¨Í³¼ÆÕâ¸ö¹¦ÄÜ£¬À´¸Ä½øÐÔÄÜ¡£
¶Ô1¸ö¾³£±»¸üеÄÁн¨Á¢Ë÷Òý£¬»áÑÏÖØÓ°ÏìÐÔÄÜ
·Ö´ØË÷Òý²»Ó¦¸Ã¹¹ÔìÔÚ¾³£±ä»¯µÄÁÐÉÏ£¬ÒòΪÕâ»áÒýÆðÕûÐеÄÒÆ¶¯
²»Òª¶ÔÓÐÏ޵öÖµµÄ×ֶν¨µ¥Ò»Ë÷ÒýÈçÐÔ±ð×Ö¶Î
²éѯºÄʱºÍ×Ö¶ÎÖµ×ܳ¤¶È³ÉÕý±È,ËùÒÔ²»ÄÜÓÃCHARÀàÐÍ£¬¶øÊÇVARCHAR¡£
¶ÔÓÚ×ֶεÄÖµºÜ³¤µÄ½¨È«ÎÄË÷Òý¡£
DB Server ºÍAPPLication Server ·ÖÀë;OLTPºÍOLAP·ÖÀë [Õâ¸ö¼¼ÊõÒªºÃºÃÑо¿£¡]
×¢ÒâUNionºÍUNion all µÄÇø±ð¡£UNION allºÃ
20¡¢ÓÃsp_configure 'query governor cost limit'»òÕßSET QUERY_GOVERNOR_COST_LIMITÀ´ÏÞÖÆ²éѯÏûºÄµÄ×ÊÔ´¡£µ±ÆÀ¹À²éѯÏûºÄµÄ×ÊÔ´³¬³öÏÞÖÆÊ±£¬·þÎñÆ÷×Ô¶¯È¡Ïû²éѯ,ÔÚ²éѯ֮ǰ¾Í¶óɱµô¡£ SET LOCKTIMEÉèÖÃËøµÄʱ¼ä
NOT IN»á¶à´ÎɨÃè±í£¬Ê¹ÓÃEXISTS¡¢NOT EXISTS £¬IN , LEFT OUTER JOIN À´Ìæ´ú£¬ÌرðÊÇ×óÁ¬½Ó,¶øExists±ÈIN¸ü¿ì£¬×îÂýµÄÊÇNOT²Ù×÷
Èç¹ûʹÓÃÁËIN»òÕßORµÈʱ·¢ÏÖ²éѯûÓÐ×ßË÷Òý£¬Ê¹ÓÃÏÔʾÉêÃ÷Ö¸¶¨Ë÷Òý£º Select * from PersonMember (INDEX = IX_Title) Where processid IN ('ÄÐ'£¬'Å®')
vi.¡¡¾¡Á¿Ê¹ÓÃexists´úÌæselect count(1)À´ÅжÏÊÇ·ñ´æÔڼǼ£¬countº¯ÊýÖ»ÓÐÔÚͳ¼Æ±íÖÐËùÓÐÐÐÊýʱʹÓ㬶øÇÒcount(1)±Ècount(*)¸üÓÐЧÂÊ¡£
¡¡44¡¢µ±·þÎñÆ÷µÄÄÚ´æ¹»¶àʱ£¬ÅäÖÆÏß³ÌÊýÁ¿ = ×î´óÁ¬½ÓÊý+5£¬ÕâÑùÄÜ·¢»Ó×î´óµÄЧÂÊ;·ñÔòʹÓà ÅäÖÆÏß³ÌÊýÁ¿<×î´óÁ¬½ÓÊýÆôÓÃSQL SERVERµÄÏ̳߳ØÀ´½â¾ö,Èç¹û»¹ÊÇÊýÁ¿ = ×î´óÁ¬½ÓÊý+5£¬ÑÏÖØµÄË𺦷þÎñÆ÷µÄÐÔÄÜ¡£
xi.¡¡×¢Òâinsert¡¢update²Ù×÷µÄÊý¾ÝÁ¿£¬·ÀÖ¹ÓëÆäËûÓ¦ÓóåÍ»¡£Èç¹ûÊý¾ÝÁ¿³¬¹ý200¸öÊý¾ÝÒ³Ãæ(400k)£¬ÄÇôϵͳ½«»á½øÐÐËøÉý¼¶£¬Ò³¼¶Ëø»áÉý¼¶³É±í¼¶Ëø¡£
46¡¢Í¨¹ýSQL Server Performance Monitor¼àÊÓÏàÓ¦Ó²¼þµÄ¸ºÔØ Memory: Page Faults / sec¼ÆÊýÆ÷Èç¹û¸Ãֵż¶û×߸ߣ¬±íÃ÷µ±Ê±ÓÐÏ߳̾ºÕùÄÚ´æ¡£Èç¹û³ÖÐøºÜ¸ß£¬ÔòÄÚ´æ¿ÉÄÜÊÇÆ¿¾±¡£
¡¡¡¡Process:
¡¡¡¡1¡¢% DPC Time Ö¸ÔÚ·¶Àý¼ä¸ôÆÚ¼ä´¦ÀíÆ÷ÓÃÔÚ»ºÑÓ³ÌÐòµ÷ÓÃ(DPC)½ÓÊÕºÍÌṩ·þÎñµÄ°Ù·Ö±È¡£(DPC ÕýÔÚÔËÐеÄΪ±È±ê×¼¼ä¸ôÓÅÏÈȨµÍµÄ¼ä¸ô)¡£ ÓÉÓÚ DPC ÊÇ
Ïà¹ØÎĵµ£º
1 £¬¶ÔÓÚÈÕÆÚ×Ö¶Î×Ö¶Î
access±íʾΪ£º#1981-28-12#
SQLSERVER2000±íʾΪ£º''1981-02-12''
2,SQLÓï¾äÇø±ð£¬select ,update ÔÚ¶Ôµ¥±í²Ù×÷ʱ¶¼²î²»¶à£¬
µ«¶à±í²Ù×÷ʱupdateÓï¾äµÄÇø±ðACCESSÓëSQLSERVERÖеÄUpdateÓï¾ä¶Ô±È:
SQLSERVERÖиüжà±íµÄUpdateÓï¾ä:
Update Tab1
SET a.Name = b.Name
from Tab1 a,Tab2 b
Whe ......
select * from student where name=?;
Èç¹û²»Óõ¥ÒýºÅÒýÆðÀ´£¬ pstmt.setString(1,"xx or 1=1");¼´sqlÓ¦¸Ã¾ÍÊÇselect * from student where name=xx or 1=1¾Í¿ÉÒÔÈ«²¿²é³ö¡£
Ç¿ÖÆµ¥ÒýºÅÒýÆðÀ´£¬select * from student where name='xx or 1=1'¡£¾ÍÎÞЧÁË¡£
ÊýÖµÐ͵ÄûÓÐÒªÇóÓõ¥ÒýºÅÒýÆðÀ´£¬Ó¦¸ÃÊÇÓÉÓÚÓÐÒ»¸öת»»¹ý ......
SQL Server Filtered Indexes - What They Are, How to Use and Performance Advantages
Written By: Arshad Ali -- 7/2/2009 -
Problem
SQL Server 2008 introduces Filtered Indexes which is an index with a WHERE clause. Doesn’t it sound awesome especially for a table that has huge amount of data and ......
Êý¾Ý¶¨ÒåÓïÑÔ£¨DDL£©<²Ù×÷±íµÄ½á¹¹>£ºcreate£¨ ´´½¨£©¡¢
alter£¨¸ü¸Ä£©¡¢
drop£¨É¾³ý£©
Êý¾Ý²Ù×ÝÓïÑÔ£¨DML£©<²Ù×÷±íµÄÊý¾Ý>£ºinsert£¨²åÈ룩¡¢select£¨Ñ¡Ôñ£©¡¢delete£¨É¾³ý£©¡¢update£¨¸üУ©
ÊÂÎñ¿ØÖÆÓïÑÔ£¨TCL£©£ºcommit £¨ Ìá½»£©¡¢savepoint£¨±£´æµã£©¡¢rollback£¨»Ø¹ö£©
Êý¾Ý¿ØÖÆÓïÑÔ£ºgrant£¨ÊÚÓè£ ......