SQL 2005 부터 인덱스와 관련하여 개선된 기능 중 하나인 온라인 인덱스 작업에 대하여 알아 보겠습니다. 온라인 인덱스 작업은 1년 365일 운영되어야 하는 시스템을 관리하는 DBA들에게 획기 적인 기능입니다. 온라인 인덱스를 할수 있게 됨으로써 다운 타임을 감소할수 있게 되었으며 인덱스를 재구성할려고 할때도 소요 시간 때문에 많은 고민을 해야 하는 수고를 덜어 줄수 있게 되었습니다. Create Index , Alter Index , Drop index Alter Table 명령어에서 ONLINE 옵션을 사용할수 있습니다.

온라인 인덱스 구성 vs. 오프라인 인덱스 구성ONLINE 옵션은 이러한 인덱스 작업을 수행하는 동안에도 사용자가 테이블이나 클러스터형(Clustered) 인덱스와 관련된 비클러스터형(Nonclustered) 인덱스에 접근할수 있습니다. 오프라인으로 클러스터형 인덱스를 구성하거나 재구성하는 등의 DDL 작업을 하면 이는 해당 데이터와 관련 인덱스에 배타적 잠금을 보유하게 되고 이로 인해 다른 사용자가 데이터나 인덱스에 접근하지 못하도록 방해하게 된다.

Example:

CREATE INDEX idx_t ON t(c1, c2)

WITH (ONLINE = ON)

 

일반 인덱스 구성 vs. 병렬 인덱스 구성멀티 프로세서를 가진 SQL서버에서 인덱스 구문 또한 다른 쿼리를 실행할 때처럼, 스캔, 정렬, 그리고 구성 작업을 수행하는데 병렬 처리가 가능 해졌습니다. 병렬 처리 수는 최대 병렬 처리 수(sp_configure로 설정한), MAXDOP 인덱스 옵션, 현재 작업부하의 크기, 파티션되지 않은 경우, 첫 번째 키 칼럼의 데이터 분포등에 의해서 결정될 수 있다.

 

Example:

CREATE INDEX idx_t ON t(c1, c2)

WITH (MAXDOP = 2)

-- 인덱스 구성에 2개의 프로세서 사용

 

온라인 인덱스 가능한 SQL 버전Microsoft SQL Server 2005 Enterprise Edition

 

온라인 인덱스는 어떻게 동작 할까?

 

 

인덱스 DDL(데이터 정의 언어) 작업 중에 동시 사용자 작업이 가능하도록 하려면 온라인 인덱스 작업 중에 원본 및 기존 인덱스, 대상 및 임시 매핑 인덱스(클러스터형 인덱스의 경우) 로 구성됨으로써 온라인 인덱스 작업 수행을 할수 있도록 합니다.

원본 및 기존 인덱스원본은 원래 테이블이거나 클러스터형 인덱스 데이터입니다. 기존 인덱스는 원본 구조와 관련된 모든 비클러스터형 인덱스입니다. 예를 들어 온라인 인덱스 작업이 네 개의 관련 비클러스터형 인덱스가 있는 클러스터형 인덱스를 재구성하는 것이라면 원본은 기존 클러스터형 인덱스이고 기존 인덱스는 비클러스터형 인덱스입니다.기존 인덱스는 여러 사용자의 동시 선택, 삽입, 업데이트 및 삭제 작업에 사용됩니다. 여기에는 대량 삽입(지원되지만 권장하지 않음)과 트리거 및 참조 무결성 제약 조건에 의한 암시적 업데이트가 포함됩니다. 쿼리와 검색에 모든 기존 인덱스를 사용할 수 있습니다. 즉, 기존 인덱스를 쿼리 최적화 프로그램에서 선택할 수 있으며 필요한 경우 인덱스 힌트에 지정할 수 있습니다.

대상대상은 만들거나 다시 작성하는 새 인덱스(또는 힙)이거나 새 인덱스 집합입니다. SQL Server 데이터베이스 엔진에서는 인덱스 작업을 수행하는 동안 원본에 대한 사용자 삽입, 업데이트 및 삭제 작업을 대상에 적용합니다. 예를 들어 온라인 인덱스 작업이 클러스터형 인덱스를 다시 작성하는 것이라면 대상은 다시 작성되는 클러스터형 인덱스입니다. 데이터베이스 엔진에서는 클러스터형 인덱스를 다시 작성할 때 비클러스터형 인덱스를 다시 작성하지 않습니다.대상 인덱스는 인덱스 작업이 커밋될 때까지 SELECT 문을 처리하는 동안 검색되지 않습니다. 내부적으로 인덱스가 쓰기 전용으로 표시됩니다.

임시 매핑 인덱스클러스터형 인덱스를 만들거나 삭제하거나 다시 작성하는 온라인 인덱스 작업에는 임시 매핑 인덱스도 필요합니다. 이러한 임시 인덱스는 동시 트랜잭션이 기본 테이블의 행이 업데이트되거나 삭제될 때 작성되는 새 인덱스에서 삭제할 레코드를 결정하는 데 사용합니다. 이러한 비클러스터형 인덱스는 새 클러스터형 인덱스(또는 힙)와 같은 단계에서 만들어지므로 별도의 정렬 작업이 필요 없습니다. 동시 트랜잭션의 모든 삽입, 업데이트 및 삭제 작업에서도 임시 매핑 인덱스가 유지됩니다.

 

참고 사이트

 

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/08/index-build-strategy-in-sql-server-introduction-i.aspxhttp://technet.microsoft.com/ko-kr/library/ms191261.aspx

 

 

출처: https://gdbt.tistory.com/68 [Gravity DB Team]

'Database > SQL Server' 카테고리의 다른 글

SQL Server 대기 통계 모니터링 : CPU 대기 해석  (0) 2020.08.29
NUMA 설정 가이드  (0) 2020.08.29
TEMPDB DBFILE 삭제  (0) 2020.08.29
index  (0) 2020.08.29
쿼리 저장소 (Query Store)  (0) 2020.08.29

머신 사양들이 좋아지면서 해당 리소스를 충분히 활용하기 위해 

 

 

 

그리고 mssql 의 성능 향상을 위해 tempdb 파일을 늘리는데.. 

 

 

 

여러 자료를 조합해 본 결과 tempdb 의 데이터 파일은 머신의 코어 수와 맞추면 된단다..

 

 

 

단 요즘은 코어가 하도 많아서... 8개 이상은 의미가 없다... 고 한다 

 

 

 

뭐 스트레스 테스트까지는 해보지 않아서 정확한건 모르겠지만.. 

 

 

 

선인들이 해보셨으면 나도 비슷하다 생각하고... 파일을 늘려놨는데...

 

 

 

 

 

파일 늘리기 전 상황을 캡쳐해놓은것이 없어 파일을 늘린것이 얼마나 효과가 있는지 잘 모르겠다.. 

 

 

 

그래서 역으로 테스트를 해보려 한다.. 

 

(tempdb datafile이 8개인경우 먼저 캡쳐 && 4개 줄여서 테스트 ㅋㅋ)

 

 

 

근데 웃긴건 tempdb datafile 이 만들때는 잘 만들어지는데 지워지지 않는다 ㅋㅋㅋㅋㅋ

 

 

 

 

 

 

 

그래서 잘~~~ 찾아보니.. datafile이 비어지지 않아서란다 ㅋㅋㅋ

 

 

 

자동으로 비우고 해주면 되지... 알파고도 나온마당에 뭘 또 에러 띄우고 그러시나.. 

 

 

 

 

 

암튼 지우기 전에 아래의 쿼리를 실행하고 돌리쟈.. 

 

 

 

==================================================================

 

USE [tempdb]

 

GO

 

DBCC SHRINKFILE ([데이터파일명], EMPTYFILE);

 

GO

 

 

 

use [master]

 

ALTER DATABASE [tempdb]  REMOVE FILE [데이터파일명]

 

 

 

GO

 

===================================================================

 

 

 

그럼 겁내 잘지워진다 ㅎㅎㅎㅎ

 

 

 

 

 

 

 

근데... 실제 사용자들이 사용하는 DB는 아래의 메세지와 함께 또.. 지워지지 않는다.. 

 

 

 

=========================================================================================

 

 

 

DBCC SHRINKFILE: 페이지 9:1320은(는) 작업 테이블 페이지이므로 이동할 수 없습니다.

 

메시지 2555, 수준 16, 상태 2, 줄 3

 

파일 "tempdb8"의 일부 내용을 다른 위치로 이동할 수 없어 emptyfile 작업을 완료할 수 없습니다.

 

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

 

 

 

==========================================================================================

 

 

 

이유인 즉슨.. 해당 데이터파일이 사용중이다..(내지는 다른데가 사용중이라 비울수 없다..)

 

 

 

그래서 찾아보니... (http://sqlserverzest.com/2012/10/07/msg-2555-level-16-state-1-line-1-error-dbcc-shrinkfile-with-emptyfile-option/

 

 

 

위와 같은 메세지가 날때는 

 

 

 

1. 외부 커넥션을 다 끊던지..

 

2. 서버를 재시작 하라고 하신다 ㅎㅎㅎㅎ

 

 

 

실운영 서버라 테스트는 못했지만.. 꼭 필요한 경우라면 진행 가능할듯 하다. ㅎ

 

 

 

그럼 오늘의 정리는 이만 ㅎ

 

'Database > SQL Server' 카테고리의 다른 글

NUMA 설정 가이드  (0) 2020.08.29
온라인 인덱스 구성(Online Index)  (0) 2020.08.29
index  (0) 2020.08.29
쿼리 저장소 (Query Store)  (0) 2020.08.29
동적쿼리  (0) 2020.08.29

페이지(Page)란?

  • 데이터 파일을 구성하는 논리 단위

  • SQL Server의 기본 데이터 저장 단위(8KB)

  • 데이터를 쓸 때 행을 페이지에 기록됨

  • 데이터를 읽을 때 페이지 내의 모든 행이 읽어짐

  • 페이지 내의 행이 많을 수록 I/O 효율 증가

  • 0 ~ n 사이의 순차적인 번호 -> 페이지 번호

데이터를 INSERT 하게 된다면 페이지에 들어가게 되고, 테이블에 쿼리를 날려 조회하는 것이 아니라 페이지에서 SELECT하는 것으로 생각하면 됩니다. 그러므로 페이지라는 것을 숙지해야합니다.

 

인덱스(Index) 는 왜 사용하고 장점은 무엇인가?

  • 빠른 데이터를 검색하기 위해서입니다. 찾는 데이터를 가지고 있다면 직접 주거나, 없다면 어디 있는지 알려줍니다.

  • 데이터의 중복을 방지할 수 있습니다. (Primary Key, Unique)

  • 잠금을 최소화 시켜줍니다. (동시성을 높여줍니다.)

 

인덱스(Index) 의 단점은 무엇인가?

  • 물리적인 공간을 차지하게 됩니다. (인덱스도 테이블처럼 데이터를 가지므로 물리적인 공간을 차지하게 됩니다.)

  • 페이지를 가지고 있는 존재는 데이터와 인덱스 두가지입니다. (프로시져/뷰는 사이즈가 없습니다.)

  • 인덱스에 대한 유지관리 부담이 존재합니다.

  • 데이터가 극히 적다면은 얻는 효과보다 유지관리 부담이 더 클 수 있습니다.

테이블의 존재 형태는 아래 그림 처럼 두 가지로 나뉩니다. 힙(heap)과 클러스터 형 인덱스 입니다.

 

 

 

 

힙(Heap) 이란?

  • 정렬의 기준이 없이 저장 된 테이블의 형태를 말합니다.

  • 데이터 페이지 내의 행들 간에 순서가 없고, 페이들 간에도 순서가 없습니다.

  • 클러스터 형 인덱스가 없는 테이블이라고 생각하시면 쉽습니다.

힙의 장점과 단점은 무엇인가?

  • 장점은 INSERT 문이 좋아하는 테이블 형태입니다. 새로운 행을 기존 페이지의 빈 곳에 추가하면 되고, 빈 공간이 없으면 새로운 페이지에 추가하면 되기 때문입니다.

  • 단점은 SELECT 문이 싫어하는 테이블 형태입니다. 데이터가 극히 적으면 상관없겠지만, 데이터가 많으면 전체적으로 모든 테이블을 스캔해야하기 때문에 데이터를 찾기가 어렵습니다.

위에서도 말했듯이, 원하는 데이터를 찾기 위해서는 Table Scan 을 해야 합니다. 전체적으로 페이지를 다 읽는겁니다. 전체적으로 모든 페이지를 다 읽는다고 하니, 당연히 좋지 않습니다.

 

클러스터 형 인덱스란?

  • 특정 열(또는 열들)을 기준으로 데이터가 정렬되어 있습니다. (물리적 정렬이 아닌, 논리적 정렬)

  • 테이블 당 하나의 클러스터 형 인덱스를 설정 가능합니다.

 

 

 

다시 한번 말하면, 테이블의 형태는 두가지로 나뉘게 됩니다.

정렬되어 있지 않은 힙과 특수한 Key 값으로 정렬되어진 클러스터 형 인덱스로 나누어집니다. 우리는 선택을 해야 합니다. 힙을 사용할지, 클러스터 형 인덱스를 선택할지. 힙이 무조건 좋지 않다라고 말할 수 없습니다. 상황마다 다르기 때문에 그 상황에 맞게 테이블을 설계하는 것이 개발자의 몫입니다.

 

 

클러스트 형의 데이터 찾기

이제부터 많은 용어가 나옵니다. 그러한 용어는 필수적으로 숙지해야합니다.

 

 

 

  • Clustered Index Seek : Root 페이지부터 찾아가서, 아주 빠른 성능을 보여줍니다.

  • Clustered Index Scan : 모든 데이터 페이지를 읽기 때문에 힙에서 사용했던 방식인 Table Scan과 다를바 없습니다.

힙 + 비 클러스터 형 인덱스

  • 비 클러스터 형 인덱스에는 인덱스 키 열의 모든 데이터와 RID(행의 주소)를 가지고 있습니다.

  • RID를 가지고 있는 이유는 RID는 거의 변하지 않고 크기가 크지 않기 때문에 한번에 찾아갈 수 있기 때문입니다.

  • 999개 까지 만들 수 있습니다.

  • Index Seek : NonClustered 가 생략되어졌고, Root 페이지를 이용하기 때문에 성능이 좋습니다.

  • RID Lookup :  비 클러스터형 인덱스에 RID 값이 저장되어 있기 때문에, 힙을 검색할때에는 RID값을 이용해서 검색하기 때문에 RID Lookup이 됩니다.

 

 

 

클러스터 형 인덱스 + 비 클러스터 형 인덱스

  • 비 클러스터 형 인덱스에는 클러스터 형 인덱스 키 열과 인덱스 키 열의 모든 데이터를 가지고 있습니다.

  • 클러스터 형 인덱스의 키 열은 거의 변하지 않습니다. (힙의 RID는 변할 가능성이 높습니다.)

  • 999개 까지 만들 수 있습니다.

  • Key Lookup : 비 클러스터 형 인덱스가 가지지 못한 데이터를 찾아가는 과정입니다.

 

 

 

결론

아래 그림으로 한방에 정리됩니다. 테이블은 클러스터 형 인덱스가 잡혀있는 테이블과 아닌 테이블로 구성되어 진다는 것을 알 수 있습니다.

그리고 비 클러스터 형 인덱스가 결합되어지면, 힙에는 RID 값을 가지고 있고 클러스터 형 인덱스는 키 값을 가지고 있습니다. 비 클러스터 형 인덱스에서 힙으로 검색될 때에는 RID Lookup, 클러스터 형 인덱스에 검색 되어 질 때에는 Key Loopup 이라는 용어가 사용되어 집니다.

현업에서 RID Lookup 으로 검색되어지는 테이블이 있다면, 그 테이블이 왜 힙을 사용하고 있는지 원인 분석이 필요한 것으로 보여집니다.

 

 

 

'Database > SQL Server' 카테고리의 다른 글

온라인 인덱스 구성(Online Index)  (0) 2020.08.29
TEMPDB DBFILE 삭제  (0) 2020.08.29
쿼리 저장소 (Query Store)  (0) 2020.08.29
동적쿼리  (0) 2020.08.29
VLF 개수에 따른 성능 차이  (0) 2020.08.29

-- <<관련 Command >>

-- Query Store 설정 확인

SELECT actual_state, actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb

FROM sys.database_query_store_options;

-- 실행 계획 확인

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*

FROM sys.query_store_plan AS Pl JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id

JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id ;

--  Query Store 데이터의 메모리 내 부분을 디스크로 플러시합니다.

EXEC sp_query_store_flush_db;

-- 쿼리를 삭제하고 쿼리 저장소에서 관련된 모든 계획과 런타임 통계를 제거합니다. ( Clears the runtime stats for a specific query plan from the query store )

EXEC sp_query_store_remove_query 3;

-- 쿼리 저장소에서 실행계획에 대한 실행 통계 삭제  ( Clears the runtime stats for a specific query plan from the query store )

EXEC sp_query_store_reset_exec_stats 3;

-- 쿼리 저장소에서 단일 계획을 제거합니다.

EXEC sp_query_store_remove_plan 3;

-- 특정 쿼리에 대한 특정 계획을 강제 실행합니다.

-- sp_query_store_force_plan 을 사용하면 쿼리 저장소에서 기록한 계획 만 해당 쿼리의 계획으로 강제 실행할 수 있습니다.

-- 즉, 쿼리에 사용할 수있는 유일한 계획은 쿼리 저장소가 활성화되어있는 동안 해당 쿼리를 실행하는 데 이미 사용 된 계획뿐입니다.

-- QUERY_ID , PLAN_ID 순서

EXEC sp_query_store_force_plan 3, 3;

-- 특정 쿼리에 대해 특정 PLAN 설정 해제 하기  ( Enables unforcing a particular plan for a particular query )

-- QUERY_ID , PLAN_ID 순서

EXEC sp_query_store_unforce_plan 3, 3;

-- 실행 계획 확인

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*

FROM sys.query_store_plan AS Pl JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id

JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id ;

ALTER DATABASE TGTEST SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);

ALTER DATABASE TGTEST SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 200);

ALTER DATABASE TGTEST SET QUERY_STORE

( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)

, DATA_FLUSH_INTERVAL_SECONDS = 300

, MAX_STORAGE_SIZE_MB = 500

, INTERVAL_LENGTH_MINUTES = 15

, SIZE_BASED_CLEANUP_MODE = AUTO

, QUERY_CAPTURE_MODE = AUTO

, MAX_PLANS_PER_QUERY = 1000

, WAIT_STATS_CAPTURE_MODE = ON );

SELECT * FROM sys.database_query_store_options;

-- 수행 횟수가 2보다 작고, 마지막 수행이 24전 보다 오랜 쿼리를 QUERY STORE 에서 삭제하기

DECLARE @id int

DECLARE adhoc_queries_cursor

  CURSOR FOR SELECT q.query_id

  FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

  ON q.query_text_id = qt.query_text_id JOIN sys.query_store_plan AS p

  ON p.query_id = q.query_id JOIN sys.query_store_runtime_stats AS rs

  ON rs.plan_id = p.plan_id

  GROUP BY q.query_id

  HAVING SUM(rs.count_executions) < 2  -- 수행 횟수가 2보다 작고

  AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE())  -- 마지막 수행 시간이 24간 이전

  ORDER BY q.query_id ;

OPEN adhoc_queries_cursor ;

FETCH NEXT FROM adhoc_queries_cursor INTO @id;

WHILE @@fetch_status = 0

BEGIN PRINT @id EXEC sp_query_store_remove_query @id

FETCH NEXT FROM adhoc_queries_cursor INTO @id

END

CLOSE adhoc_queries_cursor ;

DEALLOCATE adhoc_queries_cursor;

-- 가장 최근까지 수행 쿼리

SELECT TOP 10 qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, rs.last_execution_time

FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

ORDER BY rs.last_execution_time DESC;

-- 자주 수행되는 쿼리

SELECT q.query_id, qt.query_text_id, qt.query_sql_text, SUM(rs.count_executions) AS total_execution_count

FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text

ORDER BY total_execution_count DESC;

-- 최근 1시간 동안 평균 수행 시간이 가장 오래 걸린 쿼리

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id

      , GETUTCDATE() AS CurrentUTCTime, rs.last_execution_time

FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id

WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())

ORDER BY rs.avg_duration DESC;

-- 최근 24시간 내에 IO가 가장 높은 쿼리 10

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id

  , rs.runtime_stats_id, rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions

FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs

ON p.plan_id = rs.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi

ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id

WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())

ORDER BY rs.avg_physical_io_reads DESC;

-- 실행계획이 여러개인 쿼리

WITH Query_MultPlans AS

( SELECT COUNT(*) AS cnt, q.query_id

  FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

  ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p

  ON p.query_id = q.query_id

  GROUP BY q.query_id

  HAVING COUNT(distinct plan_id) > 1 )

SELECT q.query_id, object_name(object_id) AS ContainingObject, query_sql_text, plan_id

  , p.query_plan AS plan_xml, p.last_compile_start_time, p.last_execution_time

FROM Query_MultPlans AS qm JOIN sys.query_store_query AS q

ON qm.query_id = q.query_id JOIN sys.query_store_plan AS p

ON q.query_id = p.query_id JOIN sys.query_store_query_text qt

ON qt.query_text_id = q.query_text_id

ORDER BY query_id, plan_id;

-- 실행계획 변경으로 성능 저하 쿼리 (  수행이 48 이내,  실행계획이 변경되며, 평균 수행 시간이 더 오래 걸린 쿼리 )

SELECT qt.query_sql_text, q.query_id, qt.query_text_id, rs1.runtime_stats_id AS runtime_stats_id_1

    ,rsi1.start_time AS interval_1, p1.plan_id AS plan_1, rs1.avg_duration AS avg_duration_1

    ,rs2.avg_duration AS avg_duration_2, p2.plan_id AS plan_2, rsi2.start_time AS interval_2

    , rs2.runtime_stats_id AS runtime_stats_id_2

FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q

ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p1

ON q.query_id = p1.query_id JOIN sys.query_store_runtime_stats AS rs1

ON p1.plan_id = rs1.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi1

ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id JOIN sys.query_store_plan AS p2

ON q.query_id = p2.query_id JOIN sys.query_store_runtime_stats AS rs2

ON p2.plan_id = rs2.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi2

ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id

WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())

AND rsi2.start_time > rsi1.start_time

AND p1.plan_id      <> p2.plan_id

AND rs2.avg_duration > 2*rs1.avg_duration

ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;

-- 가장 오래 대기한 쿼리

SELECT TOP 10 qt.query_text_id, q.query_id, p.plan_id, sum(total_query_wait_time_ms) AS sum_total_wait_ms

FROM sys.query_store_wait_stats ws JOIN sys.query_store_plan p

ON ws.plan_id = p.plan_id JOIN sys.query_store_query q

ON p.query_id = q.query_id JOIN sys.query_store_query_text qt

ON q.query_text_id = qt.query_text_id

GROUP BY qt.query_text_id, q.query_id, p.plan_id

ORDER BY sum_total_wait_ms DESC

--- 최근 1시간, 24시간 쿼리 성능 비교

--- "Recent" workload - last 1 hour

DECLARE @recent_start_time datetimeoffset;

DECLARE @recent_end_time datetimeoffset;

SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());  -- 1시간전

SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload

DECLARE @history_start_time datetimeoffset;

DECLARE @history_end_time datetimeoffset;

SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME()); -- 24시간전

SET @history_end_time = SYSUTCDATETIME();

WITH hist AS

( SELECT p.query_id query_id, CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration

    , SUM(rs.count_executions) count_executions

    , COUNT(distinct p.plan_id) num_plans

    FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_plan p

    ON p.plan_id = rs.plan_id

    WHERE (rs.first_execution_time >= @history_start_time AND rs.last_execution_time < @history_end_time)

      OR (rs.first_execution_time <= @history_start_time AND rs.last_execution_time > @history_start_time)

      OR (rs.first_execution_time <= @history_end_time AND rs.last_execution_time > @history_end_time)

    GROUP BY p.query_id )

, recent AS

  ( SELECT p.query_id query_id, CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration

  , SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans

  FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_plan p

  ON p.plan_id = rs.plan_id

  WHERE (rs.first_execution_time >= @recent_start_time AND rs.last_execution_time < @recent_end_time)

  OR (rs.first_execution_time <= @recent_start_time AND rs.last_execution_time > @recent_start_time)

  OR (rs.first_execution_time <= @recent_end_time AND rs.last_execution_time > @recent_end_time)

  GROUP BY p.query_id )

SELECT results.query_id query_id, results.query_text query_text

, results.additional_duration_workload additional_duration_workload

, results.total_duration_recent total_duration_recent

, results.total_duration_hist total_duration_hist

, ISNULL(results.count_executions_recent, 0) count_executions_recent

, ISNULL(results.count_executions_hist, 0) count_executions_hist

FROM ( SELECT hist.query_id query_id, qt.query_sql_text query_text

    , ROUND(CONVERT(float, recent.total_duration/ recent.count_executions-hist.total_duration/hist.count_executions) *(recent.count_executions), 2) AS additional_duration_workload, ROUND(recent.total_duration, 2) total_duration_recent

    , ROUND(hist.total_duration, 2) total_duration_hist, recent.count_executions count_executions_recent, hist.count_executions count_executions_hist

    FROM hist JOIN recent ON hist.query_id = recent.query_id JOIN sys.query_store_query AS q

    ON q.query_id = hist.query_id JOIN sys.query_store_query_text AS qt

    ON q.query_text_id = qt.query_text_id ) AS results

    WHERE additional_duration_workload > 0

    ORDER BY additional_duration_workload DESC OPTION (MERGE JOIN);

 

'Database > SQL Server' 카테고리의 다른 글

TEMPDB DBFILE 삭제  (0) 2020.08.29
index  (0) 2020.08.29
동적쿼리  (0) 2020.08.29
VLF 개수에 따른 성능 차이  (0) 2020.08.29
In-Memory Demo  (0) 2020.08.28

정적쿼리만 하다가 동적쿼리를 하면 조금 복잡하고 어려울 수 있다.

그러면 왜 동적쿼리를 사용해야 되냐?

그 이유는 예외처리를 위해 WHERE 절에 들어가야는 조건절을 손쉽게 제어하기 위함이다.

천천히 따라해보자.

 

예제

테이블 생성

CREATE TABLE #MEMBER(

  num      INT,

  name    NVARCHAR(20),

    score    INT

)

INSERT INTO #MEMBER (num, name, score) VALUES(1, '홍길동', 90);

INSERT INTO #MEMBER (num, name, score) VALUES(2, '고길동', 85);

INSERT INTO #MEMBER (num, name, score) VALUES(3, '피카츄', 83);

INSERT INTO #MEMBER (num, name, score) VALUES(4, '파이리', 85);

INSERT INTO #MEMBER (num, name, score) VALUES(5, '꼬북이', 80);

cs

 

테이블 예제

 

동적쿼리 생성 과정

-- 매개 변수들을 선언해준다.

DECLARE @sql    AS NVARCHAR(MAX),  -- SQL 구문

        @sqlp    AS NVARCHAR(MAX),  -- SQL 매개 변수

        @num    AS INT,            -- num 매개 변수

        @name    AS NVARCHAR(20),    -- name 매개 변수

        @score  AS INT              -- score 매개 변수

 

-- WHERE 절에 들어갈 변수

SET @score = 85;

 

-- 기본적인 SELECT 구문

SET @sql = N'SELECT * FROM #MEMBER WHERE 1=1 ';

 

-- IF 절로 예외처리

IF ISNULL(@num, 0) > 0 BEGIN

    SET @sql = @sql + ' AND num = @num ';

END

IF ISNULL(@name, '') != '' BEGIN

    SET @sql = @sql + N' AND name = ''' + @name + N'''';

END

IF ISNULL(@score, 0) > 0 BEGIN

    SET @sql = @sql + ' AND score = @score';

END

 

-- EXECUTESQL에 들어갈 매개변수 정의

SET @sqlp = N'

    @num    AS INT,

    @name    AS NVARCHAR(20),

    @score  AS INT';

 

-- 실행

EXEC SP_EXECUTESQL @sql, @sqlp, @num=@num, @name=@name, @score=@score;

cs

 

 

 

 

 

Input Parameter 값의 유무에 따라서 WHERE 조건절에 들어갈지 말아야할지 정할때 동적쿼리를 가장 많이 사용한다.

@num/@name/@score 값의 유무를 @sql에 넣어준다.

 

 

@sql 변수에 들어간 동적쿼리

 

@sql 변수의 값이 자유자재로 변하게 된다.

 

원하는 결과값이 출력

 

정리

이전 회사에서 동적쿼리가 좋은지?

정적쿼리가 좋은지? 에 대한 갑론을박이 있었다.

사실 동적쿼리의 장점은 속도라고 말할 수 있었는데, 진행하고 있던 프로젝트의 전체적인 프로시저를 동적쿼리에서 정적쿼리로 바꾼적이 있다.

그 이유는 실행계획에 이유가 있었는데...정확한 이유는 기억 못하겠다.

여튼, 동적쿼리든 정적쿼리든 상황에 맞게 잘 사용해야겠다. 개발에는 100%라는 것이 없기에...

 

'Database > SQL Server' 카테고리의 다른 글

index  (0) 2020.08.29
쿼리 저장소 (Query Store)  (0) 2020.08.29
VLF 개수에 따른 성능 차이  (0) 2020.08.29
In-Memory Demo  (0) 2020.08.28
포트 에 대한 TCP/IP 연결에 실패했습니다  (0) 2020.08.28

트랜잭션 로그와 VLF


SQL Server 는 각 트랜잭션 및 데이터베이스 수정 내용을 기록하는 트랜잭션 로그가 있습니다.

트랜잭션 로그 파일은 VLF (Virtual Log File) 로 구성됩니다.

 

지금은 VLF 개수에 따른 성능 차이를 포스팅하는게 목적이기 때문에 개념에 대해서는 설명하지 않습니다.

아래 URL 에서 개념에 대해 파악할 수 있습니다.

https://docs.microsoft.com/ko-kr/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-2017

https://docs.microsoft.com/ko-kr/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-2017#physical_arch

 

 

VLF 에 따른 성능 테스트


두 개의 같은 로그 파일 크기에 VLF 수만 다르게 해서 테스트를 진행해 보겠습니다.

 

DATABASE 생성

LOG1M_AUTO1M 데이터베이스를 생성한 후, 트랜잭션 로그 파일을 1M 씩 증가시켜 300MB 까지 만듭니다.

CREATE DATABASE LOG1M_AUTO1M

 

ON PRIMARY ( NAME = 'LOG1M_AUTO1M', FILENAME = 'E:\SQL Server\LOG1M_AUTO1M.mdf', SIZE = 50MB, FILEGROWTH = 10MB )

 

LOG ON ( NAME = 'LOG1M_AUTO1M_Log', FILENAME = 'E:\SQL Server\LOG1M_AUTO1M_Log.ldf', SIZE = 1MB, FILEGROWTH = 1MB )

 

GO

 

 

 

 

BACKUP DATABASE LOG1M_AUTO1M TO DISK = 'E:\SQL Server\LOG1M_AUTO1M.bak'

 

GO

 

 

 

 

DECLARE @i INT

 

DECLARE @query VARCHAR(8000)

 

SET @i = 2

 

WHILE @i < 301 BEGIN

 

  SET @query = 'ALTER DATABASE LOG1M_AUTO1M MODIFY FILE

 

                  ( NAME = LOG1M_AUTO1M_Log, SIZE = ' + CONVERT(VARCHAR(10), @i) + ')'

 

  EXEC (@query)

 

  SET @i = @i + 1

 

END

 

GO

 

 

LOG300M_AUTO1M 데이터베이스를 생성합니다. 최초 생성시 트랜잭션 로그 파일은 300MB 크기입니다.

CREATE DATABASE LOG300M_AUTO1M

 

ON PRIMARY ( NAME = 'LOG300M_AUTO1M', FILENAME = 'E:\SQL Server\LOG300M_AUTO1M.mdf', SIZE = 50MB, FILEGROWTH = 10MB )

 

LOG ON ( NAME = 'LOG300M_AUTO1M_Log', FILENAME = 'E:\SQL Server\LOG300M_AUTO1M_Log.ldf', SIZE = 100MB, FILEGROWTH = 1MB )

 

GO

 

 

 

 

BACKUP DATABASE LOG300M_AUTO1M TO DISK = 'E:\SQL Server\LOG300M_AUTO1M.bak'

 

GO

LOG1M_AUTO1M 데이터베이스는 327 개의 VLF 를

LOG300M_AUTO1M 데이터베이스는 8 개의 VLF 를 가지게 됩니다.

 

 

테이블/인덱스 생성

생성한 데이터베이스에 테이블/인덱스를 생성한 후 DML 테스트를 진행합니다.

CREATE TABLE T1 (C1 INT)

 

CREATE INDEX IDX_T1_C1 ON T1 (C1)

 

INSERT

50,000 건을 INSERT 후 걸리는 ms 를 측정한 결과입니다.

DECLARE @I INT

 

DECLARE @ST DATETIME

 

DECLARE @ET DATETIME

 

SET @I = 1

 

SET @ST = GETDATE()

 

WHILE @I < 50001

 

BEGIN

 

  INSERT INTO T1 SELECT @I

 

  SET @I = @I + 1

 

END

 

SET @ET = GETDATE()

 

SELECT DATEDIFF(ms, @ST, @ET) AS INSERT_TEST

 

GO

 

 INSERT 테스트

 LOG1M_AUTO1M

 LOG300M_AUTO1M

 1차(ms)

 14410

 13850

 2차(ms)

 14454

 14140

 3차(ms)

 14650

 14193

 

UPDATE

50,000 건을 UPDATE 후 걸리는 ms 를 측정한 결과입니다.

DECLARE @I INT

 

DECLARE @ST DATETIME

 

DECLARE @ET DATETIME

 

SET @I = 1

 

SET @ST = GETDATE()

 

WHILE @I < 50001

 

BEGIN

 

  UPDATE T1 SET C1 = C1 - 1 WHERE C1 = @I

 

  SET @I = @I + 1

 

END

 

SET @ET = GETDATE()

 

SELECT DATEDIFF(ms, @ST, @ET) AS UPDATE_TEST

 

GO

 

 UPDATE 테스트

 LOG1M_AUTO1M

 LOG300M_AUTO1M

 1차(ms)

 19327

 18867

 2차(ms)

 19267

 18740

 3차(ms)

 19763

 19173

 

 

DELETE

50,000 건을 DELETE 후 걸리는 ms 를 측정한 결과입니다.

DECLARE @I INT

 

DECLARE @ST DATETIME

 

DECLARE @ET DATETIME

 

SET @I = 1

 

SET @ST = GETDATE()

 

WHILE @I < 50001

 

BEGIN

 

  DELETE T1 WHERE C1 = @I - 1

 

  SET @I = @I + 1

 

END

 

SET @ET = GETDATE()

 

SELECT DATEDIFF(ms, @ST, @ET) AS DELETE_TEST

 

GO

 

 DELETE 테스트

 LOG1M_AUTO1M

 LOG300M_AUTO1M

 1차(ms)

 16840

 15993

 2차(ms)

 16643

 16333

 3차(ms)

 16934

 16607

 

BACKUP LOG

위의 DML 작업을 한 후 BACKUP LOG 에 걸리는 시간을 측정한 결과입니다.

BACKUP LOG LOG1M_AUTO1M TO DISK = 'E:\SQL Server\LOG1M_AUTO1M_Log.bak'

 

BACKUP LOG LOG300M_AUTO1M TO DISK = 'E:\SQL Server\LOG300M_AUTO1M_Log.bak'

 

 Log Backup

 LOG1M_AUTO1M

 LOG300M_AUTO1M

 1차

 13038개의 페이지를 3.282초 동안 처리했습니다(31.034MB/초)

 13020개의 페이지를 2.698초 동안 처리했습니다(37.701MB/초)

 2차

 13027개의 페이지를 3.181초 동안 처리했습니다(31.994MB/초)

 13021개의 페이지를 2.790초 동안 처리했습니다(36.458MB/초)

 3차

 13026개의 페이지를 3.162초 동안 처리했습니다(32.138MB/초)

 13188개의 페이지를 2.669초 동안 처리했습니다(38.600MB/초)

 

 

 

VLF 에 따른 성능 테스트 결과


VLF 가 많을 수록 DML 및 로그파일의 백업에 걸리는 소요 시간이 소폭이지만 증가하는 것을 알 수 있습니다.

 

왜그럴까요?

데이터베이스 엔진에서 각각의 VLF 를 관리해야 하기 때문입니다.

관리해야 하는 양이 많아질수록, 비용이 증가하는건 당연합니다.

 

 

트랜잭션 로그파일 크기를 너무 작게 줄이면 로그파일이 가득 차 FILEGROWTH 에 의해 크기가 증가하며 동시에 VLF 가 증가하게 되는 문제를 낳을 수 있습니다.

따라서, 트랜잭션 로그 파일 크기를 줄이더라도 크기를 산정하여 진행해야 합니다.

DECLARE @SEARCH_TEXT NVARCHAR(MAX) = 'WEB_ORDER_REGISTER_ADDRESS';
 
SELECT B.NAME       AS NAME     -- 이름
     , B.TYPE_DESC  AS TYPE     -- 타입 구분
     , A.DEFINITION AS CONTENTS -- 내용
  FROM SYS.SQL_MODULES A WITH (NOLOCK)
  LEFT JOIN SYS.OBJECTS B WITH (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID
 WHERE DEFINITION LIKE '%' + @SEARCH_TEXT + '%'      
 ORDER BY TYPE, NAME  

'Database > Query' 카테고리의 다른 글

테이블 명세서  (0) 2020.08.29
sp_who3  (0) 2020.08.29
Full Scan, Index Scan Query  (0) 2020.08.28
모든 인덱스 다시 리빌드, 통계 업데이트  (0) 2020.08.28
INDEX 사용량  (0) 2020.08.28

-- DB생성
CREATE DATABASE DBNull 
ON PRIMARY (NAME = N'DBNull' ,FILENAME = 'C:\MSSQL_DATA\DBNull.MDF',SIZE = 500MB ,MAXSIZE = UNLIMITED ,FILEGROWTH = 100MB) 
LOG ON (NAME = N'DBNull_Log' ,FILENAME = 'C:\MSSQL_LOG\DBNull.LDF' ,SIZE = 100MB ,MAXSIZE = 1024GB ,FILEGROWTH = 100MB) 




-- 언어확인
SELECT DATABASEPROPERTYEX('DBNull','Collation')



/* IN-MEMORY DB설정
이 명령어를 실행하고, DBNull의 데이터베이스 파일그룹 정보를 확인하면, 메모리 엑세스에
최적화된 데이터라고 나오는 것을 확인 할 수 있습니다.
*/
ALTER DATABASE DBNull ADD FILEGROUP [IN_MemoryGroup] CONTAINS MEMORY_OPTIMIZED_DATA


/* IN-MEMORY 폴더 추가
해당 구문을 실행하면, 폴더와 함께 파일이 생깁니다. 
* SQL SERVER가 재시작하여도 메모리 테이블의 데이터를 계속 파일에 저장합니다. 
FILESTREAM에 저장된 데이터와 트랜잭션 로그를 사용해서 재부팅 시 RECOVERY 과정을 진행합니다.
이 과정은 메모리 테이블에 대한 RECOVERY 과정이며, 저장된 데이터를 메모리에 다시 로드하는 과정입니다.
*/
ALTER DATABASE DBNull 
ADD FILE(NAME = IN_MEMORY,FILENAME='C:\IN_MEMORY') TO FILEGROUP [IN_MemoryGroup]


/*=====================================================================================================================================*/


USE DBNull
GO
-- [SCHEMA_ONLY] 테이블 테스트
CREATE TABLE [SCHEMA_ONLY](
 col1 INT			NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000000)
,col2 NVARCHAR(100)	COLLATE Korean_Wansung_BIN2 NOT NULL INDEX[HX_TBL_1_1]
,col3 NVARCHAR(50)	NULL
,col4 INT			NULL)
WITH (MEMORY_OPTIMIZED = ON			-- 메모리 테이블
	 ,DURABILITY = SCHEMA_ONLY) -- 테이블의 유형 (SCHEMA_AND_DATA: 비휘발 / SCHEMA_ONLY:휘발)
GO

INSERT INTO [SCHEMA_ONLY] VALUES (1,N'TEST','C개발자',100)
SELECT * FROM [SCHEMA_ONLY]




-- 테이블 생성
-- 테이블 속성을 열면 정보를 확인 할 수 있습니다.
-- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\xtp
CREATE TABLE [SCHEMA_AND_DATA](
 col1 INT			NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000000)
,col2 NVARCHAR(100)	COLLATE Korean_Wansung_BIN2 NOT NULL INDEX[HX_TBL_1_1]
,col3 NVARCHAR(50)	NULL
,col4 INT			NULL)
WITH (MEMORY_OPTIMIZED = ON			-- 메모리 테이블
	 ,DURABILITY = SCHEMA_AND_DATA) -- 테이블의 유형 (SCHEMA_AND_DATA: 비휘발 / SCHEMA_ONLY:휘발)
GO
/*
메시지 12329, 수준 16, 상태 103, 줄 51
1252 이외의 코드 페이지를 가진 데이터 정렬을 사용하는 데이터 형식 char(n) 및 varchar(n)는 메모리 액세스에 최적화된 테이블에서 지원되지 않습니다.
*/
INSERT INTO [SCHEMA_AND_DATA] VALUES (1,N'TEST1','DBA',100)
SELECT * FROM [SCHEMA_AND_DATA]

/*
Foreign Key, Check 제약조건
LOB, CLR, XML 데이터 형식
파티션, 압축, TDE
복제, 미러링
DML 트리거
인덱스 컬럼에는 null 허용(null 허용 안함)
* ALTER TABLE: 테이블 삭제 후 다시 생성
* 인덱스 추가 삭제: 테이블 삭제 후 다시 생성
*/



/*=====================================================================================================================================*/



-- Collation 관련
-- https://technet.microsoft.com/ko-kr/library/ms186356(v=sql.105).aspx
SELECT *
--COLLATIONPROPERTY('name','Codepage') AS CODEPAGE,NAME
FROM SYS.fn_helpcollations ()
WHERE COLLATIONPROPERTY ('name','Codepage') = 1252
GO

-- 문자열 컬럼에 인덱스를 지정할 경우 언어 선정
SELECT COLLATIONPROPERTY(name, 'codepage') AS CODEPAGE, NAME
from sys.fn_helpcollations()
where name like '%bin2%'
    and name like '%korean%'


-- Collation에 관련된 알아야 할 점 ORDER BY
CREATE TABLE dbo.ORDER_CI_AS(
    ID int primary key nonclustered identity
    , CI_AS nvarchar(100) collate korean_wansung_ci_as
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

-- Collation에 관련된 알아야 할 점 ORDER BY
CREATE TABLE dbo.ORDER_BIN2(
    ID int primary key nonclustered identity
    , BIN2 nvarchar(100) collate korean_wansung_bin2
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

INSERT INTO ORDER_CI_AS VALUES( N'가')
INSERT INTO ORDER_BIN2 VALUES( N'가')
INSERT INTO ORDER_CI_AS VALUES(N'1')
INSERT INTO ORDER_BIN2 VALUES(N'1')
INSERT INTO ORDER_CI_AS VALUES(NULL)
INSERT INTO ORDER_BIN2 VALUES(NULL)
INSERT INTO ORDER_CI_AS VALUES(N'A')
INSERT INTO ORDER_BIN2 VALUES(N'A')
GO

SELECT 'CI_AS' as 'collate',* FROM ORDER_CI_AS ORDER BY CI_AS
SELECT 'BIN2' as 'collate',* FROM ORDER_BIN2 ORDER BY BIN2
GO
 
-- 조인 
SELECT * FROM ORDER_CI_AS AS A
INNER JOIN ORDER_BIN2 AS B
ON A.CI_AS = B.BIN2




/*=====================================================================================================================================*/




-- 네이티브 프로시저
-- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\xtp\5
-- EXEC INSERT_PROC_1 1,'형권','DBA',100
-- SELECT * FROM [TBL_1]

ALTER PROC DBO.INSERT_PROC_1
@col1 INT
,@col2 NVARCHAR(200)
,@col3 NCHAR(100)
,@col4 INT
	WITH 
		NATIVE_COMPILATION,
		SCHEMABINDING,
		EXECUTE AS OWNER
	AS
	BEGIN ATOMIC
		WITH 
		(TRANSACTION	ISOLATION LEVEL = SNAPSHOT,
		 LANGUAGE = N'KOREAN')

--SELECT * FROM [TBL_1]
--SELECT col1, col2, col3, col4 FROM TBL_1 WHERE col1 = col1 OR col2 = @col2
--INSERT INTO [dbo].[TBL_1] VALUES (@col1,@col2,@col3,@col4)

	END
GO
/* 네이티브 프로시저 제약사항
서브쿼리 불가능
case when문 불가능
outer join 불가능 (left, full...)
apply 불가능
where에 in,or,like 조건 불가능
select * 불가능
union 불가능
temp table , @table 불가능
CTE 불가능
Cursors 불가능
select into 불가능
*/


/*=====================================================================================================================================*/


USE AdventureWorks2014
GO
-- IN-MEMORY 테이블의 올바른 버킷 수 결정 
-- 전체 행수
SELECT COUNT(*) AS [전체 행 수] 
FROM Sales.SalesOrderDetail

-- 복합인덱스 일 경우 행사
SELECT COUNT(*) AS [복합 인덱스로 걸 경우 고유값]
FROM 
   (SELECT DISTINCT SpecialOfferID, ProductID 
    FROM Sales.SalesOrderDetail) t




-- ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP [IN_MemoryGroup_1] CONTAINS MEMORY_OPTIMIZED_DATA
--SP_DETACH_DB [AdventureWorks2014]
--SP_ATTACH_DB [AdventureWorks2014],'C:\MSSQL_DATA\AdventureWorks2014_Data.mdf','C:\MSSQL_LOG\AdventureWorks2014_Log.ldf'
-- https://msdn.microsoft.com/ko-kr/library/dn511655(v=sql.120).aspx
-- 테이블 생성
CREATE TABLE [Sales].[SalesOrderHeader_test]
(
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),
   [OrderSequence] int NOT NULL,
   [OrderDate] [datetime2](7) NOT NULL,
   [Status] [tinyint] NOT NULL,

PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

-- 값 삽입
SET NOCOUNT ON
DECLARE @i int = 0
BEGIN TRAN
WHILE @i < 262144
BEGIN
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)
   SET @i += 1
END
COMMIT
SET NOCOUNT OFF
GO



-- 올바른 버킷수를 확인하기 위한 스크립트
SELECT 
   object_name(hs.object_id) AS 'object name', 
   i.name as 'index name', 
   hs.total_bucket_count,
   hs.empty_bucket_count,
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',
   hs.avg_chain_length, 
   hs.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats AS hs -- 버킷 수를 볼수 있음
   JOIN sys.indexes AS i 
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id
WHERE I.name IN('IX_Status','IX_OrderSequence','PK__SalesOrd__B14003C33D8879E4')



/*=====================================================================================================================================*/


-- 백업 테스트
BACKUP DATABASE [DBNull] TO DISK = 'C:\MSSQL_BACKUP\DBNull.BAK'
-- 복원 테스트
RESTORE FILELISTONLY FROM DISK  = 'C:\MSSQL_BACKUP\DBNull.BAK'

-- 복원
RESTORE DATABASE [DBNull_2] from DISK ='C:\MSSQL_BACKUP\DBNull.BAK'
WITH MOVE 'DBNull' TO 'C:\MSSQL_DATA\DBNull_1.MDF',
MOVE 'DBNull_Log' TO 'C:\MSSQL_LOG\DBNull_1.LDF',
MOVE 'IN_MEMORY' TO 'C:\IN_MEMORY_2'

-- 데이터 확인 및 프로시저 확인
-- C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014\MSSQL\DATA\xtp
USE DBNull_2 
GO



SELECT * FROM [dbo].[TBL_2]
SELECT * FROM [dbo].[TBL_1]

EXEC [dbo].[INSERT_PROC_1] 2,'주형권','개발자',200



/*=====================================================================================================================================*/
-- 메모리 관리
CREATE RESOURCE POOL [DBNull_MEMORY] WITH (MAX_MEMORY_PERCENT = 70);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

EXEC sp_xtp_bind_db_resource_pool 'DBNull', 'DBNull_MEMORY'
GO

ALTER DATABASE DBNull SET OFFLINE
GO
ALTER DATABASE DBNull SET ONLINE
GO

-- 메모리 할당량 확인
USE [DBNull]
go
SELECT OBJECT_NAME(object_id), *
FROM sys.dm_db_xtp_table_memory_stats;

-- SQL SERVER 2014 IN-MEMORY 성능수집 관련 
-- https://msdn.microsoft.com/ko-kr/KO/library/dn511015.aspx
with XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)

SELECT * FROM

 (

 SELECT

  db_name(qt.dbid) AS 'DB Name'

  ,qt.dbid

  ,OBJECT_NAME(qp.objectid,qp.dbid) as sp_name

  ,qt.text AS 'sp_text'

  , substring(qt.text, (qs.statement_start_offset/2)+1

   , ((case qs.statement_end_offset

      when -1 then datalength(qt.text)

      else qs.statement_end_offset

      end - qs.statement_start_offset)/2) + 1) as statement_text

  , qs.creation_time

  , qs.execution_count AS 'Execution Count'

  , ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'

  , DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'

  , ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime(㎲)'

  , qs.total_elapsed_time/1000.0/1000.0 AS 'TotalElapsedTime(sec)'

  , max_elapsed_time /1000.0 AS 'maxelapsedTime(ms)'

  , qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime(㎲)'

  , qs.total_worker_time AS 'TotalWorkerTime(㎲)'

  , max_worker_time as 'max_worker_time(㎲)'

  , ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'AvgLogicalreads'

  , total_logical_reads

  , qs.max_logical_reads

  , ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'AvgphysicalReads'

  , total_physical_reads

  , qs.max_physical_reads

  , ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'AvglogicalWrites'

  , qs.total_logical_writes

  , qs.max_logical_writes

  ,text

  ,cast(query_plan as xml) as query_plan

 FROM sys.dm_exec_query_stats as qs

 CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt

 CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp

 WHERE  db_name(qt.dbid) <> 'nxdba'

)Y

CROSS APPLY

(

      SELECT

       c.value('(./@PhysicalOp)[1]','varchar(100)') as PhysicalOp

       FROM Y.query_plan.nodes('//sql:RelOp')B(C)

)X

where PhysicalOp  IN ('Table Scan','Index Scan')and

Y.dbid not in (1,2,3,4,32767)

'Database > Query' 카테고리의 다른 글

sp_who3  (0) 2020.08.29
특정 문자열 포함하는 프로시저  (0) 2020.08.28
모든 인덱스 다시 리빌드, 통계 업데이트  (0) 2020.08.28
INDEX 사용량  (0) 2020.08.28
INDEX 정보  (0) 2020.08.28

영문 windows2008r2 - mssql 2008 에서 다음과 같은 에러가 발생

 

[오류]

 

com.microsoft.sqlserver.jdbc.SQLServerException: 호스트 10.250.34.163, 포트 1433에 대한 TCP/IP 연결에 실패했습니다. 오류: "No buffer space available (maximum connections reached?): connect. 연결 속성을 확인하고 SQL Server의 인스턴스가 호스트에서 실행되고 있고 포트에서 TCP/IP 연결을 허용하고 있는지 확인하십시오. 또한 포트에서 TCP 연결을 차단하고 있는 방화벽이 없는지 확인하십시오.".

 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:171)

 at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1033)

 at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)

 at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)

 at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)

 at java.sql.DriverManager.getConnection(Unknown Source)

 at java.sql.DriverManager.getConnection(Unknown Source)

 at com.cci.gss.server.Context$Root.<init>(Context.java:202)

 at com.cci.gss.server.request.ConnectGSS.run(ConnectGSS.java:76)

 at com.cci.gss.server.GSSRequest.execute(GSSRequest.java:29)

 at com.cci.gss.server.ThreadPool$GSSThread.run(ThreadPool.java:33)

 

 

 

 

 

[해결]

 

Resolution for “No buffer space available (maximum connections reached?): JVM_Bind” issue

 

I hit this issue recently which occurred on only one windows 7 host. The error was caused by this hard to guess reason (http://support.microsoft.com/kb/196271). The default number of ephemeral TCP ports is 5000. Sometimes this number may become less if the server has too many active client connections due to which the ephemeral TCP ports are all used up and in this case no more can be allocated to a new client connection request resulting in the below issue (for a Java application):

Caused by: java.net.SocketException: No buffer space available (maximum connections reached?): JVM_Bind

    at java.net.PlainSocketImpl.socketBind(Native Method)

    at java.net.PlainSocketImpl.bind(PlainSocketImpl.java:365)

    at java.net.Socket.bind(Socket.java:577)

    at com.sun.net.ssl.internal.ssl.BaseSSLSocketImpl.bind(BaseSSLSocketImpl.java:95)

    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.bind(SSLSocketImpl.java:45)

    at com.sun.net.ssl.internal.ssl.SSLSocketImpl.<init>(SSLSocketImpl.java:399)

    at com.sun.net.ssl.internal.ssl.SSLSocketFactoryImpl.createSocket(SSLSocketFactoryImpl.java:123)

    at org.apache.commons.httpclient.contrib.ssl.EasySSLProtocolSocketFactory.createSocket(EasySSLProtocolSocketFactory.java:183)

    at org.apache.commons.httpclient.HttpConnection.open(HttpConnection.java:707)

    at org.apache.commons.httpclient.MultiThreadedHttpConnectionManager$HttpConnectionAdapter.open(MultiThreadedHttpConnectionManager.java:1361)

    at org.apache.commons.httpclient.HttpMethodDirector.executeWithRetry(HttpMethodDirector.java:387)

    at org.apache.commons.httpclient.HttpMethodDirector.executeMethod(HttpMethodDirector.java:171)

    at org.apache.commons.httpclient.HttpClient.executeMethod(HttpClient.java:397)

 




The resolution is to open the registry editor and locate the registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters and add a new entry as shown below:

 

Value Name: MaxUserPort

Value Type: DWORD

Value data65534

That’s it! Thanks to Daniel Baktiar for his post.

+ Recent posts