본문 바로가기

분류 전체보기

 (518)
쿼리저장소 쿼리스토어는? 명령문을 컴파일/분석한다 -> 컴파일 후에 각 명령어를 실행하고 통계 알람을 받는다. -> 해당되는 통계를 메모리에 캐시하고 쿼리와 실행 계획을 집계한다. -> 정기적으로 설정에 따라 데이터를 새로 고친다. SQL프로파일러보다 성능에 부화를 조금밖에 주지 않는 이유는? 비동기로 실행이 된다. - 3~0.5% 정도의 성능에 영향 - 프로파일러는 40~15%이상의 성능에 영향을 준다. 옵티마이저는 통계를할떼 테이블정보와 인덱스정보를 수집한다. 쿼리 저장소는 변경전, 후의 값을 가지고있어 분석하기 더 편하다. 장점 : 실행계획이 변경되서 성능 저하 원인을 찾기 편함. 인덱스 변경, 컬럼 속성이 변경 된 다음 쿼리속도(실행계획 등)을 확인하고 비교 가능하다. 즉 변경 전/후를 비교해서 무엇때문에 ..
GUI로 sql 쿼리 자동 튜닝 설정, 강제계획 실행, 추적(쿼리저장소) 쿼리튜닝 자동모드는 1. 데이터베이스 옵션에서 작동모드를 읽기/쓰기로 바꿈 2. 모니터링은 15분에 한번씩 통계수집은 1시간. 30일 지난녀석은 날려버림으로 셋팅이 변경됨 -- 데이터베이스를 새로고침을 해줘야 '쿼리저장소'가생김 새로고침 한 후 해당 데이터베이스의 뷰에 들어가 1000개행 보기를 다 눌러버린다 쿼리저장소 -> 회귀된 쿼리 로 15분 뒤에 확인한다. 하기 전 하고난 후 이렇게 쿼리저장소의 목록을에서 그래프가 생긴다. 고변형 쿼리에서 쿼리아이디에 따른 막대를 클릭 한 후 '계획 강제 적용'을 누르면 강제적용이 된다. 테이블을 새로고침하고 인덱스의 비율/페이지 등 다른점을 확인한다 '강제계획이 포함된쿼리'를 열어보면 아까 선택한 쿼리id와 강제 계획된 sql텍스트를 볼 수 있다. 이 외에도 추..
SQL 자동튜닝 설정 코드 -- 10초에 한번씩 사용자가 던지는 쿼리문들을 자동으로 튜닝을 할지 말지 확인을 한다. 지금 자동튜닝되는 쿼리가 있는지 확인하는거 SELECT reason, score, script = JSON_VALUE(details, '$.implementationDetails.script'), planForceDetails.*, estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000, error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCo..
PLAN CACHE에대해 (예상실행계획)내부 결과를 반환하는 캐시쿼리 -- 그림을 표로 표시해줌. ;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT cp.query_hash,cp.query_plan_hash, PhysicalOperator= operators.value('@PhysicalOp','nvarchar(50)'), LogicalOp= operators.value('@LogicalOp','nvarchar(50)'), AvgRowSize= operators.value('@AvgRowSize','nvarchar(50)'), EstimateCPU= operators.value('@EstimateCPU','nvarchar(50)'), EstimateIO=..
최적화가 필요한 PlanCache 캐시쿼리로 확인(찾기) 인덱스가 없는것 /* 최적화가 필요한 PlanCache 확인(찾기) 인덱스가 없는 현재 모든 클랜 캐시를 찾음 */ SELECT dec.usecounts, dec.refcounts, dec.objtype, dec.cacheobjtype, des.dbid, des.text, deq.query_plan FROM sys.dm_exec_cached_plans AS dec CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq WHERE deq.query_plan.exist (N'/ShowPlanXML/BatchSequence/Batch/Statements/Stm..
한번 썼던 PLAN CACHE 쿼리들을 삭제하는 명령어 주기적으로 유지보수 할 때 싹 삭제함... DECLARE @MB decimal(19,3) , @Count bigint, @StrMB nvarchar(20) SELECT @MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN-- 범위를 정해줌 size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 ,@Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END) ,@StrMB = convert(nvarchar(20), @MB) FROM sys.dm_exec_cached_pl..
PLAN CACHE에서 단일 사용계획에 사용되는 메모리양을 확인 할 때 사용하는 쿼리 SELECT objtype AS [CacheType] , count_big(*) AS [Total Plans] , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs] , avg(usecounts) AS [Avg Use Count] , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1] , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1] FROM sys.dm_exec_cached_pl..
PLAN CACHE에서 단일 실행계획을 찾고, 또 삭제하는 캐시 쿼리 명령어 -- 현재 캐시된것들 확인하기 SELECT * FROM SYS.dm_exec_cached_plans GO -- PLAN CACHE에서 단일 실행계획을 찾는 쿼리문(PLAN CACHE사이즈를 최적화하기위함) 아래 출력되는 것들을 재 컴파일하면 속도를 줄일 수 있음 --> 한번만 쓰고 버리는 쿼리들 위주로 재컴파일 필요,,,,, 해당 쿼리문은 재활용 하자!!!! SELECT TEXT, CP.OBJTYPE, CP.SIZE_IN_BYTES FROM SYS.dm_exec_cached_plans AS cp CROSS APPLY SYS.dm_exec_sql_text(CP.PLAN_HANDLE) ST WHERE CP.cacheobjtype = 'Compiled Plan' AND -- Adhoc은 쿼리를 한번 던지는것,..

티스토리툴바