DB Library

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());


WITH XMLNAMESPACES
	(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT	X.*
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') as [Schema]
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') as [Table]
	,	t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') as [Column]
	,	ic.DATA_TYPE AS ConvertFrom
	,	ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength
	,	t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo
	,	t.value('(@Length)[1]', 'int') AS ConvertToLength
FROM
(
	SELECT	db_name(qt.dbid) AS 'db_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 'ExecutionCount'
		,	ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second'
		,	DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache(min)'
		,	ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0AS 'avg_Elapsed_Time(ms)'
		,	qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'
		,	max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'
		,	(qs.total_worker_time/qs.execution_count) /1000.0 AS 'avg_Worker_Time(ms)'
		,	qs.total_worker_time/1000.0 AS 'total_Worker_Time(ms)'
		,	max_worker_time /1000.0 as 'max_worker_time(ms)'
		,	ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'
		,	total_logical_reads
		,	qs.max_logical_reads
		,	ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'
		,	total_physical_reads
		,	qs.max_physical_reads
		,	ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'
		,	qs.total_logical_writes
		,	qs.max_logical_writes
		,	cast(qp.query_plan as xml) as query_plan
		,	OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
    FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
)X
--sys.dm_exec_cached_plans AS cp
--CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

mssql db를 사용하다보면 vlf count가 증가하는 것을 볼수 있다.

vlf count가 증가하면 DML 및 로그 백업시 지연 이슈가 발생 할 수 있기 때문에 적절하게 축소해 주는것이 유리하다

2019 버전에서는 계속해서 증가하지 않도록 개선이 되었으나, 이하 버전에서는 관리가 필요

vlf 확인

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 사용자 DB + TempDB 이름을 임시 테이블로 저장, 변수 선언
IF OBJECT_ID(N'tempdb..#DB') IS NOT NULL
BEGIN
	DROP TABLE #DB
END

CREATE TABLE #DB (name sysname, dbid smallint)

INSERT INTO #DB (name, dbid)
SELECT name, dbid FROM master.dbo.sysdatabases
--WHERE name not in ('master', 'msdb', 'model')
GO

print  '*** TR Log VLFs'
DECLARE @dbname SYSNAME
DECLARE @min_dbid SMALLINT
DECLARE @SQL VARCHAR(8000)
DECLARE @productversion VARCHAR(128)
DECLARE @Majorversion INT

SET @productversion = convert(VARCHAR(128), SERVERPROPERTY('ProductVersion'))
SET @Majorversion = convert(INT, left(@productversion, charindex('.', @productversion) - 1))

IF OBJECT_ID(N'tempdb..#LOGFILECNT') IS NOT NULL 
BEGIN
     DROP TABLE #LOGFILECNT
END


CREATE TABLE #LOGFILECNT (
	seq INT identity
	,dbname SYSNAME
	,logcount INT
	)

SET @min_dbid = 0

WHILE (@min_dbid IS NOT NULL)
BEGIN
	SELECT @min_dbid = MIN(dbid)
	FROM #DB
	WHERE dbid > @min_dbid

	IF (@min_dbid IS NULL)
		BREAK

	SELECT @dbname = NAME
	FROM #DB
	WHERE dbid = @min_dbid

	INSERT INTO #LOGFILECNT (dbname)
	SELECT @dbname

	SET @SQL = 'USE [' + @dbname + ']; '

	IF @Majorversion < 11
	BEGIN
		SET @SQL = @SQL + '
  CREATE TABLE #T1 (c1 nvarchar(30), c2 nvarchar(30), c3 nvarchar(30), c4 nvarchar(30), c5 nvarchar(30), c6 nvarchar(30), c7 nvarchar(30))
  INSERT INTO #T1 EXEC (''DBCC LOGINFO'')
  UPDATE #LOGFILECNT SET logcount = (SELECT count(*) FROM #T1)
  WHERE dbname = ''' + @dbname + '''
  DROP TABLE #T1
  '
	END
	ELSE
	BEGIN
		SET @SQL = @SQL + '
  CREATE TABLE #T1 (c1 nvarchar(30), c2 nvarchar(30), c3 nvarchar(30), c4 nvarchar(30), c5 nvarchar(30), c6 nvarchar(30), c7 nvarchar(30), c8 nvarchar(30))
  INSERT INTO #T1 EXEC (''DBCC LOGINFO'')
  UPDATE #LOGFILECNT SET logcount = (SELECT count(*) FROM #T1)
  WHERE dbname = ''' + @dbname + '''
  DROP TABLE #T1
  '
	END

	EXEC (@SQL)
END

SELECT dbname
	,logcount
FROM #LOGFILECNT
ORDER BY seq

IF OBJECT_ID(N'tempdb..#LOGFILECNT') IS NOT NULL 
BEGIN
     DROP TABLE #LOGFILECNT
END
GO

vlf 축소

-- checkpoit 발생
CHECKPOINT

-- 버퍼 클린
DBCC DROPCLEANBUFFERS

-- Log Backup
BACKUP LOG TestDB TO  DISK = N'e:\BACKUP\TestDB_log.trn' WITH NOFORMAT, NOINIT,  NAME = N'TestDB Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

-- DB Shrink
USE TestDB
GO
DBCC SHRINKFILE (N'TestDB_log' , 0, TRUNCATEONLY)
GO



USE [master]
GO
ALTER DATABASE TestDB MODIFY FILE ( NAME = N'TestDB_log', SIZE = 10GB )
GO


dbcc loginfo(TestDB)

[waiteresource 정보]
RID. 잠금이 보유 또는 요청된 테이블 내의 단일 행을 식별합니다. RID는 RID: db_id:file_id:page_no:row_no로 표시됩니다. 예를 들면 RID: 6:1:20789:0과 같습니다.

OBJECT. 잠금이 보유 또는 요청된 테이블을 식별합니다. OBJECT는 OBJECT: db_id:object_id로 표시됩니다. 예를 들면 TAB: 6:2009058193과 같습니다.

KEY. 잠금이 보유 또는 요청된 인덱스 내의 키 범위를 식별합니다. KEY는 KEY: db_id:hobt_id (index key hash value)로 표시됩니다. 예를 들면 KEY: 6:72057594057457664 (350007a4d329)와 같습니다.

PAG. 잠금이 보유 또는 요청된 페이지 리소스를 식별합니다. PAG는 PAG: db_id:file_id:page_no로 표시됩니다. 예를 들면 PAG: 6:1:20789와 같습니다.

EXT. 익스텐트 구조를 식별합니다. EXT는 EXT: db_id:file_id:extent_no로 표시됩니다. 예를 들면 EXT: 6:1:9와 같습니다.

DB. 데이터베이스 잠금을 식별합니다. DB는 다음 방법 중 하나로 표시됩니다.

DB: db_id

DB: db_id[BULK-OP-DB]. 이 방법은 백업 데이터베이스에서 수행된 데이터베이스 잠금을 식별합니다.

DB: db_id[BULK-OP-LOG]. 이 방법은 특정 데이터베이스에 대해 백업 로그에서 수행된 잠금을 식별합니다.

APP. 응용 프로그램 리소스에서 수행된 잠금을 식별합니다. APP는 APP: lock_resource로 표시됩니다. 예를 들면 APP: Formf370f478과 같습니다

[확인 예시] 

waitresource = 11:24:3169121  -> 첫 번째 값은 DB 아이디. 
  
DBCC traceon (3604) WITH no_infomsgs          
DBCC PAGE (11,24,3169121) with  no_infomsgs, tableresults 

ParentObject Object Field VALUE 
PAGE HEADER: Page @0x000000095145E000 m_objId (AllocUnitId.idObj) 16431 
PAGE HEADER: Page @0x000000095145E000 m_indexId (AllocUnitId.idInd) 256 
PAGE HEADER: Page @0x000000095145E000 Metadata: AllocUnitId 7.20576E+16 
PAGE HEADER: Page @0x000000095145E000 Metadata: PartitionId 7.20576E+16 
PAGE HEADER: Page @0x000000095145E000 Metadata: IndexId 2                                   <---- 인덱스 아이디
PAGE HEADER: Page @0x000000095145E000 Metadata: ObjectId 132468542                     <---- 테이블 아이디 

object_id 값과 index 값을 알았으니 어느 테이블에 어느 객체인지 확인 하면 된다. 

select * from sys.objects where object_id = 132468542 
tblPlayer 132468542 NULL 1 0 U  USER_TABLE 2021-01-07 08:27:49.803 2021-01-07 08:27:49.823 0 0 0

select * from sys.indexes where object_id = 132468542 and index_id = 2
132468542 PK_tblPlayer 2 2 NONCLUSTERED 1 2 0 1 0 80 0 0 0 1 1 0 NULL NULL

결론 : tblPlayer 테이블의 PK_tblPlayer 인덱스에서 잠금이 발생

참고 : https://ceusee.tistory.com/140 [Bora.Choi]

--{SQL handle} 부분에 값을 입력하여 실행

(방법 1)
SELECT sql_handle AS Handle,
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS Text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE sql_handle = --{SQL Handle}

(방법2)
select * from sys.dm_exec_query_plan({SQL Handle})
select * from sys.dm_exec_query_plan(0x0600030D550343000000000000000)

참고

https://rauofthameem.wordpress.com/2012/09/14/sql-query-that-gets-sql-statement-from-sqlhandle/

https://holictoweb.tistory.com/17

확인 내용

* procedure가 올바른 plan을 선정하였는지

* cached plan 사이즈가 효율적으로 사용되고 있는지

* plan에 해당하는 query확인

Procedure query_id 및 plan_id 확인

하나의 SP에 여러 plan이 존재할수 있음 

그중 비효율적인 plan을 사용하는 경우 이슈가 발생

--'spTest_get' SP를 분석

select qsq.query_id, qp.plan_id, qst.query_sql_text, qp.query_plan 
from sys.query_store_query qsq
inner join sys.query_store_query_text qst
	on qsq.query_text_id = qst.query_text_id
inner join sys.query_store_plan qp
	on qp.query_id = qsq.query_id
where object_id = object_id('spTest_get')

/**
query_id	plan_id
5324	662
5324	782
5324	787
5324	807
5324	818

3315	663
8092	1200
8093	1201
**/

Cached plan 카운트 및 사이즈

adhoc 쿼리를 과도하게 호출 시 cached plan이 저장 가능한 공간을 초과 할 수 있음.

아래의 경우 1회만 사용되는 plan이 대부분인 케이스

1회만 사용되는 plan을 sp_executesql 형태로 변경하거나 동일 plan을 사용하도록 유도가 필요함

-- 모든 cached plan을 조회

select objtype, count(*) as cnt, sum(convert(bigint, size_in_bytes))/(1024*1024) as size_mb
from sys.dm_exec_cached_plans
--where usecounts = 1
group by objtype
order by cnt desc

/**
objtype	cnt	size_mb
Adhoc	156329	6906			<--
Prepared	3708	967
View	1285	181
Proc	215	253
**/


-- cached plan 사용 횟수가 1회 경우만 조회
select objtype, count(*) as cnt, sum(convert(bigint, size_in_bytes))/(1024*1024) as size_mb
from sys.dm_exec_cached_plans
where usecounts = 1
group by objtype
order by cnt desc

/**
objtype	cnt	size_mb
Adhoc	153763	1889			<--
Prepared	3145	242
Proc	41	19
**/

query_hash 통한 이슈 query 확인

이슈 query 확인 후 수정

-- 실행 count가 1인 경우만 조회

select query_hash, count(*) as cnt
from sys.dm_exec_query_stats
where execution_count = 1
group by query_hash
order by cnt desc

/**
query_hash	cnt
0xD1CC06V33C082806	135420      <--- 확인
0x3129830A6071HND2	2108
0x30E30C19A239CX39	218
0x690CY4EC5110G475	218
~
**/


-- 이슈 query text 확인

select top 100 st.text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as st
where qs.query_hash = 0xD1CC06V33C082806 

/**
USE master   INSERT INTO *******************
USE master   INSERT INTO *******************
**/