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 Server 인스턴스 내에서 파일 이동이 필요할 경우에 이 방법을 사용하며, 만약 다른 인스턴스나  다른 SQL Server로 이동한다면 분리 및 연결(sp_attach_db, sp_detach_db 시스템 저장 프로시져) 방법 또는 백업 및 복원 방법을 이용하면 됩니다.


[시나리오]
1. 데이터베이스명 : TestDB

2. 변경 전 파일 경로 
   1) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB.mdf
   2) C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB_log.ldf

3. 변경 후 파일 경로
   1) D:\Data\TestDB.mdf  
   2) D:\Data\TestDB_log.ldf


[작업절차]

USE MASTER
GO

-- 1. 파일의 논리 이름을 확인합니다.
SELECT file_id, name, physical_name  FROM sys.master_files WHERE database_id = db_id('LAIGO');
/* LAIGO, LAIGO_LOG */

-- 2. 30초 후 모든 작업을 롤백하고 단일 사용자 모드로 전환합니다.
ALTER DATABASE LAIGO SET SINGLE_USER WITH ROLLBACK AFTER 30


-- 3. LAIGO 데이터베이스 오프라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET OFFLINE


-- 4. mdf,ldf 파일 위치를 변경합니다.


-- 5. mdf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO, FILENAME='d:\data\laigo.mdf')


-- 6. ldf 파일 위치 정보를 변경합니다.
ALTER DATABASE LAIGO MODIFY FILE (NAME=LAIGO_LOG, FILENAME='d:\data\laigo_log.ldf')


-- 7. 데이버테이스를 온라인 상태로 전환합니다.
ALTER DATABASE LAIGO SET ONLINE


--8. 멀티 사용자 모드로 변경합니다.
ALTER DATABASE LAIGO SET MULTI_USER


-- 9. 정상적으로 변경되었는지 확인합니다.
SELECT  name, physical_name  AS CurrentLocation, state_desc  FROM  sys.master_files
WHERE  database_id  = DB_ID(N'LAIGO');

참고 : https://laigo.kr/76 [Lai Go's Blog]

USE master
GO


--1. tempdb의논리파일이름확인

SELECT name, physical_name, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')
GO


--2. ALTER DATABASE 사용하여파일위치변경

ALTER DATABASE tempdb MODIFY FILE(NAME = tempdev, FILENAME = 'd:\mssql\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE(NAME = templog, FILENAME = 'e:\mssql\templog.ldf')
GO


--3. Processor만큼파일분할및사이즈변경및파일사이즈, 증가옵션설정

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'd:\mssql\tempdev2.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'd:\mssql\tempdev3.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'd:\mssql\tempdev4.ndf' , SIZE = 20480KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 163840KB )
GO

--4.SQL Server 서비스 재시작.
 

--5.SQL Server 서비스가 시작된것을확인후 정상 이동 확인 

SELECT name, physical_name, state_desc 
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb')

참고 : http://blog.naver.com/PostView.nhn?blogId=jevida&logNo=140149742633

DBCC CHECKIDENT

-- person 스키마의 AddressType Table의 identity값 확인
USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType');  
GO  


-- Person 스키마의 AddressType Table의 identity값을 10으로 변경
-- 이후 identity값은 11을 반환
USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO

참고 : https://docs.microsoft.com/ko-kr/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017 

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

원인

SQL Server Agent 서비스 제어는 SQL Server Agent Roles 권한이 부여되어 있거나 또는 sysadmin role 이 부여되어 있어야 합니다.
test_srv 계정은 SQL Sever Agent Roles 또는 sysadmin role 이 없습니다. 

해결방법

방법1. 
SSMS - Security - Logins - test_srv - User Mapping - msdb - SQLAgentUserRole 부여(필요에 따라 SQLAgentReaderRole, SQLAgentOperatorRole 부여 가능)
단, 운영체제 외부 리소스를 사용하기 위해서는 별도의 Credentials, SQL Server Proxy Accounts 를 설정할 수 있음.

test_srv 계정에 view database 권한 필요

만약 개체 존재 여부를 먼저 체크 하는 로직을 실행 한다면 아래 권한 추가

방법2. 
sysadmin role 부여 (권장하지 않음)


참고 : https://docs.microsoft.com/ko-kr/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-2017

https://laigo.kr/497

문제

개체의 정의가 변경된다고 해서 이 개체를 참조하는 (스키마 바인딩 되지 않은) SP, UDF, VIEW의 메타데이터가 자동으로 갱신되지는 않습니다.

해결

--SQL Server 2000
참조한 개체는 Alter를 이용해서 다시 컴파일되어야 합니다. VIEW의 경우 sp_refreshview가 존재했지만, 다른 개체는 일일히 수작업을 해야 했었죠.

--SQL Server 2005
SQL Server 2005 SP2에서 sp_refreshsqlmodule 이 처음으로 소개되었습니다. 이 system sp를 이용하면 변경된 개체를 참조하는 SP, UDF, VIEW에 대해서 한번에 변경된 메타데이터를 갱신해 줍니다. 여전히 sp_refreshview도 사용 가능합니다.

실행 예)
EXEC sys.sp_refreshsqlmodule 'dbo.to_upper';

출처
http://msdn2.microsoft.com/ko-kr/library/bb326754.aspx
https://optimizer.tistory.com/entry/SQL2k5sprefreshsqlmodule-vs-sprefreshview

[요약]

1. MySQL Connector ODBC 설치
2. ODBC 데이터 원본(64) 실행
3. 시스템 DSN에 등록
4. 시스템 DSN에 등록한 이름으로 MSSQL LinkedServer 생성
5. 접속 중 MSDASQL의 스키마 또는 카탈로그 에러시, 연결되 서버 -> 공급자 -> MSDASQL -> 속성 옵션 수정
6. 오픈 쿼리로 실행 ( select * from openquery ( 'DSN이름', 'select * from tbl_a')

[구성]

Mysql 연결 드라이버 다운

• 드라이버 다운로드 : https://dev.mysql.com/downloads/connector/odbc/

다운로드 받은 파일을 설치

드라이버 설치가 완료되면 윈도우 [서버 관리자]에서 ODBC [데이터 원본]을 실행

ODBC 데이터 원본 관리자에서 [시스템 DSN]탭을 선택하고 [추가]버튼을 클릭

[새 데이터 원본 만들기] 창이 나타나면 MySQL ODBC 5.3 드라이버를 선택

연결 속성에 각 정보를 입력

Database항목에 해당 MySQL/MariaDB의 데이터베이스 목록이 보이지 않는다면 설정이 잘못되었으니 정보를 확인하고 정확하게 입력한다.

ODBC 데이터 원본 추가작업이 완료되면 시스템 데이터 원본 목록에서 사용자가 생성한 연결 정보가 나타난다.

SQL Server의 SSMS 도구에서 [새 연결된 서버]를 실행하여 링크드서버를 등록

서버 유형은 [기타 데이터 원본]에서 공급자를 OLE DB Provider for ODBC Drivers를 선택 한다.

보안 탭에서 사용자 로그인 계정과 비밀번호를 입력

링크드서버 등록이 완료되면 SSMS에서 추가된 링크드서버를 확인

[사용]

아래 스크립트는 분산 쿼리 및 원격 저장 프로시저에 사용되는 링크드서버로그인 정보를 확인한다. 사용자가 생성한 링크드명과 리모트 로그인 정보를 확인 할 수 있다.

다음 스크립트는 지정된 연결된 서버에서 테이블에 대한 정보를 반환한다.

MySQL/MariaDB 쿼리 조회는 Openquery를 사용한다.

SQL Server에서 스키마 방식으로 링크드서버 데이터를 조회하였을 때 다음과 같은 에러가 발생한다면 공급자 MSDASQL에서 공급자 옵션을 체크해야 한다.

스키마 방식의 데이터 조회 오류를 해결 하기 위해 링크드서버에서 공급자를 확장하면 연결 공급자 목록이 나타난다. MSDASQL을 선택하고 속성을 클릭한다.

MSDASQL 공급자 옵션 창이 나타나면 필요한 옵션을 선택한다.

스키마를 사용한 데이터 조회가 가능한 것을 확인 할 수 있다.

프로시저 호출의 경우 연결된 서버 속성에서 RPC 구성을 활성화해야 쿼리를 실행할 수 있다.

참고 : https://sqlmvp.tistory.com/1072?category=618825 [Data Science Lab]