SQL

통계 자동 갱신 조건과 별개로 옵티마이저 판단
SQL Server는 통계가 오래되거나 변경되면 자동으로 갱신하고 그때 계획을 다시 컴파일합니다.
하지만 통계 갱신이 없더라도, 데이터 분포 변화가 심해 기존 계획이 비효율적이라고 판단되면 옵티마이저가 강제 Recompile을 트리거할 수 있습니다.
 
DML로 인한 데이터 양 변화
예를 들어, 특정 테이블에 대량의 INSERT/DELETE가 발생해 행 수가 급격히 변하면, 기존 계획의 조인 전략(예: Nested Loop vs Hash Join)이 더 이상 적합하지 않을 수 있습니다.
이 경우 옵티마이저는 계획 캐시를 무효화하고 새 계획을 생성합니다.
ü   대량 DML로 인해 통계 임계치 초과 시 자동 갱신 → 기존 계획의 카디널리티 추정이 틀려서 재컴파일 발생

옵션 및 힌트 영향
OPTION(RECOMPILE) 힌트가 있거나, 저장 프로시저에 WITH RECOMPILE이 설정된 경우 매 실행마다 새 계획을 생성합니다.
파라미터 스니핑으로 인해 특정 값에서 기존 계획이 비효율적일 때도 Recompile이 발생합니다.
 
메모리 압박 또는 캐시 제거
DBCC FREEPROCCACHE 실행, 메모리 부족으로 인한 캐시 제거 등도 재컴파일을 유발합니다.
 
스키마 변경
o    ALTER TABLE, ALTER VIEW, 인덱스 생성/삭제
o    제약조건, 열 추가/삭제 → 계획이 참조하는 객체 구조가 바뀌면 무효화

 
SET 옵션 변경
ANSI_NULLS, QUOTED_IDENTIFIER 등 세션 옵션 변경 시
 
임시 테이블 변경
Temp Table 구조 변경, 인덱스 추가/삭제
 
기타
Query Store 강제 계획 적용 실패
데이터베이스 버전 변경
파티션 뷰 변경 등

또한 통계 갱신이 없더라도 옵티마이저는 내부 임계값을 기준으로 판단합니다.

예를 들어 특정 테이블에서 수백만 건의 INSERT가 발생하게 될 경우, 통계는 아직 갱신되지 않았지만, 옵티마이저는 기존 계획이 잘못될 가능성이 높다고 판단하여 Recompile을 수행할 수 있고, 파라미터 값 변화로 인해 기존 Plan이 비효율적으로 판단될 경우 Recompile을 수행할 수 있습니다.

Recompile이 발생하는 경우는 여러가지 이유가 있으며 테이블의 컬럼 및 인덱스 변경, Set옵션의 변경, 서버 재시작, 플랜캐시의 상황으로 캐시에서 밀려난 후 Compile, RECOMPILE 힌트, 명시적인 SP_Recompile등의 요소에 의해 발생할 수 있습니다.

: Recompile a Stored Procedure - SQL Server | Microsoft Learn

1. XEvent저장 Default경로확인

SQL Server의 시간은 운영체제(Windows)의 시간이므로, 정상적인 DST 전환만으로 SQL Server나 서버 자체의 리부팅은 필요 없습니다.

SQL Server는 Windows API(GetSystemTimeAsFileTime)로 시스템 시간을 그대로 사용합니다.

SYSDATETIME (Transact-SQL) - SQL Server | Microsoft Learn

해당 시간 전환 전/후로 아래와 같이 쿼리하여 현재 SQL Server가 인지하는 시간값을 retrieve 하실 수 있으며,

혹시 해당 시간 전환이 잘 안 될 경우 서버에서 제대로 시간 변경이 안 되는 것이므로 이 때 NTP 서버와의 통신 상태 점검 및 Windows 레벨에서의 시간 설정을 확인해 보실 수 있습니다.

sys.time_zone_info (Transact-SQL) - SQL Server | Microsoft Learn

WRITELOG는 SQL Server가 트랜잭션 로그에 변경 내용을 디스크에 기록할 때, 해당 작업이 완료될 때까지 대기하는 동안 발생하는 Wait Type입니다.
디스크 I/O 성능이 낮거나 순간적으로 트랜잭션 로그 기록 량이 많을 경우 이 대기가 증가할 수 있습니다.

로그 기록 지연은 해당 트랜잭션뿐 아니라 다른 트랜잭션에도 영향을 미쳐 전체 성능 저하를 유발할 수 있습니다.
WRITELOG 대기의 일반적인 원인은 다음 URL을 참고할 수 있습니다.

Troubleshoot slow SQL Server performance caused by I/O issues - SQL Server | Microsoft Learn

WRITELOG 대기 원인 분석을 위해 성능 모니터와 세션 정보 수집을 권장 드립니다.
성능 카운터는 전반적인 시스템 상태를 확인하는 데 유용하며, 세션 정보는 어떤 쿼리나 트랜잭션이 WRITELOG 대기를 발생시키는지 파악하고 세부 분석하는 데 도움이 됩니다.
수집 방법은 아래와 같습니다.

1. SQL 성능 카운터 수집 

2. 세션 정보

초단위로 DMV를 사용하여 세션 정보를 기록합니다.

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)