ÃæÏò OLTP Ó¦ÓóÌÐòµÄÖØÒª SQL Server 2005 ÐÔÄÜÎÊÌâ
ÃæÏò OLTP Ó¦ÓóÌÐòµÄÖØÒª SQL Server 2005 ÐÔÄÜÎÊÌâ
OLTP ¹¤×÷¸ººÉµÄÌØÕ÷ÊÇ´æÔÚ´óÁ¿ÏàËƵÄСÐÍÊÂÎñ¡£
ÔÚ¼ì²éÊý¾Ý¿âÉè¼Æ¡¢×ÊÔ´ÀûÓúÍϵͳÐÔÄܵÄÖØÒªÐÔʱ£¬ÇëÎñ±ØÀμÇÕâЩÌØÕ÷¡£ÏÂÃæ¼òÊöÃæÏò OLTP
Ó¦ÓóÌÐòµÄÖØÒªÐÔÄÜÆ¿¾±»òȱÏÝ¡£
Êý¾Ý
¿âÉè¼ÆÎÊÌâ
³£Óòéѯ´æÔÚ¹ý¶àµÄ±íÁª½Ó¡£ÔÚ OLTP Ó¦ÓóÌÐòÖйý¶àʹÓÃÁª½Ó½«µ¼Ö²éѯÔËÐлºÂý£¬
ÀË·Ñϵͳ×ÊÔ´¡£Í¨³££¬Ó¦¸ÃÖØÐÂÉè¼ÆÊý¾Ý¿â£¬±ÜÃâÐèÒª 5 ¸ö»ò 5 ¸öÒÔÉϱíÁª½ÓµÄƵ·±²Ù×÷¡£
Ƶ·±¸üУ¨°üÀ¨²åÈë¡¢¸üкÍɾ³ý£©µÄ±íÖдæÔÚ¹ý¶àµÄË÷Òýµ¼Ö¶îÍâµÄË÷Òýά»¤¿ªÏú¡£Í¨³££¬OLTP Êý¾Ý¿âµÄÉè¼ÆÖÐÓ¦¸Ã½«Ë÷ÒýÊý±£³ÖΪÕý³£ÔËתǰÌáϵÄ×îСֵ£¬ÕâͬÑùÊÇÓÉÓÚ´óÁ¿ÏàËÆÊÂÎñÓëË÷Òýά»¤³É±¾¹²Í¬×÷ÓõĽá¹û¡£
ÓÉÓÚȱʧË÷Òý£¬µ¼Ö´óÁ¿ IO£¬Èç±íºÍ·¶Î§µÄɨÃè¡£¸ù¾Ý¶¨Ò壬OLTP ÊÂÎñ²»Ó¦ÒªÇó´óÁ¿ IO£¬²¢ÇÒÓ¦¸Ã½ÓÊܼì²é¡£
δʹÓõÄË÷ÒýÔÚ²åÈë¡¢¸üкÍɾ³ýÖе¼ÖÂË÷Òýά»¤³É±¾£¬¶ø¶ÔÈκÎÓû§¶¼Ã»ÓÐÓá£
Ó¦¸ÃÇå³ýδʹÓõÄË÷Òý¡£ÈκÎʹÓùýµÄË÷Òý£¨Í¨¹ýÑ¡Ôñ¡¢¸üлòɾ³ý²Ù×÷£©¶¼³öÏÖÔÚ sys.dm_db_index_usage_stats ÖС£Òò´Ë£¬ÈκÎÒѶ¨Ò嵫²»°üÀ¨ÔÚ´Ë DMV ÖеÄ
Ë÷Òý×ÔÉÏ´ÎÖØÐÂÆô¶¯ SQL Server ÒÔÀ´ÉÐδʹÓùý¡£
CPU
Æ¿
¾±
Ðźŵȴýʱ¼ä³¬¹ý×ܵȴýʱ¼äµÄ 25%¡£Çë²ÎÔÄ sys.dm_os_wait_stats ÖеÄÐźŵȴýʱ¼äºÍ×ܵȴýʱ¼ä¡£Ðźŵȴýʱ¼äÓÃÓÚ¶ÈÁ¿¿ÉÔËÐжÓÁеȴý CPU ʱËù»¨·ÑµÄʱ¼ä¡£Ðźŵȴýʱ¼äÖµºÜ¸ß˵Ã÷³öÏÖ CPU Æ¿¾±¡£
¼Æ
»®ÖØÓÃСÓÚ 90%¡£²éѯ¼Æ»®ÓÃÓÚÖ´ÐÐij¸ö²éѯ¡£OLTP ¹¤×÷¸ººÉ½¨ÒéʹÓüƻ®ÖØÓã¬ÒòΪÖØд´½¨ÏàͬµÄ¼Æ»®£¨ÓÃÓÚÏàËÆ»òÏàͬµÄÊÂÎñ£©ÊÇ¶Ô CPU
×ÊÔ´µÄÀË·Ñ¡£±È½Ï SQL Server SQL Statistics£ºbatch requests/sec Óë SQL
compilations/sec¡£°´ÕÕÈçϹ«Ê½¼ÆËã¼Æ»®ÖØÓ㺼ƻ®ÖØÓà =£¨Åú´¦ÀíÇëÇóÊý — SQL
±àÒëÊý£©/Åú´¦ÀíÇëÇóÊý¡£¼Æ»®ÖØÓùæÔòµÄÌØÀý£ºSQL 2005 SP2
Öв»»º´æ£¨¶ø²»ÊÇÖØÓã©Áã³É±¾¼Æ»®¡£Ê¹ÓÃÁã³É±¾¼Æ»®µÄÓ¦ÓóÌÐò¾ßÓнϵ͵ļƻ®ÖØÓ㬵«Õâ²»ÊÇÐÔÄÜÎÊÌâ¡£
²¢ÐеȴýÀàÐÍ cxpacket ³¬¹ý×ܵȴýʱ¼äµÄ 10%¡£²¢ÐÐÎþÉü CPU ×ÊÔ´»»È¡Ö´ÐÐËٶȡ£Èç¹ûÓдóÁ¿ OLTP£¬²¢Ðвéѯͨ³£»á½µµÍ OLTP ÍÌÍÂÁ¿£¬ËùÒÔÓ¦¾¡Á¿±ÜÃâ¡£Óйصȴýͳ¼ÆÐÅÏ¢£¬
Çë²ÎÔÄ sys.dm_os_wait_stats¡£
ÄÚ´æÆ¿
¾±
Average
Page Life Expectancy ³ÖÐø½ÏµÍ¡£Çë²ÎÔÄ Perfmon ¶ÔÏó SQL Server Buffer Manager ÖеÄ
Average Page Life Expectancy ¼ÆÊýÆ÷£¨±íʾһҳÔÚ»º´æÖÐÍ£ÁôµÄƽ¾ù
ÃëÊý£©¡
Ïà¹ØÎĵµ£º
PowerBuilderÊÇÄ¿Ç°×îÁ÷ÐеÄÊý¾Ý¿â¿ª·¢¹¤¾ßÖ®Ò»¡£PowerBuilderÌṩÁËÔÚ³ÌÐò´úÂëÖмÓÈëǶÈëʽSQLÓï¾äµÄ¹¦ÄÜÀ´Ö§³Ö¶ÔÊý¾Ý¿âµÄ·ÃÎÊ¡£µ«ÕâÖÖǶÈëʽSQLÓï¾äÖ»ÄÜÖ§³ÖһЩ¹Ì¶¨µÄ±ê×¼µÄSQLÓï¾ä£¬¼´ÔÚ½øÐгÌÐò´úÂë±àÒë´¦ÀíʱÕâЩSQLÓï¾ä±ØÐëÊÇÈ·¶¨µÄ£¬ÀýÈ磺¶ÔÄÄÕűíÄļ¸¸ö×ֶνøÐвÙ×÷ÔÚ³ÌÐò´úÂëÖÐÊǹ̶¨Ð´Ã÷µÄ£¬ÁíÍâÕâÖÖ·½Ê½Ò²²»ÄÜ ......
use myoa
select * from
delete from department where departmentid=1
insert Department(DepartmentId,DepartmentName) values(1,'¼¼Êõ²¿')
update Department set departmentname='ÐÅÏ¢¼¼Êõ²¿' where departmentid=1
--ɾ³ý±í
drop table department
--ɾ³ýÊý¾Ý¿â
drop database bai
--ͳ¼ÆÊý¾Ý¿â±íÖмǼ
se ......
LINQ to SQL ÐÔÄÜ 10 Tips
http://www.cnblogs.com/worksguo/archive/2008/06/04/1213075.html
Ç°Ò»ÖÜ£¬ÎÒµÄÓ²ÅÌÓÐÁ½·ÖÇø»µÁË£¬ÎÒ×¼±¸µÄÎÄÕÂÓë×ÊÁ϶¼ÔÚÀïÃ棬ËùÒÔLINQµÄÎÄÕÂÍ£ÁËÒ»¶Îʱ¼ä£¬ÕæµÄÌ«²»ºÃÀ£¬ÎªÀ´ÃÖ²¹Ò»Ï¾ÍÏÈ·¢ÕâƪÎÄÕÂÉÏÀ£¬Ã÷ÌìÔÙһƪ¹ØÓÚDisconnection DataµÄÎÄÕ¡£
Ö»²»¹ýÐҺã¬ÔÚÕ⼸ÌìÖÐÎҽ߾¡È«Á¦ ......
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
select dateadd(day,2,'2004-10-15') --·µ»Ø£º2004-10-17 00:00:00.000
3. datediff ·µ»Ø¿çÁ½¸öÖ¸¶¨ÈÕÆÚµÄÈÕÆÚºÍʱ¼ä ......
ΪÁ˹«Ë¾¿¼ÇÚϵͳµÄÐèÒª
±àдµÄ¼¸¸ö¼òµ¥´æ´¢¹ý³Ì(¿ÉÒÔÊÖ¶¯ÔËÐÐ,Ò²¿ÉÒÔÉèÖÃÊÂÎñ×Ô¶¯ÔËÐÐ!¸Ð¾õ»¹ÐбȽÏͨÓÃ,д³öÀ´¹²ÏíÏÂ)
Calendar±í½á¹¹ºÜ¼òµ¥,2¸ö×Ö¶Î:
fdDate ÈÕÆÚ
fdType ¿¼ÇÚÀàÐÍ(¹¤×÷ÈÕN,ÖÜÄ©W,½Ú¼ÙÈÕH[ÐèÒª¸ù¾ÝÐèÒª×Ô¼ºÐÞ¸Ä])
--ÅжÏÒ»¶Îʱ¼ä·¶Î§ÄڵŤ×÷ÈÕ£¨N£©ºÍÖÜÄ©£¨W£©
Create PR ......