SQL

-- <<관련 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);

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

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

그 이유는 예외처리를 위해 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%라는 것이 없기에...

트랜잭션 로그와 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

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

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

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'

VLF 에 따른 성능 테스트 결과

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

왜그럴까요?

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

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

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

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

-- 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

영문 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 data: 65534

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

use [데이터베이스명]

select name, lastdate
from (
    SELECT b.name,
      max(a.last_execution_time) as lastdate
   FROM sys.dm_exec_procedure_stats a
      INNER JOIN sys.objects b
      ON a.object_id = b.object_id
   WHERE  Db_name(a.database_id) = '데이터베이스명'
   group by b.name
)z
order by lastdate desc

-- DB 주의대상
EXEC sp_resetstatus ‘DB이름’;
ALTER DATABASE DB이름 SET EMERGENCY
DBCC CHECKDB (‘DB이름’)
ALTER DATABASE DB이름 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (‘DB이름’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE DB이름 SET MULTI_USER

ALTER DATABASE testdb ADD FILE (name='InMemory_Test', filename='E:\DATA\InMemory_Test') TO FILEGROUP InMemoryFileGroup



명시 적-Transact-SQL에는 최종 COMMIT TRANSACTION과 함께 BEGIN TRANSACTION 코드가 포함되어 있습니다. 

두 개 이상의 명령문을 동일한 트랜잭션에 상관시킬 수 있습니다. 

명시 적 모드에서는 데이터베이스 옵션 MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT을 사용하거나 FROM 절의 메모리 최적화 테이블에서 트랜잭션 격리 레벨에 대한 테이블 힌트를 코딩해야합니다.



아래는이 데이터베이스 옵션을 켜는 방법을 보여주는 예입니다.



SELECT is_memory_optimized_elevate_to_snapshot_on,  * FROM sys.databases

where is_memory_optimized_elevate_to_snapshot_on = 1



SELECT SCHEMA_NAME(Schema_id) SchemaName,

name TableName,

is_memory_optimized,

durability_desc,

create_date, modify_date

FROM sys.tables

where is_memory_optimized = 1

GO



ALTER DATABASE YourDatabase SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;



--

ALTER DATABASE [dbdominos2011] REMOVE FILE memory_optimized_table;

ALTER DATABASE [dbdominos2011] REMOVE FILEGROUP InMemoryGroup;

ALTER DATABASE [dbdominos2011] ADD FILE ( NAME = N'memory_optimized_web_member', FILENAME = N'E:\DATA\memory_optimized_table_web_member' ) TO FILEGROUP InMemoryGroup;

GO

-- Backup Script
DECLARE @name VARCHAR(50) -- database name   
DECLARE @path VARCHAR(256) -- path for backup files   
DECLARE @fileName VARCHAR(256) -- filename for backup   
DECLARE @fileDate VARCHAR(20) -- used for file name  

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR FOR
      SELECT name
      FROM master.dbo.sysdatabases  
      WHERE name NOT IN ('master','model','msdb','tempdb')  
      AND DATABASEPROPERTYEX(name, 'Recovery') IN ('FULL','BULK_LOGGED') 

OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @name    

WHILE @@FETCH_STATUS = 0    
BEGIN    
      SET @fileName = @path + @name + '_' + @fileDate + '.TRN'   
      BACKUP LOG @name TO DISK = @fileName   

      FETCH NEXT FROM db_cursor INTO @name    
END    

CLOSE db_cursor    
DEALLOCATE db_cursor

활동 모니터를 보면 최근에 비싼 쿼리와 쿼리 열 아래에 "fetch api_cursor0000000000000003"이 표시됩니다. 이게 뭐야? 이 쿼리 뒤에 무엇이 실행되고 있는지 어떻게 알 수 있습니까?

분명히 그 질문의 힌트는 "Fetch and Cursor"키워드였습니다. 그래서 시작점이 무엇인지 알았습니다. 쿼리를 추적하고 싶지만 프로파일 러를 캡처하거나 sys.sysprocesses 또는 DBCC INPUTBUFFER의 기존 방법을 사용하면 다음과 같은 상황이 몇 번이나 나타납니다.

프로파일 러에서도 똑같은 것은 아래와 같습니다.

EXEC sp_cursorfetch 180150003,16,8,1

또는

FETCH API_CURSOR0000000000000001

참고 : 쿼리의 숫자는 환경에 따라 다를 수 있습니다. 다음은 문제를 시뮬레이션하는 VBScript 코드입니다.

Dim strConnection
Dim MyConnection
Dim MyRecordSet
Dim strSQL
strConnection = “Provider=SQLOLEDB;Data Source=.\SQL2014;Initial Catalog=Master;Integrated Security=SSPI;”
Set MyConnection = CreateObject(“ADODB.Connection”)
MyConnection.Open strConnection
Set MyRecordSet = CreateObject(“ADODB.recordset”)
strSQL = “SELECT TOP 3 * FROM sysobjects”
MyRecordSet.Cursorlocation = 2
MyRecordSet.Open strSQL, MyConnection, 3,3
MyRecordSet.MoveFirst
WHILE NOT MyRecordSet.EOF
MsgBox(MyRecordSet(“name”).value)
MyRecordSet.MoveNext
WEND
MyRecordSet.Close
Set MyRecordSet = Nothing

문제를 재현하려면 위의 코드를 파일에 저장하고 확장명 .vbs를 유지할 수 있습니다. 그런 다음 명령 프롬프트에서 실행할 수 있습니다. 환경에 맞게 연결 문자열에서 Source 값을 변경해야 할 수 있습니다. VB 스크립트를 실행하면 테이블 이름이 포함 된 팝업이 표시됩니다. 프로파일 러가 처음부터 캡처 된 경우 쿼리를 알 수있는 sp_cursoropen이 표시되어야합니다. 쿼리가 이미 실행중인 경우 아래 쿼리를 사용할 수 있습니다.

SELECT creation_time,
cursor_id,
c.session_id,
c.properties,
c.creation_time,
c.is_open,
SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, (
( CASE c.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE c.statement_end_offset
END - c.statement_start_offset) / 2) + 1) AS statement_text
FROM   sys.Dm_exec_cursors(0) AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st
GO

다음은 샘플 실행입니다. 보시다시피 커서를 연 쿼리 텍스트를 얻을 수 있습니다.

참고 : cursor_id는 sp_cursorfetch에서 본 것과 동일합니다. 또한 전체 프로필을 캡처하면 아래 출력을 볼 수 있습니다.

쿼리가 시작된 이후 전체 프로파일 러를 보거나 캡처 sp_cursorfetch 하거나 sys.Dm_exec_cursors를 사용하여 정확한 쿼리를 가져옵니다.FETCH API_CURSOR0000000000000001