SQL

1.     파티션 테이블 압축시 서비스 영향도

-> 온라인으로 가능한지, IO 발생 및 압축진행중인 파티션 사용시 lock 발생 등의 위험요소가 궁금합니다.

-     파티션별 압축의 경우에도 전체 인덱싱과 마찬가지로 온라인 인덱싱을 지원합니다.

Online Indexing의 경우, 인덱싱 작업중 대상 테이블/파티션에 대한 CRUD가 가능하나, 아래와 같이 단계에 따라 작업 및 잠금을 획득하며, 이 과정에서 여러가지 형태의 성능 경합과 잠금이 발생합니다.

다만 해당 환경의 성능 여유가 충분한 상황이라면, 일반적으로 이와 같은 성능 경합이 심각한 성능 영향을 주지는 않습니다.

이와 관련하여서는 다음의 문서를 통해 조금 더 세부적인 내용을 확인하실 수 있습니다.

: How Online Index Operations Work - SQL Server | Microsoft Learn

Phase	Source activity	Source locks	Architecture
Preparation : Short phase	-  System metadata preparation to create the new empty index structure.   -  A snapshot of the table is defined. That is, row versioning is used to provide transaction-level read consistency.   -  Concurrent user write operations on the source are blocked for a short period. No concurrent DDL operations are allowed except creating multiple nonclustered indexes.	-  S (Shared) on the table*   -  IS (Intent Shared)   -  INDEX_BUILD_INTERNAL_RESOURCE**
Build : Main phase	-  The data is scanned, sorted, merged, and inserted into the target in bulk load operations.   -  Concurrent user select, insert, update, and delete operations are applied to both the pre-existing indexes and any new indexes being built.	-  IS   -  INDEX_BUILD_INTERNAL_RESOURCE**
Final : Short phase	-  All uncommitted update transactions must complete before this phase starts.   -  Depending on the acquired lock, all new user read or write transactions are blocked for a short period until this phase is completed.   -  System metadata is updated to replace the source with the target.   -  The source is dropped if it is required. For example, after rebuilding or dropping a clustered index.	-  INDEX_BUILD_INTERNAL_RESOURCE**   -  S on the table if creating a nonclustered index.*   -  SCH-M (Schema Modification) if any source structure (index or table) is dropped.*

-     다음은 온라인 인덱싱 작업에 대한 일반적인 가이드라인입니다. Online Indexing 작업 중 발생할 수 있는 여러가지 이슈사항과 사전 고려사항을 정리하고 있으며,
Update 부하가 높은 환경에서 Online Indexing 중, 종종 발생하는 Deadlock(자동으로 해소)과 같은 증상도 해당 문서를 통해 확인할 수 있습니다.

: Guidelines for online index operations - SQL Server | Microsoft Learn

-> 온라인으로 절대 해서는 안되는 작업인지

-     온라인 수행에 문제는 없습니다. 다만 고객환경의 비즈니스로직에서의 성능 영향도를 파악하기 위해 고객사 내부적으로 충분한 테스트를 거쳐 검증하신 후 작업을 수행하실 것을 권장 드립니다.

-> 압축 진행중인 해당 파티션에만 영향을 주는 것인지

-     특정 파티션에 대해 압축을 수행하는 경우, 결과적으로는 해당 파티션에만 영향을 주는 것이 맞습니다.

하지만 압축을 진행하는 과정의 마지막 단계에서 Metadata를 업데이트 하며 Sch-M 잠금을 획득하므로, 동 시간대 대상 테이블에 대해 Sch-S/Sch-M을 요청하는 작업들과의 일시적인 성능 경합이 있을 수 있습니다.

2.     테이블/인덱스 압축시 내부 프로세스 및 모니터링 필수 요소

-> 압축대상 파티션을 바로 압축을 시도 하는지 혹은 별도의 데이터파일 여유 공간에 생성후 전환 (rebuild 처럼) 등등

-     인덱스의 파티션별 압축 작업 또한 마찬가지로 ALTER INDEX REBUILD 작업을 수행하며, 데이터파일 신규 공간을 할당 받아 Sort 및 인덱스를 생성한 후 기존의 개체를 대체하게 되므로 추가 공간을 사용하게 됩니다.

예를 들어 30GB 크기의 A인덱스의 1번 파티션을 압축하여 10GB로 압축이 수행된 경우, 작업 시작 시점에 Datafile 내 여유공간이 없었다면 데이터파일의 크기가 10GB 증가하게 됩니다.

-     Sort_in_tempdb 옵션을 사용하는 경우에도 새로운 공간에 신규 인덱스를 배치한 후 기존의 인덱스 개체를 제거하므로 추가 Disk 공간의 사용은 발생합니다.

-> 롤백이 발생하게 되나요?

-     Offline Indexing의 경우 작업을 중단하는 경우 해당 작업에 대한 Rollback이 발생하며, Online Indexing의 경우에도 작업 중단에 의해 메타데이터 업데이트를 위한 짧은 Rollback이 필요합니다.

-     작업 수행 중 다음의 내용을 모니터링 하실 수 있을 것으로 의견 드립니다.

①     Data / Log Disk 사용률 모니터링

②     Blocked Process 발생 여부 모니터링

③     Data File 사용률 모니터링

④     Log File 사용률 모니터링

-> 파티션 압축 작업을 진행하게 되면 로그 사용량의 변화는 어떻게 되는지 궁금합니다.

-     전체 인덱스를 한 번에 압축하는 경우와 한 개 파티션 씩 압축하는 경우 모두 거의 유사한 크기의 Transaction Log가 발생하는 것으로 확인됩니다.

##SQL Version :Microsoft SQL Server 2019 (RTM-CU17) (KB5016394) - 15.0.4249.2 (X64)

확인사항]

먼저 DTC실패(Error: 8509)기록된후 Rollback하는 과정에서 Dump가 생성으며, 해당 이슈는 이미 알려진 이슈로 CU21에서 FIX가 되었습니다.

해당 Dump는 in-memory Table사용과정에서 발생할수있으며, 고객사같은 경우는 DTC Transaction이 Rollback 되는 과정에서 해당 known issue로 인한 Dump가 생성었고 이로인한 가용성그룹이 Resolving상태로 빠지게됩니다.

이후 Leasetime out이 발생하나 FailureConditionLevel(2)로 Failover가 자동적으로 Triggered되지 않았습니다.

해당 이슈가 Failover를 Trigger하는 부차적인 이슈가 있으며, 이로인한 failover를 반복적으로 수행되나 해당 역할을 Online시키지 못함에 따라 반복적으로 dump로 인한 failover가 수행되었습니다.

이과정에서 large transaction rollback및 반복적인 failover시도로인한 DB연결이슈(983)가 발생한 것으로 확인하였습니다.

이후 고객사에서 LES Server재시작(2024-04-02 14:49)이후 정상적으로 'DATABASE_P03' Online되며 AG가 정상적으로 Online되었습니다.

[제안사항]

해당 이슈는 CU21에서 FIX되었으며, 최소 CU21로 patch하시는 것을 제안드립니다.

Cumulative update 21 for SQL Server 2019 (KB5025808) - SQL Server | Microsoft Learn

다만, 기존 known issue들에 의 한 잠재적인 위험요소가 있기에 가장 마지막 버젼인 CU25로 업그레이드 하시는 것을 권장드립니다.

Download SQL Server® 2019 for Microsoft® Windows Latest Cumulative Update from Official Microsoft Download Center

DECLARE @SERVER_ROLE nvarchar(50)

 
SELECT

    @SERVER_ROLE = A.ROLE_DESC

FROM sys.dm_hadr_availability_replica_states AS A

    INNER JOIN sys.availability_groups AS B ON A.GROUP_ID = B.GROUP_ID

WHERE A.IS_LOCAL = 1


IF @SERVER_ROLE = 'PRIMARY'

BEGIN
	PRINT ' Go To the next step.'
END

ELSE
	BEGIN
		RAISERROR ('Do not go to the next step.', 16, 1)
	END

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = 'bar@baz.com;',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

https://sqltouch.blogspot.com/2014/03/free-real-time-sql-server-performance.html

Download:.............. http://bit.ly/2NTn6Zi

 

Agreement:

Figure: SQL Performance Monitor

A challenge:
Retrieving and visualizing the SQL Server performance data is always a challenge and a tedious task for SQL Server database professionals. Utilizing the Windows PerfMon application is the easiest way to perform this task as well as querying “sys.dm_os_performance_counters” and some other DMVs brings a lot of useful information.

Starting from SQL Server 2005, Microsoft has introduced DMV to query various internal metadata directly to explore various health status data. Although collecting and analyzing SQL Server performance data in a regular basis provides trending ability, monitoring real-time performance data is critical to understand an ongoing performance condition that is occurring.

We are all familiar with built-in “SQL Server Activity Monitor” and obviously it is a good starting point to troubleshoot some SQL Server issues. However, the capacity of this tool is limited as it does not provide other performance metrics which are important to understand the server health status. To extend this idea especially during a performance condition, I have attempted to develop a “SQL Performance Monitor” desktop app by including some other interesting metrics which I believe might be helpful to troubleshoot or understand a problem.

This tool collects more than 50+ performance data directly from SQL Server in real-time and shows data in the chart continuously. Also, it does not require any installation and configuration.

Data collection:
SQL Scripts used in my tool are excerpted from SSMS and some are collected from various forums which are freely available. My understanding is that all the scripts that I have used are reliable however if any are not working, please let me know and I will attempt to fix the issue.

How does it work?
1.           Has the ability to monitor only a single SQL instance at a time and can be used against all editions of SQL Server from 2005 to SQL 2014. 

3.           Performance data will be saved automatically as they are collected in a SQLite database (sqlmonitor.db3).
4.           All saved performance data can be queried, and then can be exported as a CSV format. As “sqlmonitor.db3” is not protected therefore it can be opened with any SQLite tool.

Limitations:
1.           It has no notification system, such as email, alert, popup.
2.           It is a desktop 32-bit application, cannot run as a service.
3.           Chart colors have no special meaning.

Known Limitations:
(a)       SQL 2005 – in the “Server Info” tab the “Available Memory” will be zero.
(b)       CPU utilization has been calculated from “Resource Pool” and @@CPU_BUSY. Due to the internal limitation of SQL Server, and feature limitation of Standard and Express editions, CPU value may show zero on the chart. In Enterprise edition, CPU utilization will not be zero.

How to run:

Connect to a database server:
The tool bar of “SQL Performance Monitor”

Figure#1: Tool bar of SQL Activity Monitor

First time connection:
To connect a SQL Server instance, click the “SQL Server to Monitor” button. Supply the required information and then click “Try Connect” in the connection dialog box. Once connected, close the connection dialog box or choose another server to connect to.

All charts will be populated for an hour with blank data once a connection is made. It continues to collect and display data based on the duration configured on the tool bar. All collected data will be saved in a SQLite database (sqlmonitor.db) for later review and analysis.

Using a saved connection:
A successful connection can be saved for later use. Once the tool successfully connects to a database server, click the “save connection” button to save the connection string. An encoded text file will be created in the same folder with the “.txt” extension where the “SQLMonitor.exe” resides.

From the bottom list box of the “SQL Server Connection” (figure#2) dialog box, double click a previously saved file to connect to a SQL Server instance.

Couple of Screenshots from “SQL Performance Monitor”

Figure#2: SQL Server Connection dialog

Figure#3B: Viewing all sessions

Historical data:
In the history tab, put “SQL Instance Name” and “date” to query historical data. Click any column header to view data in the chart. All data and charts can be saved.

Figure#4: Historical data browse

Figure#5: Summarizing historical data

SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id

ALTER FUNCTION [dbo].[GetDBSize] 
(
    @db_name NVARCHAR(100)
)
RETURNS TABLE
AS
RETURN

  SELECT 
        database_name = DB_NAME(database_id)
      , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
      , row_size_mb = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2))
      , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
  FROM sys.master_files WITH(NOWAIT)
  WHERE database_id = DB_ID(@db_name)
      OR @db_name IS NULL
  GROUP BY database_id

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
      database_id INT PRIMARY KEY
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT
      d.database_id
    , d.name
    , d.state_desc
    , d.recovery_model_desc
    , t.total_size
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
    , bu.full_last_date
    , bu.full_size
    , bu.log_last_date
    , bu.log_size
FROM (
    SELECT
          database_id
        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
    SELECT
          database_name
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_finish_date
            , backup_size =
                        CAST(CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0 AS DECIMAL(18,2))
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC

use master
DECLARE @xQry NVARCHAR(MAX)=''
SELECT @xQry+= ' UNION ALL SELECT '''+name+''' COLLATE Modern_Spanish_CI_AS AS [Database], 
    schema_name(tab.schema_id) + ''.'' + tab.name COLLATE Modern_Spanish_CI_AS AS [table], 
        cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
        cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
    from '+name+'.sys.tables tab
    join '+name+'.sys.indexes ind 
         on tab.object_id = ind.object_id
    join '+name+'.sys.partitions part 
         on ind.object_id = part.object_id and ind.index_id = part.index_id
    join '+name+'.sys.allocation_units spc
         on part.partition_id = spc.container_id
    group by schema_name(tab.schema_id) + ''.'' + tab.name COLLATE Modern_Spanish_CI_AS'
FROM sys.databases 

SET @xQry= RIGHT(@xQry,LEN(@xQry)-11) + ' order by 3 desc'
EXEC (@xQry)

MERGE 문을 사용하면 변경할 테이블에 데이터가 존재하는지 체크하고, UPDATE, DELETE, INSERT를 한 번에 작업이 가능하다. MERGE 문을 사용하지 않을 경우 해당 조건으로 테이블을 SELECT 한 후 IF 조건을 사용하여 UPDATE나 INSERT로 분기하는 로직을 작성해야 하는 번거로움이 있다.

MERGE 문의 경우 단일(한개의) 테이블에 UPDATE 또는 INSERT를 하는 경우 많이 사용하지만, 두개의 테이블을 비교하거나 서브 쿼리의 결과에 따라서 UPDATE, INSERT 작업이 가능하다.

MSSQL MERGE 문

단일 테이블 사용법 (DUAL)

오라클에서는 DUAL이라는 dummy 테이블을 USING 절에 사용하면 단일 테이블 작업이 간단하지만, MSSQL에서는 DUAL 테이블이 없기 때문에 dummy 서브 쿼리를 사용하면 된다.

MERGE INTO dept AS a
USING (SELECT 1 AS dual) AS b
   ON (a.deptno = 50)
 WHEN MATCHED THEN
   UPDATE SET a.dname = 'IT', a.loc = 'SOUTHLAKE'
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) VALUES(50, 'IT', 'SOUTHLAKE')
;

(SELECT 1 AS dual) AS b 이부분은 dummy 서브 쿼리 이므로 그대로 복사해서 사용하면 된다.

dept 테이블에 deptno = '50'에 만족하는 값이 있으면 UPDATE, 없으면 INSERT 한다.

DECLARE @deptno INT = 50
DECLARE @dname NVARCHAR(14) = 'IT'
DECLARE @loc NVARCHAR(13) = 'SOUTHLAKE'

MERGE INTO dept AS a
USING (SELECT 1 AS dual) AS b
   ON (a.deptno = @deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = @dname, a.loc = @loc
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) VALUES(@deptno, @dname, @loc)
;

동일한 쿼리문을 조금 더 이해하기 쉽도록 변수를 사용하여 작성하다.

서브 쿼리를 이용하는 방법

DECLARE @deptno INT = 50

MERGE INTO dept AS a
USING (SELECT DISTINCT 
              d.deptno AS deptno
            , d.dname  AS dname
            , d.loc    AS loc
         FROM emp AS e
        INNER JOIN dept_history AS d
           ON e.deptno = d.deptno
        WHERE e.deptno = @deptno) AS b
   ON (a.deptno = b.deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = b.dname
            , a.loc   = b.dname
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) 
   VALUES(b.deptno, b.dname, b.loc)
;

USING 절에 서브 쿼리를 사용하는 방법을 설명한 쿼리이다.

emp 테이블에 deptno = '50'이 존재하고, 서브 쿼리 결과와 dept 테이블을 비교하여 존재 여부에 따라서 UPDATE, INSERT 한다.

두개의 테이블 조인하는 방법

MERGE INTO dept AS a
USING dept_history AS b
   ON (a.deptno = b.deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = b.dname
            , a.loc   = b.loc
 WHEN NOT MATCHED THEN
   INSERT(deptno, dname, loc) 
   VALUES(b.deptno, b.dname, b.loc)
;

dept_history 테이블의 값이 dept 테이블에 존재하는 경우, dept_history 테이블의 값으로 UPDATE, 없으면 INSERT 한다.

기타 사용법

ON 절에 WHERE 절과 유사하게 AND, OR 를 사용하여 여러개의 조건을 부여할 수 있다.

WHEN 절에도 MATCHED, NOT MATCHED 외에 추가로 조건을 부여할 수 있다.

MERGE INTO dept AS a
USING dept_history AS b
   ON (a.deptno = b.deptno)
 WHEN MATCHED THEN
   UPDATE SET a.dname = b.dname, a.loc = b.loc
 WHEN NOT MATCHED BY TARGET THEN
   INSERT(deptno, dname, loc) VALUES(b.deptno, b.dname, b.loc)
 WHEN NOT MATCHED BY SOURCE THEN
   DELETE
;

NOT MATCHED 인 경우 BY TARGET와 BY SOURCE 를 사용할 수 있다. 

NOT MATCHED BY TARGET (= NOT MATCHED)

TARGET 테이블에 데이터가 없는 경우 TARGET 테이블에 INSERT

NOT MATCHED와 동일 하므로 BY TARGET는 생략해도 된다

NOT MATCHED BY SOURCE

SOURCE 테이블에는 없고 TARGET 테이블에만 존재하는 데이터를 TARGET 테이블에서 DELETE

NOT MATCHED BY SOURCE

DELETE 문에는 WHERE 조건문을 작성하지 않는다.

필요시 WHEN 절에 조건을 작성한다.

MERGE 문 사용 시 주의사항

USING 절에 별칭이 없을 경우

오류 메시지 : 키워드 'ON' 근처의 구문이 잘못되었습니다.

쿼리문 끝에 세미콜론이 없을 경우

오류 메시지 : MERGE 문은 세미콜론(;)으로 종료해야 합니다.

USING 절의 데이터에 변경할 테이블과 비교할 테이블의 KEY 컬럼 값이 중복으로 존재 할 경우

오류 메시지 : MERGE 문이 동일한 행을 여러 번 UPDATE 또는 DELETE하려고 했습니다. 대상 행이 둘 이상의 원본 행과 일치하면 이런 경우가 발생합니다. MERGE 문은 대상 테이블의 동일한 행을 여러 번 UPDATE/DELETE할 수 없습니다. ON 절을 구체화하여 대상 행이 하나의 원본 행하고만 일치하도록 하거나 GROUP BY 절을 사용하여 원본 행을 그룹화하십시오.

SQL Server에서 매개 변수 스니핑이란 무엇입니까?
임시 또는 저장 프로 시저를 실행하는 모든 배치는 향후 사용을 위해 계획 캐시에 보관되는 쿼리 계획을 생성합니다. SQL Server는 데이터를 검색하기 위해 최상의 쿼리 계획을 만들려고 시도하지만 계획 캐시의 경우 항상 명확 해 보이는 것은 아닙니다.

SQL Server가 최상의 계획을 선택하는 방법은 비용 추정입니다. 예를 들어 어떤 것이 가장 좋은지 물어 보면 인덱스 검색 후 키 조회 또는 테이블 스캔이 첫 번째로 대답 할 수 있지만 조회 횟수에 따라 다릅니다. 즉, 검색되는 데이터의 양에 따라 다릅니다. 따라서 최상의 쿼리 계획은 입력 매개 변수와 통계를 기반으로 한 카디널리티 추정을 고려합니다.

옵티마이 저가 실행 계획을 생성 할 때 매개 변수 값을 스니핑합니다. 이것은 문제가 아닙니다. 사실 최상의 계획을 세우는 데 필요합니다. 쿼리가 다른 데이터 배포에 최적화 된 이전에 생성 된 계획을 사용할 때 문제가 발생합니다.

대부분의 경우 데이터베이스 워크로드는 동종이므로 매개 변수 스니핑은 문제가되지 않습니다. 그러나 소수의 경우에 이것은 문제가되고 그 결과는 극적 일 수 있습니다.

작동중인 SQL Server 매개 변수 스니핑
이제 예제를 통해 매개 변수 스니핑 문제를 설명하겠습니다.

1. CREATE DATABASE script.

USE [master]
GO

CREATE DATABASE [TestDB]
CONTAINMENT = NONE
ON PRIMARY 
( NAME = N'TestDB', FILENAME = N'E:\MSSQL\TestDB.mdf' , 
SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON 
( NAME = N'TestDB_log', FILENAME = N'E:\MSSQL\TestDB.ldf' , 
SIZE = 5MB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO

ALTER DATABASE [TestDB] SET RECOVERY SIMPLE

2. Create two simple tables.

USE TestDB
GO

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
  DROP TABLE dbo.Customers
GO

CREATE TABLE Customers
  (
   CustomerID INT NOT NULL IDENTITY(1,1) ,
   CustomerName VARCHAR(50) NOT NULL ,
   CustomerAddress VARCHAR(50) NOT NULL ,
   [State] CHAR(2) NOT NULL ,
   CustomerCategoryID CHAR(1) NOT NULL ,
   LastBuyDate DATETIME ,
   PRIMARY KEY CLUSTERED ( CustomerID )
  )

IF OBJECT_ID('dbo.CustomerCategory', 'U') IS NOT NULL
  DROP TABLE dbo.CustomerCategory
GO

CREATE TABLE CustomerCategory
  (
   CustomerCategoryID CHAR(1) NOT NULL ,
   CategoryDescription VARCHAR(50) NOT NULL ,
   PRIMARY KEY CLUSTERED ( CustomerCategoryID )
  )

CREATE INDEX IX_Customers_CustomerCategoryID 
ON Customers(CustomerCategoryID)

3. The idea with the sample data is to create an odd distribution.

USE TestDB
GO

INSERT INTO [dbo].[Customers] ( 
                [CustomerName],
                [CustomerAddress],
                [State],
                [CustomerCategoryID],
                [LastBuyDate])
SELECT 
    'Desiree Lambert',
    '271 Fabien Parkway',
    'NY',
    'B',
    '2013-01-13 21:44:21'


INSERT INTO [dbo].[Customers] ( 
                [CustomerName],
                [CustomerAddress],
                [State],
                [CustomerCategoryID],
                [LastBuyDate])
SELECT  
    'Pablo Terry',
    '29 West Milton St.',
    'DE',
    'A',
    GETDATE()

go 15000

4. Execute the following query and take a look at the query plan.

USE TestDB
GO

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = 'A'

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = 'B'

보시다시피 첫 번째 쿼리는 CustomersCategory 테이블에서 클러스터형 인덱스 검색을 수행하고 tghe Customers 테이블에서 클러스터형 인덱스 스캔을 수행하는 반면 두 번째 쿼리는 비 클러스터형 인덱스 (IX_Customers_CustomerCategoryID)를 사용합니다. 그 이유는 쿼리 최적화 프로그램이 주어진 매개 변수에서 쿼리 결과를 예상 할 수있을만큼 똑똑하고 인덱스 검색을 수행 한 후 비 클러스터형 인덱스에 대한 키 조회를 수행하는 대신 클러스터형 인덱스를 스캔하기 때문에 비용이 더 많이 듭니다. 첫 번째 쿼리는 거의 전체 테이블을 반환합니다.

5. Now we create a stored procedure to execute our query.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID

GO

6. Execute the stored procedure.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing @CustomerCategoryID 
GO

스크립트에서 먼저 DBCC FREEPROCCACHE를 실행하여 계획 캐시를 정리 한 다음 저장 프로 시저를 실행합니다. 아래 이미지를 보면 저장된 프로 시저에 대한 두 번째 호출이 주어진 매개 변수를 고려하지 않고 동일한 계획을 사용하는 방법을 볼 수 있습니다.

그런 다음 동일한 단계를 수행하지만 매개 변수를 역순으로 사용하면 동일한 동작이 표시되지만 쿼리 계획은 다릅니다.

SQL Server 매개 변수 스니핑에 대한 해결 방법

이제 문제를 해결하는 몇 가지 방법이 있습니다.

• WITH RECOMPILE 옵션을 사용하여 SQL Server 저장 프로 시저 만들기
• SQL Server 힌트 옵션 사용 (권장)
• SQL Server 힌트 옵션 (OPTIMIZE FOR) 사용
• SQL Server 저장 프로 시저에서 더미 변수 사용
• 인스턴스 수준에서 SQL Server 매개 변수 스니핑 비활성화
• 특정 SQL Server 쿼리에 대한 매개 변수 검색 비활성화

WITH RECOMPILE 옵션을 사용하여 SQL Server 저장 프로 시저 만들기

문제가 옵티마이 저가 더 이상 적합하지 않은 매개 변수로 컴파일 된 계획을 사용하는 것이라면 재 컴파일은 새 매개 변수로 새 계획을 생성 할 것입니다. 맞습니까? 이것은 가장 간단한 해결책이지만 최고는 아닙니다. 문제가 저장 프로 시저 코드 내의 단일 쿼리 인 경우 전체 프로 시저를 다시 컴파일하는 것이 최선의 방법이 아닙니다. 문제가있는 쿼리를 수정해야합니다.

또한 재 컴파일은 CPU 부하를 증가시킬 것이며 동시 시스템이 많은 경우 우리가 해결하려는 문제만큼 문제가 될 수 있습니다.

예를 들어, 다음은 재 컴파일 옵션을 사용하여 이전 저장 프로 시저를 만드는 샘플 코드입니다.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Recompile 
@CustomerCategoryID   CHAR(1)
WITH RECOMPILE
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID

GO

SQL Server 힌트 옵션 사용 (권장)

이전 단락에서 말했듯이 전체 저장 프로 시저를 다시 컴파일하는 것은 최선의 선택이 아닙니다. 힌트 RECOMPILE을 활용하여 어색한 쿼리 만 다시 컴파일 할 수 있습니다. 아래 샘플 코드를보십시오.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Query_Hint_Option_Recompile 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(RECOMPILE)

GO

이제 다음 코드를 실행하고 쿼리 계획을 살펴보십시오.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_Option_Recompile @CustomerCategoryID 
GO

이전 이미지에서 볼 수 있듯이 두 쿼리의 매개 변수에 따라 올바른 계획이 있습니다.

SQL Server 힌트 옵션 (OPTIMIZE FOR) 사용

이 힌트를 통해 최적화를위한 참조로 사용할 매개 변수 값을 설정할 수 있습니다. SQL Server OPTIMIZE FOR Hint를 사용하여 매개 변수 기반 쿼리를 최적화 하는 Greg Robidoux의 팁에서이 힌트를 읽을 수 있습니다 . 우리 시나리오에서는 프로 시저가 실행하는 데 사용할 매개 변수 값을 알지 못하기 때문에이 힌트를 사용할 수 없습니다. 그러나 SQL Server 2008 이상을 사용하는 경우 OPTIMIZE FOR UNKNOWN은 약간의 빛을 가져옵니다. 평신도 용어로 말하면 최상의 계획을 만들지 못할 것이라는 점을 경고해야합니다. 그 결과 계획은 중간에있을 것입니다. 따라서이 힌트를 사용하려면 저장 프로 시저가 잘못된 계획으로 실행되는 빈도와 쿼리가 오래 실행되는 환경에 미치는 영향을 고려해야합니다.

이 힌트를 사용하는 샘플 코드는 다음과 같습니다.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Query_Hint_Optimize_Unknown 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(OPTIMIZE FOR UNKNOWN )

GO

The next script is to execute our new Stored Procedure.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_Optimize_Unknown @CustomerCategoryID 
GO

다음은 실행 계획의 화면 캡처입니다.

SQL Server 저장 프로 시저에서 더미 변수 사용

이것은 2005 년 이전의 SQL Server 버전에서 사용 된 오래된 방법입니다. 입력 매개 변수를 로컬 변수에 할당하고 매개 변수 대신이 변수를 사용합니다.

아래 샘플 코드를보십시오.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Dummy_Var 
@CustomerCategoryID   CHAR(1)
AS
DECLARE @Dummy    CHAR(1)

SELECT @Dummy = @CustomerCategoryID

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @Dummy

GO

To execute this Stored Procedure you can use this code.

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Dummy_Var @CustomerCategoryID 
GO

결과 계획을보십시오.

인스턴스 수준에서 SQL Server 매개 변수 스니핑 비활성화

아마도 이것은 당신이 할 수있는 최악의 선택 일 것입니다. 앞서 말했듯이 매개 변수 스니핑은 그 자체로 나쁜 것은 아니며 대부분의 경우 최상의 계획을 세우는 데 매우 유용합니다. 그러나 원하는 경우 추적 플래그 4136을 설정하여 인스턴스를 시작하면 매개 변수 스니핑이 비활성화됩니다. SQL Server 2008 R2 누적 업데이트 2, SQL Server 2008 SP1 누적 업데이트 7 및 SQL Server 2005 SP3 누적 업데이트 9에서는 이 플래그를 사용하는 데 필요한 패치 수준과 이에 대한 자세한 내용을 읽을 수 있습니다. "매개 변수 스니핑"프로세스를 비활성화하는 데 사용됩니다 .

이 방법을 사용하기 전에 다음 방법을 살펴보면 훨씬 덜 과감합니다.

특정 SQL Server 쿼리에 대한 매개 변수 검색 비활성화

이것은 사용자에게 알려지지 않았을 수 있지만 쿼리는 추적 플래그를 힌트로 사용하여 쿼리 최적화 프로그램의 동작을 변경할 수 있습니다. 이를 수행하는 방법 은 OPTION 절에 QUERYTRACEON 힌트를 추가하는 것 입니다.

다음은 샘플 저장 프로 시저와 그 실행입니다.

USE TestDB
GO

CREATE PROCEDURE Test_Sniffing_Query_Hint_QUERYTRACEON 
@CustomerCategoryID   CHAR(1)
AS

SELECT C.CustomerName,
   C.LastBuyDate
FROM  dbo.Customers C
    INNER JOIN dbo.CustomerCategory CC ON CC.CustomerCategoryID = C.CustomerCategoryID
WHERE  CC.CustomerCategoryID = @CustomerCategoryID
OPTION(QUERYTRACEON 4136)

GO

USE TestDB
GO

DBCC FREEPROCCACHE()
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'B'
EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID 
GO

DECLARE @CustomerCategoryID CHAR(1)

SET @CustomerCategoryID = 'A'
EXEC dbo.Test_Sniffing_Query_Hint_QUERYTRACEON @CustomerCategoryID 
GO

실행 계획의 화면 캡처입니다.

백업 파일로부터 DB를 새로 복원할 때 곤란한 점은 일일이 DB의 User와 SQL서버의 Login을 매핑해 줘야 한다는 점이다.

아래 스크립트를 사용하면 이 작업을 한번에 처리할 수 있다.


1. User와 Login의 Sync가 맞지 않는 내역이 있는지 확인

DECLARE @nvcCollation sysname;

DECLARE @nvcStmt nvarchar(4000);

DECLARE @tblTemp table (

       DBName sysname NOT NULL,

       UserName sysname NOT NULL,

       LoginName sysname NOT NULL

);

SET @nvcCollation = CAST(DatabasePropertyEx('master', 'Collation') AS sysname);

SET @nvcStmt = N'

USE [?];

SELECT ''?'', A.name, B.loginname

FROM sys.sysusers A

       INNER JOIN master.dbo.syslogins B ON A.name COLLATE ' + @nvcCollation + N' = B.Name

       INNER JOIN master.dbo.sysdatabases C ON C.name = ''?''

WHERE A.issqluser = 1 AND (A.sid IS NOT NULL AND A.sid <> 0x0)

       AND SUSER_SNAME(A.sid) IS NULL

       AND (C.status & 32) =0 --loading

       AND (C.status & 64) =0 --pre recovery

       AND (C.status & 128) =0 --recovering

       AND (C.status & 256) =0 --not recovered

       AND (C.status & 512) =0 --offline

       AND (C.status & 1024) =0 --read only

ORDER BY A.name';

INSERT @tblTemp EXEC sp_msforeachdb @nvcStmt;

SELECT * FROM @tblTemp;

2. 위 스크립트에서 레코드가 반환된다면 => User와 Login을 Sync해 주기

DECLARE @nvcCollation sysname;

DECLARE @nvcStmt nvarchar(4000);

SET @nvcCollation = CAST(DatabasePropertyEx('master', 'Collation') AS sysname);

SET @nvcStmt = N'

USE [?];

DECLARE @nvcUserName sysname;

DECLARE @nvcLoginName sysname;

DECLARE SyncDBLogins CURSOR FOR

       SELECT A.name AS userName, B.loginname AS loginName

       FROM sys.sysusers A

             INNER JOIN master.dbo.syslogins B ON A.name COLLATE ' + @nvcCollation + N' = B.Name

             INNER JOIN master.dbo.sysdatabases C ON C.Name = ''?''

       WHERE A.issqluser = 1

             AND (A.sid IS NOT NULL AND A.sid <> 0x0)

             AND suser_sname(A.sid) IS NULL

             AND (C.status & 32) =0 --Loading

             AND (C.status & 64) =0 --pre recovery

             AND (C.status & 128) =0 --recovering

             AND (C.status & 256) =0 --not recovered

             AND (C.status & 512) =0 --offline

             AND (C.status & 1024) =0 --read only

       ORDER BY A.name;

OPEN SyncDBLogins;

FETCH NEXT FROM SyncDBLogins INTO @nvcUserName, @nvcLoginName;

WHILE @@FETCH_STATUS = 0

BEGIN

       EXEC sp_change_users_login ''update_one'', @nvcUserName, @nvcLoginName;

    FETCH NEXT FROM SyncDBLogins INTO @nvcUserName, @nvcLoginName;

END;

CLOSE SyncDBLogins;

DEALLOCATE SyncDBLogins;'

EXEC sp_msforeachdb @nvcStmt;