Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL ServerËÀËø

 1. ËÀËøÔ­Àí
    ¸ù¾Ý²Ù×÷ϵͳÖеĶ¨Ò壺ËÀËøÊÇÖ¸ÔÚÒ»×é½ø³ÌÖеĸ÷¸ö½ø³Ì¾ùÕ¼Óв»»áÊͷŵÄ×ÊÔ´£¬µ«Òò»¥ÏàÉêÇë±»ÆäËû½ø³ÌËùÕ¾Óò»»áÊͷŵÄ×ÊÔ´¶ø´¦ÓÚµÄÒ»ÖÖÓÀ¾ÃµÈ´ý״̬¡£
    ËÀËøµÄËĸö±ØÒªÌõ¼þ£º
»¥³âÌõ¼þ(Mutual exclusion)£º×ÊÔ´²»Äܱ»¹²Ïí£¬Ö»ÄÜÓÉÒ»¸ö½ø³ÌʹÓá£
ÇëÇóÓë±£³ÖÌõ¼þ(Hold and wait)£ºÒѾ­µÃµ½×ÊÔ´µÄ½ø³Ì¿ÉÒÔÔÙ´ÎÉêÇëеÄ×ÊÔ´¡£
·Ç°þ¶áÌõ¼þ(No pre-emption)£ºÒѾ­·ÖÅäµÄ×ÊÔ´²»ÄÜ´ÓÏàÓ¦µÄ½ø³ÌÖб»Ç¿ÖƵذþ¶á¡£
Ñ­»·µÈ´ýÌõ¼þ(Circular wait)£ºÏµÍ³ÖÐÈô¸É½ø³Ì×é³É»·Â·£¬¸Ã»·Â·ÖÐÿ¸ö½ø³Ì¶¼ÔڵȴýÏàÁÚ½ø³ÌÕýÕ¼ÓõÄ×ÊÔ´¡£
¶ÔÓ¦µ½SQL ServerÖУ¬µ±ÔÚÁ½¸ö»ò¶à¸öÈÎÎñÖУ¬Èç¹ûÿ¸öÈÎÎñËø¶¨ÁËÆäËûÈÎÎñÊÔͼËø¶¨µÄ×ÊÔ´£¬´Ëʱ»áÔì³ÉÕâЩÈÎÎñÓÀ¾Ã×èÈû£¬´Ó¶ø³öÏÖËÀËø£»ÕâЩ×ÊÔ´¿ÉÄÜÊÇ£ºµ¥ÐÐ(RID£¬¶ÑÖеĵ¥ÐÐ)¡¢Ë÷ÒýÖеļü(KEY£¬ÐÐËø)¡¢Ò³(PAG£¬8KB)¡¢Çø½á¹¹(EXT£¬Á¬ÐøµÄ8Ò³)¡¢¶Ñ»òBÊ÷(HOBT) ¡¢±í(TAB£¬°üÀ¨Êý¾ÝºÍË÷Òý)¡¢Îļþ(File£¬Êý¾Ý¿âÎļþ)¡¢Ó¦ÓóÌÐòרÓÃ×ÊÔ´(APP)¡¢ÔªÊý¾Ý(METADATA)¡¢·ÖÅäµ¥Ôª(Allocation_Unit)¡¢Õû¸öÊý¾Ý¿â(DB)¡£Ò»¸öËÀËøʾÀýÈçÏÂͼËùʾ£º
 
    ËµÃ÷£ºT1¡¢T2±íʾÁ½¸öÈÎÎñ£»R1ºÍR2±íʾÁ½¸ö×ÊÔ´£»ÓÉ×ÊÔ´Ö¸ÏòÈÎÎñµÄ¼ýÍ·(ÈçR1->T1£¬R2->T2)±íʾ¸Ã×ÊÔ´±»¸ÄÈÎÎñËù³ÖÓУ»ÓÉÈÎÎñÖ¸Ïò×ÊÔ´µÄ¼ýÍ·(ÈçT1->S2£¬T2->S1)±íʾ¸ÃÈÎÎñÕýÔÚÇëÇó¶ÔӦĿ±ê×ÊÔ´£»
    ÆäÂú×ãÉÏÃæËÀËøµÄËĸö±ØÒªÌõ¼þ£º
(1).»¥³â£º×ÊÔ´S1ºÍS2²»Äܱ»¹²Ïí£¬Í¬Ò»Ê±¼äÖ»ÄÜÓÉÒ»¸öÈÎÎñʹÓã»
(2).ÇëÇóÓë±£³ÖÌõ¼þ£ºT1³ÖÓÐS1µÄͬʱ£¬ÇëÇóS2£»T2³ÖÓÐS2µÄͬʱÇëÇóS1£»
(3).·Ç°þ¶áÌõ¼þ£ºT1ÎÞ·¨´ÓT2ÉÏ°þ¶áS2£¬T2Ò²ÎÞ·¨´ÓT1ÉÏ°þ¶áS1£»
(4).Ñ­»·µÈ´ýÌõ¼þ£ºÉÏͼÖеļýÍ·¹¹³É»·Â·£¬´æÔÚÑ­»·µÈ´ý¡£
 
2. ËÀËøÅŲé
(1). ʹÓÃSQL ServerµÄϵͳ´æ´¢¹ý³Ìsp_whoºÍsp_lock£¬¿ÉÒԲ鿴µ±Ç°Êý¾Ý¿âÖеÄËøÇé¿ö£»½ø¶ø¸ù¾ÝobjectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)¿ÉÒԲ鿴Äĸö×ÊÔ´±»Ëø£¬ÓÃdbcc ld(@blk)£¬¿ÉÒԲ鿴×îºóÒ»Ìõ·¢Éú¸øSQL ServerµÄSqlÓï¾ä£»
(2). ʹÓà SQL Server Profiler ·ÖÎöËÀËø: ½« Deadlock graph ʼþÀàÌí¼Óµ½¸ú×Ù¡£´ËʼþÀàʹÓÃËÀËøÉæ¼°µ½µÄ½ø³ÌºÍ¶ÔÏóµÄ XML Êý¾ÝÌî³ä¸ú×ÙÖÐµÄ TextData Êý¾ÝÁС£SQL Server ʼþ̽²éÆ÷ ¿ÉÒÔ½« XML ÎĵµÌáÈ¡µ½ËÀËø XML (.xdl) ÎļþÖУ¬ÒÔºó¿ÉÔÚ SQL Server Management Studio Öв鿴¸


Ïà¹ØÎĵµ£º

ͼÎÄÏê½âÈçºÎÓÃPowerDesignerÉè¼ÆSQL Server

¹¤¾ß£º
Sybase PowerDesigner 12.5
Microsoft SQL Server 2005
µÚÒ»²½£º¸ÅÒªÉè¼Æ
´ò¿ªPowerDesignerÈí¼þ£¬Éè¼Æ“¸ÅÄîÊý¾ÝÄ£ÐÍ”(Conceptual Data Model)£º
 
ͼ1
Éè¼Æ±í½á¹¹£º
 
ͼ2
ÔÚÉè¼ÆÊôÐÔ(×Ö¶Î)µÄʱºò£¬Èý¸ö×Öĸ(M¡¢P¡¢D)·Ö±ð±íʾ£º
M£ºÊÇ·ñΪ¿Õ;(√±íʾ²»ÔÊÐíΪ¿Õ)
P£ºÊÇ·ñ뻅 ......

sql serverµÄ¸´ÖƺͶ©ÔÄ

      ×î½üÓöµ½Ò»¸öÏîÄ¿£¬¿Í»§ÔÚ×ܲ¿Â¼ÈëÐÅÏ¢ºó£¬Êý¾ÝÐèҪͬ²½µ½ÏÂÊôµÄÈô¸É¸ö×ÓϵͳÖС£ÏÖÔÚ×ܲ¿µ¥¶ÀʹÓÃÒ»¸öÊý¾Ý¿â£¬ÏÂÊôµ¥Î»¸÷×ÔʹÓÃ×Ô¼ºµÄÊý¾Ý¿â¡£
      ¶ÔÓÚÕâ¸öÎÊÌ⣬Ïëµ½Á˼¸¸ö½â¾ö·½°¸£º
     1¡¢×Ô¼ºÐ´´úÂë½â¾ö¡£¶¨ÒåºÃ·ÃÎÊÿ¸öÊý¾ ......

¹¦ÄÜÇ¿´óµÄSQLÓï¾ä

1. ¸´ÖƱí½á¹¹
Sql´úÂë
   1. select * into B from A where 1=0;   
select * into B from A where 1=0;
 2.¸´ÖƱí¼Ç¼ ¸´ÖÆijЩ×Ö¶Î
Sql´úÂë
   1. insert into B(a, b, c) select d, e, f from A;   
insert into B(a, b, c) select d, e, f from A;
  ¸´Ö ......

SQL SERVER 2005·ÖÒ³´æ´¢¹ý³Ì

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PE011_Page]
  @TableName varchar(50),        --±íÃû
 @Fields varchar(5000) = '*',    --×Ö¶ÎÃû(È«²¿×Ö¶ÎΪ*)
 @OrderField varchar(5000),    &n ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ