SQL Server2012 Checkpoint Á¦¾î
SQL Server2012 Checkpoint Á¦¾î - Version : SQL Server 2012 SQL Server µ¥ÀÌÅͺ£À̽º ¿£ÁøÀº ÀÚµ¿À¸·Î ÇöÀçÀÇ µ¥ÀÌÅͺ£À̽º¹öÆÛij½Ã¿¡ ÀÖ´Â µ¥ÀÌÅ͸¦ µð½ºÅ©·Î ±â·ÏÇÏ´ÂCHECKPOINT ¸í·ÉÀ» ½ÇÇàÇÑ´Ù. SQL Server ¿£ÁøÀº ¼º´É»óÀÇ ÀÌÀ¯·Î º¯°æ ³»¿ëÀÌ ÀÖÀ» ¶§¸¶´Ù¸Þ¸ð¸®(¹öÆÛij½Ã)¿¡¼ µ¥ÀÌÅͺ£À̽º ÆäÀÌÁö¸¦ ¼öÁ¤Çϸç ÀÌ·¯ÇÑÆäÀÌÁö¸¦ µð½ºÅ©¿¡ ±â·ÏÇÏÁö´Â ¾Ê´Â´Ù. ´ë½Å µ¥ÀÌÅͺ£À̽º ¿£ÁøÀº °¢ µ¥ÀÌÅͺ£À̽º¿¡¼ Á¤±âÀûÀ¸·Î CHECKPOINT¸¦ ½ÇÇà ÇÑ´Ù. CHECKPOINT´Â ÇöÀç ¸Þ¸ð¸®³»ÀÇ ¼öÁ¤µÈ ÆäÀÌÁö(´õƼÆäÀÌÁö)¿Í ¸Þ¸ð¸®ÀÇ Æ®·£Àè¼Ç ·Î±×Á¤º¸¸¦ µð½ºÅ©¿¡ ¾²°í Æ®·£Àè¼Ç ·Î±×¿¡ ´ëÇÑ Á¤º¸µµ ±â·ÏÇÑ´Ù. ÀÌ ±â´ÉÀº SQL ServerÀÇ ¿¹±âÄ¡ ¾ÊÀº Àå¾Ö³ª ½Ã½ºÅÛ Ãæµ¹·ÎÀÎÇÏ¿© µ¥ÀÌÅͺ£À̽º¸¦ º¹±¸Çϴµ¥ µ¥ÀÌÅÍÀÇ ¾ÈÁ¤¼º ¹× ½Ã°£À» Àý¾àÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀÌ µÈ´Ù. [CHECKPOINT] : http://msdn.microsoft.com/ko-kr/library/ms189573.aspx http://msdn.microsoft.com/ko-kr/library/ms188748.aspx SQL Server 2012ÀÇ TARGET_RECOVERY_TIME¿É¼Ç°ú recovery interval ¿É¼ÇÀÇ »óÈ£ ÀÛ¿ë ÀÇ TARGET_RECOVERY_TIME recovery interval »ç¿ëµÇ´Â °Ë»çÁ¡ À¯Çü 0 0 º¹±¸ °£°ÝÀÌ 1ºÐÀÎ ÀÚµ¿ °Ë»çÁ¡ 0 >0 º¹±¸ °£°ÝÀÌ recovery interval ¿É¼Ç °ª »ç¿ë >0 ÇØ´ç »çÇ× ¾øÀ½ º¹±¸ °£°ÝÀÌ TARGET ¿É¼Ç °ª »ç¿ë.(ÃÊ) SSMS¸¦ ½ÇÇàÇÏ¿© ´ÙÀ½ Äõ¸®¸¦ ½ÇÇàÇϸé ÇØ´ç µ¥ÀÌÅͺ£À̽ºÀÇ TARGET_RECOVERY_TIME °ªÀ» È®ÀÎ ÇÒ ¼ö ÀÖ´Ù. SELECT TARGET_RECOVERY_TIME_IN_SECONDS FROM SYS.DATABASES WHERE NAME ='ADVENTUREWORKS2012' SSMSÀÇ UI·Î È®Àεµ°¡´É ÇÏ´Ù. ´ÙÀ½°ú °°ÀÌ ÇØ´ç µ¥ÀÌÅͺ£À̽º¿¡¼ ¸¶¿ì½º ¿À¸¥ÂÊÀ» Ŭ¸¯ÇÏ¿© [¼Ó¼º]À» ¼±Åà ÇÑ´Ù. [µ¥ÀÌÅͺ£À̽º ¼Ó¼º]âÀ̳ªÅ¸³ª¸é [¿É¼Ç] ÅÇ¿¡¼ [º¹±¸] – [´ë»ó º¹±¸ ½Ã°£(ÃÊ)]¿¡¼È®ÀÎ °¡´É ÇÏ´Ù. ½ºÅ©¸³Æ®¸¦ ÀÌ¿ëÇÏ¿© TARGET_RECOVERY_TIME °ªÀ» º¯°æÇÏÀÚ. ½Ç½À¿¡¼´Â 120ÃÊ ¸¶´ÙCHECKPOINT°¡ ¹ß»ýÇϵµ·Ï ¼³Á¤ ÇÏ¿´´Ù. ALTER DATABASE AdventureWorks2012 SET TARGET_RECOVERY_TIME = 120 SECONDS GO SELECT TARGET_RECOVERY_TIME_IN_SECONDS FROM SYS.DATABASES WHERE NAME ='ADVENTUREWORKS2012' SSMSÀÇ UI¿¡¼ º¯°æµµ°¡´ÉÇÏ´Ù. º¯°æ ¹æ¹ýÀº À§¿¡¼ È®ÀÎÇÑ [´ë»ó º¹±¸ ½Ã°£(ÃÊ)]¿¡¼ º¯°æÇϸé Àû¿ë µÈ´Ù. [CHECKPOINT Á¦¾îÀÇ ÀåÁ¡] - ƯÁ¤ µ¥ÀÌÅͺ£À̽ºÀÇ Àüü º¹±¸ ½Ã°£À» ÁÙÀÏ ¼ö ÀÖ´Ù. - IO ºñ¿ëÀ» °í·ÁÇÏ¿© CHECKPOINT¸¦ Á¶Àý ÇÒ ¼ö ÀÖ´Ù. [CHECKPOINT Á¦¾îÀÇ ´ÜÁ¡] - ¸Å¿ì ¹Ù»Û OLTP ȯ°æ¿¡¼ ¼º´É Ãæµ¹ÀÌ ¹ß»ýÇÒ ¼öµµ ÀÖ´Ù. ¹Ýµå½Ã Å×½ºÆ® ȯ°æ¿¡¼ °ËÁõÇÏ°í Àû¿ëÇÏÀÚ.