thread 완벽 정리!
카테고리 없음 / 2016. 7. 27. 11:15
http://www.albahari.com/threading/
'2016/07'에 해당되는 글 5건
- 2016.07.27 thread 완벽 정리!
- 2016.07.26 while 문 안에서 주석문은 주의 하세요
- 2016.07.24 윈도우 정품인증 날짜 확인
- 2016.07.20 XE
- 2016.07.01 업데이트 검색
while 문 안에서 주석문은 주의 하세요
카테고리 없음 / 2016. 7. 26. 18:27
comment.sql위 sql 을 받아서 주석중 a 한개만 지우면 read i/o 가 1602 에서 13 으로 줄어듭니다.
반면, while 문이 아닌 구간은 그렇게 큰 영향이 없습니다.
위 파일은 주석이 훨씬 많은데도 i/o 에 별 영향이 없습니다.
위 현상은 sql 2012 2016 등에서 재현 가능합니다.
EXEC master.dbo.xp_create_subdir 'd:\XE'
CREATE EVENT SESSION [SQLTAG_BASELINE_01_B_2012] ON SERVER
ADD EVENT sqlos.WAIT_INFO(
WHERE ([package0].[greater_than_equal_uint64]([duration],(3)))),
ADD EVENT sqlos.WAIT_INFO_EXTERNAL(
WHERE ([package0].[greater_than_equal_uint64]([duration],(3)))),
ADD EVENT sqlserver.RPC_COMPLETED(SET collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_uint64]([duration],(3000)))),
ADD EVENT sqlserver.SP_STATEMENT_COMPLETED(SET collect_object_name=(1),collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_int64]([duration],(3000)))),
ADD EVENT sqlserver.SQL_BATCH_COMPLETED(SET collect_batch_text=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_uint64]([duration],(3000)))),
ADD EVENT sqlserver.SQL_STATEMENT_COMPLETED(SET collect_parameterized_plan_handle=(0),collect_statement=(1)
ACTION(sqlserver.query_hash,sqlserver.sql_text)
WHERE ([package0].[greater_than_equal_int64]([duration],(3000))))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_BASELINE_01_B_2012.XEL',max_file_size=(100),max_rollover_files=(1000))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
CREATE EVENT SESSION [SQLTAG_BLOCKED_REPORTS_03_R_2012] ON SERVER
ADD EVENT sqlserver.BLOCKED_PROCESS_REPORT(
ACTION(package0.callstack,sqlos.cpu_id,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_BLOCKED_REPORTS_03_R_2012.XEL',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
CREATE EVENT SESSION [SQLTAG_ERROR_02_E_2012] ON SERVER
ADD EVENT sqlserver.ERROR_REPORTED(
ACTION(sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
WHERE ([PACKAGE0].[NOT_EQUAL_INT64]([ERROR_NUMBER],(8153)) AND [PACKAGE0].[NOT_EQUAL_INT64]([ERROR_NUMBER],(5707)) AND [ERROR_NUMBER]<>(5701) AND [ERROR_NUMBER]<>(8153)))
ADD TARGET package0.event_file(SET filename=N'D:\XE\SQLTAG_ERROR_02_E_2012.XEL',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=10240 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [SQLTAG_BASELINE_01_B_2012]
ON SERVER
STATE = start;
ALTER EVENT SESSION [SQLTAG_BLOCKED_REPORTS_03_R_2012]
ON SERVER
STATE = start;
ALTER EVENT SESSION [SQLTAG_ERROR_02_E_2012]
ON SERVER
STATE = start;
EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC SP_CONFIGURE 'blocked process threshold (s)', 2
GO
RECONFIGURE WITH OVERRIDE
GO
https://catalog.update.microsoft.com/v7/site/Search.aspx?q=SQL%20SERVER%202012%20SP3%20CU1