ããã«ã¡ã¯ãã¢ãŒããã¯ãéšã®å»£ç¬ã§ãã åŒç€Ÿã§ã¯ãµãŒãã¹ã®äžéšã«SQL Serverã䜿çšããŠãããBigQueryäžã®ããŒã¿åºç€ãžããŒãã«ã飿ºããŠããŸãã飿ºã®ä»çµã¿ã¯éåžžã«ããã§ããŠãããã®ã®ãããŒã¿äžæŽåãé
å»¶ãçºçãåŸããšãã課é¡ãæ±ããŠããŸãããããããSQL Serverã®ã¹ãããã·ã§ããåé¢ã¬ãã«ãå°å
¥ããããšã§ãããã解決ã§ããŸãããæ¬èšäºã§ã¯ãæ±ããŠãã課é¡ããã³è§£æ±ºãŸã§ã®æµããšãã¹ãããã·ã§ããåé¢ã¬ãã«ãå°å
¥ããéã«æ°ãä»ããç¹ã玹ä»ããŸãã ããŒã¿åºç€é£æºã®æ¹æ³ãšèª²é¡ ããŒã¿åºç€ãšã®é£æºæ¹æ³ã¯ãæ¥æ¬¡é£æºãšãªã¢ã«ã¿ã€ã 飿ºã®2çš®é¡ã§ããããããã®é£æºæ¹æ³ãšæ±ããŠãã課é¡ã«ã€ããŠèª¬æããŸãã æ¥æ¬¡é£æº 1æ¥1åãSQL Serverå°çšã®äžæ¬ã³ããŒããŒã«ã§ãããbcpãã䜿çšããŠããŒãã«å
šäœã®ããŒã¿ãååŸãã飿ºæ¹æ³ã§ããããŒã¿ååŸæã®SQLã®ã€ã¡ãŒãžã¯ä»¥äžã®éãã§ãã SELECT #{columns} FROM #{@tablename} WITH (NOLOCK) ãã®æ¹æ³ã§ã¯ãããŒãã«ãµã€ãºã®å€§ããã«å¿ããŠããŒã¿ååŸã«ãããæéãé·ããªããŸããSQL Serverã«ãããããã©ã«ãã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã¯ãREAD COMMITTEDãã§ãããã®ããããŠãŒã¶ãŒæäœã«ãã£ãŠçºè¡ãããæŽæ°ã¯ãšãªããããã¯ããŠããŸãæžå¿µãããããããé¿ããããã«ãWITH(NOLOCK)ããä»äžããŠããŸãã ãã®ãWITH(NOLOCK)ããã³ããã€ãããšãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ããREAD UNCOMMITTEDãã«ãªããŸãããã®åé¢ã¬ãã«ã§ã¯ããŒãã£ãªãŒããèš±å¯ãããããããŒã¿ã®èªã¿åãäžã«ããŒãžåå²ãèµ·ãããšãããŒã¿ã®æ¬ æãéè€ãªã©ã®äžæŽåã«ã€ãªãããŸããããŒã¿åºç€ã¯ã¢ããªã®PUSHé
ä¿¡ã«ã䜿ãããŠãããããéè€ãé¿ããããã®å·¥å€«ãé
ä¿¡åŽã§å®è£
ããæéããããŒã¿æ¬ æã«ããæ©äŒæå€±ãçºçããŠããŸããããªãããWITH(NOLOCK)ããã³ããšããŒãžåå²ã®é¢ä¿æ§ã«ã€ããŠã¯ ãã¡ãã®èšäº ã§è©³ãã解説ãããŠããŸãã ãã®ããã«ãREAD COMMITTEDãã§ããREAD UNCOMMITTEDãã§ããããããã«æžå¿µããããŸãããããããã©ã¡ãããåãå
¥ãããããªãããããŠãŒã¶ãŒæäœãžã®æªåœ±é¿ãé¿ããããšãåªå
ããŠãREAD UNCOMMITTEDãåé¢ã¬ãã«ãæ¡çšããŠããŸããã ãªã¢ã«ã¿ã€ã 飿º çŽ1åã«1åãåŒç€Ÿã§éçºãããªã¢ã«ã¿ã€ã ããŒã¿é£æºã®ä»çµã¿ã䜿ããçŽè¿ã§æŽæ°ã®ãã£ãå·®åããŒã¿ã®ã¿ãååŸãã飿ºæ¹æ³ã§ãããªãããªã¢ã«ã¿ã€ã ããŒã¿é£æºåºç€ã«é¢ãã詳ããå
容ã«ã€ããŠã¯ãäžèšã®èšäºããåç
§ãã ããã techblog.zozo.com äžèšèšäºã§ç޹ä»ããŠããããŒã¿ååŸæã®SQLã®ã€ã¡ãŒãžã¯ä»¥äžã®éãã§ãã SELECT a.SYS_CHANGE_OPERATION AS changetrack_type, a.SYS_CHANGE_VERSION AS changetrack_ver, #{columns} FROM CHANGETABLE(CHANGES #{@tablename}, @ååæŽæ°ããããŒãžã§ã³) AS a LEFT OUTER JOIN #{@tablename} ON a.#{@primary_key} = b.#{@primary_key} ãã®æ¹æ³ã§ã¯ãå·®åããŒã¿ã®ã¿ãååŸãããããããŒã¿ã®ååŸãé«éã«å®äºããŸãããã®ãããããŒã¿ååŸã¯ãšãªãä»ã®ã¯ãšãªãé·æéãããã¯ããæžå¿µã¯ã»ãŒãããŸããããããã£ãŠããWITH(NOLOCK)ããã³ããã€ããã«ãREAD COMMITTEDãåé¢ã¬ãã«ã§ã¯ãšãªå®è¡ããŠããŸãã ãããã該åœããŒãã«ãžé·æéã®æŽæ°ã¯ãšãªãå®è¡ãããŠããç¶æ³ã ãšãéã«ããŒã¿ååŸã¯ãšãªããããã¯ãããŠããŒã¿ã®åæé
å»¶ãçºçããããšããããŸããããããã¯ãããŠåŸ
ã¡ç¶ããå Žåã«ããã¯ã®ç¶æ³ãæªåããªããããã¯ãšãªå®è¡æã«ããã¯ã®ã¿ã€ã ã¢ãŠãèšå®ãå
¥ããããã€ã³ã¿ãŒãã«ã60ç§ãšé·ãã«ãšããšãã工倫ãããŠããŸãã 飿ºæ¹æ³ãšèª²é¡ã®ãŸãšã ãããŸã§ã®èª¬æããŸãšãããšã以äžã®éãã§ãã 飿ºã®æ¹æ³ã¯æ¥æ¬¡ãšãªã¢ã«ã¿ã€ã ã®2çš®é¡ãååš æ¥æ¬¡é£æºã§ã¯ãWITH(NOLOCK)ãä»ãã§ãREAD UNCOMMITTEDãåé¢ã¬ãã«ã§ã¯ãšãªãå®è¡ 課é¡ïŒããŒãã£ãªãŒããèš±å¯ããŠãããããããŒã¿ã®æ¬ æãéè€ãªã©ã®äžæŽåãèµ·ããåŸã ãªã¢ã«ã¿ã€ã ããŒã¿é£æºã§ã¯ãREAD COMMITTEDãåé¢ã¬ãã«ã§ã¯ãšãªãå®è¡ 課é¡ïŒä»ã®ã¯ãšãªãæŽæ°ã¯ãšãªãé·æéå®è¡äžã ãšãããããã³ã°ã«ããããŒã¿ã®åæé
å»¶ãèµ·ããåŸã 以éã§ã¯ããããã®èª²é¡ãã©ã®ããã«è§£æ±ºããããé ã«èª¬æããŸãã ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã®æ€èš ä»åã®èª²é¡ã解決ããã«ã¯ããREAD UNCOMMITTEDãåé¢ã¬ãã«ã䜿çšããã«ãä»ã®æŽæ°åŠçã«ãã£ãŠé£æºã¯ãšãªããããã¯ãããªãç¶æ³ãäœãå¿
èŠããããŸãããã®ããã«ã¯ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã倿Žããå¿
èŠããããŸãã ãŸããSQL Serverã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ã«ã€ããŠç°¡åã«èª¬æããŸãã SQL Serverã«ã¯ã5ã€ã®ãã©ã³ã¶ã¯ã·ã§ã³åé¢ã¬ãã«ãçšæãããŠããŸãã READ UNCOMMITTED READ COMMITTED REPEATABLE READ SNAPSHOT SERIALIZABLE ããã©ã«ãã®åé¢ã¬ãã«ã¯ãREAD COMMITTEDãã§ãããããã¯å€æŽã§ããŸããã ãã©ã³ã¶ã¯ã·ã§ã³åäœã§ã®åé¢ã¬ãã«ã¯åå¥ã«æå®å¯èœã§ãæªæå®æã¯ããã©ã«ãã®åé¢ã¬ãã«ãšãªããŸãã åé¢ã¬ãã«ã®æå®ã¯ä»¥äžã®ã¯ãšãªã§å®è¡å¯èœã§ãã SET TRANSACTION ISOLATION LEVEL <åé¢ã¬ãã«å> ãŸããåé¢ã¬ãã«ã§ã¯ãªããã®ã®ããREAD COMMITTEDãã®æåãå€åãããããŒã¿ããŒã¹ãªãã·ã§ã³ãREAD_COMMITTED_SNAPSHOTã(READ COMMITTED SNAPSHOT ISOLATION : RCSI) ãååšããŸãã ãã®ãªãã·ã§ã³ãONã«ãããšãããŒã¿æŽæ°æã«ã³ãããæžã¿ã®ã¬ã³ãŒãïŒãã©ã³ã¶ã¯ã·ã§ã³å
ã§å€æŽããåã®ç¶æ
ã®ããŒã¿ïŒãtempdbãžãšæžã蟌ãŸããããã«ãªããŸãããããŠSELECTã¯ãšãªãå®è¡ããéã¯ãå¿
èŠã«å¿ããŠtempdbã«æ ŒçŽãããã³ãããæžã¿ããŒã¿ãèªã¿åãããšã§ãããã¯ç¡ãã§æŽåæ§ã®ãšããããŒã¿ãååŸã§ããŸãã ãã®ãªãã·ã§ã³ã®ON/OFFãèæ
®ãããšãåé¢ã¬ãã«ã¯ä»¥äžã®6ã€ã«åé¡ã§ããŸãã READ UNCOMMITTED READ COMMITTEDïŒREAD_COMMITTED_SNAPSHOT OFFïŒ READ COMMITTEDïŒREAD_COMMITTED_SNAPSHOT ONïŒ REPEATABLE READ SNAPSHOT SERIALIZABLE ãã®äžã§ãREAD UNCOMMITTEDãåé¢ã¬ãã«ã䜿çšããã«ãä»ã®æŽæ°åŠçã«ãã£ãŠSELECTã¯ãšãªããããã¯ãããªãç¶æ³ãäœãã«ã¯ã READ COMMITTEDïŒREAD_COMMITTED_SNAPSHOT ONïŒ SNAPSHOT ã®ã©ã¡ããã®åé¢ã¬ãã«ïŒïŒãªãã·ã§ã³ïŒãèšå®ããå¿
èŠããããŸãã ãã®ããããã®2çš®é¡ã®éžæè¢ã«ã€ããŠæ¯èŒæ€èšã宿œããŸããã READ COMMITTEDïŒREAD_COMMITTED_SNAPSHOT ONïŒ vs. SNAPSHOT ã©ã®æç¹ã®ããŒã¿ãèªã¿åãã READ COMMITTEDïŒREAD_COMMITTED_SNAPSHOT ONïŒ åã¹ããŒãã¡ã³ãïŒSELECTæïŒãçºè¡ããã¿ã€ãã³ã°ã§ãã³ããããããŠããããŒã¿ SNAPSHOT ãã©ã³ã¶ã¯ã·ã§ã³ãéå§ããã¿ã€ãã³ã°ã§ã³ããããããŠããããŒã¿ åäžãªãœãŒã¹ãžã®æžã蟌ã¿ãç«¶åããå Žåã®æå READ COMMITTEDïŒREAD_COMMITTED_SNAPSHOT ONïŒ ããããã³ã°ãçºç SNAPSHOT ãã©ã³ã¶ã¯ã·ã§ã³ã®éå§åŸãä»ã®ã¯ãšãªã«ãã£ãŠå€æŽãããããŒã¿ã«å¯ŸããŠå€æŽãã³ãããããããšãããšãããŒã«ããã¯ãããšã©ãŒãšãªãïŒè©³çŽ°ã¯ ããã¥ã¡ã³ã ãåç
§ïŒ èªã¿åãã®æåãå€åããç¯å² READ COMMITTEDïŒREAD_COMMITTED_SNAPSHOT ONïŒ ãªãã·ã§ã³ãONã«ããæç¹ã§ãã¹ãŠã®ã»ãã·ã§ã³ã圱é¿ãåããã³ãããæžã¿ããŒã¿ã ããããã¯ç¡ãã§èªã¿åãããã«ãªã æ¢åã¢ããªã±ãŒã·ã§ã³ãžã®åœ±é¿ãããïŒèªã¿åãåŠçãšããŒã¿æŽæ°åŠçãšã®ç«¶åããªããªãïŒ SNAPSHOT SNAPSHOTåé¢ã¬ãã«ãæå®ããã»ãã·ã§ã³ã®ã¿ã圱é¿ãåãã æ¯èŒæ€èšã®çµæ ä»å課é¡ãæ±ããŠããã®ã¯èªã¿åãã®ã¿ã®ã¯ãŒã¯ããŒãã§ãããããã£ãŠãæžã蟌ã¿ã®ç«¶åãèæ
®ããå¿
èŠã¯ãããŸããããŸããäœå¹ŽãéçšãããŠããDBã®ãããREAD_COMMITTED_SNAPSHOTããªãã·ã§ã³ãONã«ãããšæ¢åã®ã¢ããªã±ãŒã·ã§ã³ã®æåã«äºæãã¬å€åãçããæžå¿µããããŸãããäžæ¹ã§ãSNAPSHOTåé¢ã¬ãã«ã®å Žåã¯æç€ºçã«åé¢ã¬ãã«ãæå®ããã»ãã·ã§ã³ã®ã¿ã圱é¿ãåãããããæ¢åã¢ããªã±ãŒã·ã§ã³ã®æåã¯äžåå€åããŸããã 以äžã®èå¯ãèžãŸããæçµçã«SNAPSHOTåé¢ã¬ãã«ãå°å
¥ããããšã«ããŸããã SNAPSHOTåé¢ã¬ãã«ã®å°å
¥ SNAPSHOTåé¢ã¬ãã«ã«åãæ¿ããããã«ã¯ã該åœã»ãã·ã§ã³ã§ä»¥äžã®ã¯ãšãªãå®è¡ããŸãã SET TRANSACTION ISOLATION LEVEL SNAPSHOT ãã ããããŒã¿ããŒã¹ãªãã·ã§ã³ãALLOW_SNAPSHOT_ISOLATIONããæå¹åãããŠããå¿
èŠããããŸãã ALTER DATABASE <ããŒã¿ããŒã¹å> SET ALLOW_SNAPSHOT_ISOLATION ON ãã®ãªãã·ã§ã³ãéçšäžã®æ¬çªç°å¢ã«é©çšããéã«ã¯æ³šæç¹ãããã®ã§ç޹ä»ããŸãã å°å
¥æã®æ³šæç¹ ãALLOW_SNAPSHOT_ISOLATIONãã®æå¹åã¯ãªã³ã©ã€ã³ã§å®æœå¯èœã§ãã ãã ãããALTER DATABASEãå®è¡ããåã«éå§ããããã©ã³ã¶ã¯ã·ã§ã³ããååšããéããALTERæã®å®è¡ã¯å®äºããŸããããENABLE_VERSIONINGããšããåŸ
ã¡äºè±¡ã§åŸ
ã¡ç¶ããããšã«ãªããŸãã ãªãããALTER DATABASEãå®è¡ããåŸã«æ°ãã«éå§ããããã©ã³ã¶ã¯ã·ã§ã³ãã«ã€ããŠã¯ALTERæã®å®è¡ã劚ããããšã¯ãããŸããã ããã¥ã¡ã³ã ã«ã¯ä»¥äžã®èšèŒããããŸãã ALLOW_SNAPSHOT_ISOLATION ãæ°ããç¶æ
ã« (ON ãã OFF ãžããŸã㯠OFF ãã ON ãž) èšå®ããå ŽåãALTER DATABASE ã¯ãããŒã¿ããŒã¹å
ã«ãããã¹ãŠã®æ¢åã®ãã©ã³ã¶ã¯ã·ã§ã³ãã³ãããããããŸã§ãåŒã³åºãå
ã«å¶åŸ¡ãè¿ããŸããã ããŒã¿ããŒã¹ãæ¢ã« ALTER DATABASE ã¹ããŒãã¡ã³ãã§æå®ããç¶æ
ã«ããå Žåã«ã¯ãå¶åŸ¡ã¯åŒã³åºãå
ã«çŽã¡ã«è¿ãããŸãã å®éã«åŒç€Ÿã®ç°å¢ã§å°å
¥ããéã¯ãç¬æã«å®äºããDBãããã°ãå®äºãŸã§90ç§çšåºŠããã£ãDBããããŸããã åºæ¬çã«ãã®ALTERæãä»ã®ã¯ãšãªããããã¯ããããšã¯ç¡ãèªèã§ãããäžäžã®äºæ
ã«åããALTERæã®å®è¡äžã¯åžžã« sys.dm_exec_requests ã䜿ããå®è¡äžã®ã¯ãšãªã§ããããã³ã°ãçºçããŠããªãããç£èŠããããšãããããããŸãã å°å
¥åŸã®æ³šæç¹ å°å
¥åŸã¯ãããŒã¿ã®æžã蟌ã¿ãçºçãã床ã«tempdbã«ã³ãããæžã¿ã®ã¬ã³ãŒãæ
å ±ãæžã蟌ãŸããããã«ãªããããtempdbã®è² è·ãäžæããŸãã ãã®æ§è³ªã念é ã«ãããŠãããã©ãŒãã³ã¹ã¢ãã¿ã®ä»¥äžã®ã¡ããªã¯ã¹ã§ç®ç«ã£ãå€åãç¡ããã確èªããŸãã CPUã®é«éš°ãã¿ãããªãã ProcessorÂ¥% Processor Time åæå®è¡æ§ã®äœäžã¯ã¿ãããªãã SQLServer:StatisticsÂ¥Batch Requests/sec SQLServer:General StatisticsÂ¥Processes blocked ãè¡ã®ããŒãžã§ã³ç®¡çãé¢é£ã¡ããªã¯ã¹ã§æ°ã«ãªãå€åã¯ãªãã SQL Server:TransactionsÂ¥Free Space in tempdb(KB) SQL Server:TransactionsÂ¥Version Store Size(KB) SQLServerTransactionsÂ¥Version Cleanup rate (KB/s) SQL Server:TransactionsÂ¥Version Generation rate (KB/s) tempdbã®ãã£ã¹ã¯è² è·ã¯åé¡ãªãã Physical Disk (tempdbã®ãã©ã€ã)Â¥Disk Read Bytes/Sec Physical Disk (tempdbã®ãã©ã€ã)Â¥Disk Write Bytes/Sec Physical Disk (tempdbã®ãã©ã€ã)Â¥Current Disk Queue Length è¡ã®ããŒãžã§ã³ç®¡çã«äœ¿çšããtempdbã®é åã¯ã宿çã«èªåã§ã¯ãªãŒã³ã¢ãããããŸããé åãµã€ãºãå¢ãç¶ããã«ã宿çã«æžå°ããã¿ã€ãã³ã°ïŒè¡ã®ããŒãžã§ã³ç®¡çã®ã¯ãªãŒã³ã¢ããïŒãããããšå¿
ã確èªããŸãã ããããŠãsys.dm_exec_requestsã䜿ã£ãŠããªã¢ã«ã¿ã€ã ã§ã¯ãšãªã®åæå®è¡æ§ã«ã€ããŠã確èªããŠãããšããå®å¿ã§ããåºæ¬çã«ã¯ãäžèšå
å®¹ã«æ°ãä»ãã€ã€å°å
¥ããã³å°å
¥åŸã®è©äŸ¡ã宿œããã°ãå®å¿ããŠSNAPSHOTåé¢ã¬ãã«ã䜿çšã§ããããšæããŸãã ç£èŠé
ç® ãALLOW_SNAPSHOT_ISOLATIONããæå¹åããåŸã¯ã以äžã®2ç¹ã¯å¿
ãç£èŠããŸãããã tempdbã®å®¹ééŒè¿«ã®æ€ç¥ ç°å¢ã«ãã£ãŠã¯ã倧éã®ããŒã¿æŽæ°ãªã©ã®çç±ã§tempdbã®ç©ºã容éã®æ¯æžãæžå¿µãããŸãã90ïŒ
ãè¶
ãããã¢ã©ãŒããçºå ±ãããªã©ãæ€ç¥ã§ããä»çµã¿ãçšæããŠãããŸãããã é·æééãã£ã±ãªãã®ãã©ã³ã¶ã¯ã·ã§ã³ã®æ€ç¥ è¡ã®ããŒãžã§ã³ç®¡çã®ã¯ãªãŒã³ã¢ããã«ã€ããŠã ããã¥ã¡ã³ã ã«ä»¥äžã®èšèŒããããŸãã ããŒãžã§ã³ã¹ãã¢ã«æ ŒçŽãããŠããããŒãžã§ã³ã¯ãè¡ã®ããŒãžã§ã³ç®¡çã«åºã¥ãåé¢ã¬ãã«ã§å®è¡ããããã©ã³ã¶ã¯ã·ã§ã³ã§å¿
èŠãªéãä¿æãããŸãã SQL Server ããŒã¿ããŒã¹ ãšã³ãžã³ã«ãããå¿
èŠãªãã©ã³ã¶ã¯ã·ã§ã³ ã·ãŒã±ã³ã¹çªå·ã®äžã§æãå°ããçªå·ã远跡ãããããããããã©ã³ã¶ã¯ã·ã§ã³ ã·ãŒã±ã³ã¹çªå·ãå°ããè¡ã®ããŒãžã§ã³ã¯å®æçã«ãã¹ãŠåé€ãããŸãã ã€ãŸããéãã£ã±ãªãã®ãã©ã³ã¶ã¯ã·ã§ã³ããããšããã®ãã©ã³ã¶ã¯ã·ã§ã³ããåŸã«éå§ããããã©ã³ã¶ã¯ã·ã§ã³ã«ãã£ãŠtempdbã«æžã蟌ãŸããããŒã¿ã¯ãã€ãŸã§ãã¯ãªãŒã³ã¢ãããããŸããããã®ç¶æ³ã«ãªããštempdbã®å®¹ééŒè¿«ã«ã€ãªããæžå¿µããããããäŸãã°ä»¥äžã®ãããªã¯ãšãªã宿çã«å®è¡ããŠã¬ã³ãŒããååŸã§ããå Žåã¯éç¥ããä»çµã¿ãçšæããŠãããŸãã -- 60å以äžéãã£ã±ãªãã®ãã©ã³ã¶ã¯ã·ã§ã³ãæ€ç¥ SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP ( 1 ) ' transaction_time_min: ' + isnull( cast (datediff(minute, transaction_begin_time, getdate()) AS VARCHAR ( max )), '' ) + ' / session_id: ' + isnull( cast (es.session_id AS VARCHAR ( max )), '' ) + ' / host_name: ' + isnull( cast (host_name AS VARCHAR ( max )), '' ) + ' / program_name: ' + isnull( cast (program_name AS VARCHAR ( max )), '' ) + ' / status: ' + isnull( cast (es.STATUS AS VARCHAR ( max )), '' ) + ' / last_request_end_time: ' + isnull( cast (last_request_end_time AS VARCHAR ( max )), '' ) + ' / text: ' + isnull( cast (TEXT AS VARCHAR ( max )), '' ) AS result FROM sys.dm_tran_session_transactions ts JOIN sys.dm_exec_sessions es ON es.session_id = ts.session_id JOIN sys.dm_tran_active_transactions at ON at.transaction_Id = ts.transaction_id LEFT JOIN sys.dm_exec_requests der ON es.session_id = der.session_id OUTER APPLY sys.dm_exec_sql_text(sql_handle) AS dest WHERE datediff(minute, transaction_begin_time, getdate()) > 60 ORDER BY datediff(minute, transaction_begin_time, getdate()) DESC å°å
¥å¹æ ãALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ãæå¹åããããŒã¿åºç€ãžã®é£æºã¯ãšãªã ããSNAPSHOTãåé¢ã¬ãã«ã䜿çšããããšã§ãæ±ããŠãã以äžã®èª²é¡ã解決ã§ããŸããã æ¥æ¬¡é£æºïŒWITH(NOLOCK)ã€ãã®ã¯ãšãªãå®è¡ããããšã«ããããŒã¿äžæŽå ããããã³ã°ã®æžå¿µãç¡ããªã£ããããWITH(NOLOCK)ãå€ãããšãã§ãã ãªã¢ã«ã¿ã€ã 飿ºïŒãREAD COMMITTEDãåé¢ã¬ãã«ã§ã¯ãšãªãå®è¡ããéã«ä»ã®ã¯ãšãªã«ãããã¯ããã ãããã¯ãããããšãç¡ããªã£ããããé
å»¶ãçºçããªããªãããŒã¿åºç€ãžã®é£æºãå®å®ãã ãããã¯ããããšãç¡ããªã£ããããåæã®ã€ã³ã¿ãŒãã«ãçãèšå®ããŠããæ©ã飿ºã§ããããã«ãªã£ã ãŸããæ¢åã®ã¢ããªã±ãŒã·ã§ã³ã®æåã«ã€ããŠã¯äžåå€åããªããããäºæãã¬äžå
·åãçºçããããšãé¿ããããšãã§ããŸããã å°å
¥åŸã«èµ·ããåé¡ ã»ãšãã©ã®DBã¯äžèšå
容ã§ã¹ã ãŒãºã«å°å
¥ã§ããŸããããäžéšã®DBã§ã¯å°å
¥åŸã«åé¡ãçºçããŠåãæ»ããŸãããçºçããåé¡ç¹ãšãçå®ããè§£æ±ºæ¹æ³ã玹ä»ããŸãã ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã®ããã¯ã¢ãããµã€ãºãæ¥æ¿ã«è¥å€§ ãALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ãæå¹åããããšã«ç¹å®ã®DBã ãããã©ã³ã¶ã¯ã·ã§ã³ãã°ãã¡ã€ã«ã®ããã¯ãµã€ãºãçŽ100åã«è¥å€§ããŸãããtempdbãžã®æžã蟌ã¿ãå¢å ããããšã¯èªèããŠããŸãããããŠãŒã¶ãŒDBã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ãã¡ã€ã«ããããŸã§æ¥æ¿ã«è¥å€§ããããšã¯èæ
®ã§ããŠããŸããã§ããã ãã°è¥å€§ã®åå èª¿æ» ãALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ãæå¹åããååŸã®ããã¯ã¢ãããã°ãã¡ã€ã«ãããŒãã«ã«ãã³ãããŠè§£æããŸããã ãŸãã以äžã®ã¯ãšãªã§ãã°ãã¡ã€ã«ãããŒãã«ã«INSERTããŸãã SELECT * INTO tran_log_dump FROM sys.fn_dump_dblog( NULL , NULL , NULL , 1 , N ' C:\***\backup_log_file.trn ' , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL ) 次ã«ãINSERTããããŒãã«ãOperationïŒINSERT/DELETEãªã©ïŒãContextïŒHEAP/CLUSTEREDãªã©ïŒãAllocUnitIdïŒããŒãã«ïŒåäœã§éèšããåèšã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ãµã€ãºã倧ããé ã«è¡šç€ºããŸããã SELECT * , SUM (cnt) OVER () AS sum_cnt , SUM (sum_log_record_length) OVER () AS sum_all_log_record_length , SUM (sum_log_reserve) OVER () AS sum_all_log_reserve FROM ( SELECT Operation ,Context ,AllocUnitId , COUNT (*) AS cnt , SUM ( CAST ([ Log Record Length ] AS BIGINT)) AS sum_log_record_length --åäœïŒbyte , SUM ( CAST ([ Log Reserve] AS BIGINT)) AS sum_log_reserve --åäœïŒbyte FROM tran_log_dump WITH (NOLOCK) GROUP BY Operation ,Context ,AllocUnitId ) AS A ORDER BY sum_log_record_length DESC äžã®å³ã¯ããALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ãæå¹åããåŸã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã§ããå³ã®ããã«ã1äœã ããã°ãµã€ãºãããã°ã®ä»¶æ°ãçªåºããŠããŸãããæå¹ååã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ãšæ¯èŒãããšãLOP_MODIFY_ROWïŒè¡ã®UPDATEïŒã®åºçŸåæ°ãçŽ2500åã«ãªã£ãŠããŸããã ã©ã®ããŒãã«ãžã®UPDATEã倧éã«è¡ãããŠããã®ã確èªããããã«ãAllocUnitIdã䜿ã£ãŠããŒãã«åã解決ããŸããã SELECT allocation_unit_id, object_name(object_id) FROM sys.allocation_units WITH (NOLOCK) JOIN sys.partitions WITH (NOLOCK) ON container_id = hobt_id WHERE allocation_unit_id IN ( 12 * * * * 34 ) ãã®åŸã該åœã®ããŒãã«ã®UPDATEåæ°ãdm_db_index_operational_statsã䜿ã£ãŠç¢ºèªããŸããã SELECT * FROM sys.dm_db_index_operational_stats(db_id( ' DatabaseName ' ), NULL , NULL , NULL ) WHERE object_id = object_id( ' TableName ' ) AND database_id = db_id( ' DatabaseName ' ) å³ã®ããã«ãUPDATEåæ°ãçŽ6å
åãšãINSERTãDELETEãšãã£ãä»ã®æäœãšæ¯ã¹ãŠãçªåºããŠå€§ããªå€ãšãªã£ãŠããŸããããã®ããŒãã«ãžUPDATEããŠããåŠçã¯ã©ããã£ããã®ãããã®ããã£ãã·ã¥ãã確èªãããšããã5åã«1åã®ããŒã¹ã§å®æçã«è©²åœããŒãã«ãæŽæ°ããŠãããããåŠçããããŸããããã®åŠçã®ã¯ãšãªã¯ã該åœããŒãã«ã®ã»ãŒå
šã¬ã³ãŒãã«å¯ŸããŠUPDATEãå®è¡ããŠããŸãããã倧åã®ã¬ã³ãŒãã¯åãå€ã§UPDATEãããŠããããšãåãããŸããããã®ãããåãå€ã§ã«ã©ã ãUPDATEãããšãã®æåã«ã€ããŠããALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ã®æå¹åååŸã§æ¯èŒã宿œããŸããã ãALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ã®æå¹åååŸã§ã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã®æ¯èŒ 以äžã®æ€èšŒçšã®ã¯ãšãªã¯ãåäžã®ããŒãã«ãåãã¬ã³ãŒãæ°ãåãå€ã§UPDATEããéã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã®äžèº«ã確èªããã¯ãšãªã§ãããALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ã®ç¡å¹åæãšæå¹åæã®çµæããããã確èªã§ããŸãã SET NOCOUNT ON GO DROP TABLE IF EXISTS UpdateTest GO CREATE TABLE UpdateTest ( C1 INT PRIMARY KEY CLUSTERED ,C2 INT ,C3 INT ,C4 INT ,C5 INT ) GO -- 10000ã¬ã³ãŒããã©ã³ãã ãªå€ã§INSERT DECLARE @cnt INT = 1 BEGIN TRAN WHILE (@cnt <= 10000 ) BEGIN INSERT INTO UpdateTest VALUES (@cnt, RAND() * 100 , RAND() * 100 , RAND() * 100 , RAND() * 100 ) SET @cnt += 1 END COMMIT TRAN; GO -- ãã°ããã¯ã¢ããã«ãããã©ã³ã¶ã¯ã·ã§ã³ãã°ãåãæšãŠã CHECKPOINT BACKUP DATABASE TEST TO DISK = N ' NUL ' CHECKPOINT BACKUP LOG TEST TO DISK = N ' NUL ' GO -- ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã®äžèº«ããã§ãã¯ïŒãã®æç¹ã§ã¯ç©ºã£ãœã®ã¯ãïŒ SELECT * FROM sys.fn_dblog( NULL , NULL ) WHERE AllocUnitName LIKE ' %UpdateTest% ' GO -- ãALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ç¡å¹å ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF GO -- C1=1ã®ã«ã©ã ã®ååšãã§ã㯠SELECT * FROM UpdateTest WHERE C1 = 1 GO -- åãå€ã§ã«ã©ã ãUPDATE UPDATE UpdateTest SET C2 = C2 WHERE C1 = 1 GO 10 -- ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã®äžèº«ããã§ãã¯ïŒããã®çµæãæ¯èŒãããïŒ SELECT * FROM sys.fn_dblog( NULL , NULL ) WHERE AllocUnitName LIKE ' %UpdateTest% ' -- ãALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³æå¹å ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON GO -- åãå€ã§ã«ã©ã ãUPDATE UPDATE UpdateTest SET C2 = C2 WHERE C1 = 1 GO 10 -- ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã®äžèº«ããã§ãã¯ïŒããã®çµæãæ¯èŒãããïŒ SELECT * FROM sys.fn_dblog( NULL , NULL ) WHERE AllocUnitName LIKE ' %UpdateTest% ' äžèšã¯ãšãªã®å®è¡çµæã¯ä»¥äžã®ããã«ãªããŸããããALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ã®ç¡å¹åæïŒäžæ®µïŒã¯ãåãå€ã§UPDATEããå Žåã¯ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã«æžã蟌ãŸããŠããŸãããäžæ¹ã§ãæå¹åæïŒäžæ®µïŒã¯åãå€ã§UPDATEããŠããã©ã³ã¶ã¯ã·ã§ã³ãã°ã«æžã蟌ã¿ãè¡ãããããã«ãªã£ãŠããŸããã ãALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ãæå¹åãããšãåã¬ã³ãŒãã«ããŒãžã§ã³æ
å ±ã®ã¿ã€ã ã¹ã¿ã³ããä¿æããããã«ãªããŸããä»åã®å®éšã«ãããæåã®éãã¯ãæå¹åæã¯ã¿ã€ã ã¹ã¿ã³ãã ããæŽæ°ããããã®æŽæ°æ
å ±ããã©ã³ã¶ã¯ã·ã§ã³ãã°ã«æžã蟌ãŸãããã®ãšæšæž¬ãããŸãã æ¬¡ã«ããã®æšæž¬éãã®æåã«ãªã£ãŠãããã確èªããŸããããALLOW_SNAPSHOT_ISOLATIONããªãã·ã§ã³ãæå¹åããç¶æ
ã§ãåãå€ã§UPDATEããååŸã®ããŒã¿ããŒãžã®äžèº«ã確èªããŸãã DBCC TRACEON( 3604 ) DBCC PAGE (N ' TEST ' , 1 , 2875640 , 3 ) WITH TABLERESULTS DBCC TRACEOFF( 3604 ) GO UPDATE UpdateTest SET C2 = C2 WHERE C1= 1 GO 10 DBCC TRACEON( 3604 ) DBCC PAGE (N ' TEST ' , 1 , 2875640 , 3 ) WITH TABLERESULTS DBCC TRACEOFF( 3604 ) æšæž¬éããUPDATEã®ååŸã§è¡ã®ããŒãžã§ã³æ
å ±ã§ããããTransaction TimestampããæŽæ°ãããŠããããšã確èªã§ããŸããã ãããã£ãŠããã°è¥å€§ã®åå ã¯ããã©ã³ã¶ã¯ã·ã§ã³ãã°ãžã®æžã蟌ã¿ã®æåããªãã·ã§ã³æå¹åã«ãã£ãŠå€åããåãå€ã§å€§éã®ã¬ã³ãŒããæŽæ°ããŠããåŠçããã°ãžæžã蟌ãŸããããã«ãªã£ãããããšå€æã§ããŸãã 察å¿ç åé¡ãšãªã£ããããåŠçã§ã¯ãã»ãšãã©ã®ã¬ã³ãŒãã¯åãå€ã§UPDATEãããŠãããããå€åããã£ãã¬ã³ãŒãã ããæŽæ°ããå·®åæŽæ°ã«åŠçãä¿®æ£ããããšã§ãã°è¥å€§ãæãããããšèããããŸãããªãªãŒã¹ã«åããŠçŸåšå¯Ÿå¿äžã§ãã ãŸãšã æ¬èšäºã§ã¯ãSQL ServerããããŒã¿åºç€ãžãšããŒã¿ã飿ºããéã«æ±ããŠãã課é¡ã«ã€ããŠèª¬æããã¹ãããã·ã§ããåé¢ã¬ãã«ãå°å
¥ããããšã§èª²é¡ã解決ãããŸã§ã®æµãïŒåé¢ã¬ãã«ã®éžå®ãå°å
¥ååŸã®æ³šæç¹ãå°å
¥åŸã®åé¡ç¹ïŒã玹ä»ããŸãããåããããªèª²é¡ãæ±ããŠããæ¹ã®åèã«ãªãã°å¹žãã§ãã æåŸã« ZOZOãã¯ãããžãŒãºã§ã¯ãäžç·ã«ãµãŒãã¹ãäœãäžããŠãããæ¹ãåéäžã§ãããèå³ã®ããæ¹ã¯ã以äžã®ãªã³ã¯ãããã²ãå¿åãã ããïŒ tech.zozo.com