DB Library

정보

Core별 MSSQL 'max worker threads' Default 값 설정

Core 기준 : 논리 코어

'max worker threads' 설정 값 확인

• SELECT max_workers_count FROM sys.dm_os_sys_info
• sp_configure 'max worker threads'

기준

예시

CPU : 물리 코어 24 ( 논리 코어 48 )

512 + (( 48 - 4 ) * 16 ) = 1,216

 참고 : https://blog.sqlauthority.com/2018/06/15/sql-server-optimal-value-max-worker-threads/

Windows Server 환경에서 클러스터 구성시, 클러스터에 포함할 서버가 추가되지 않는 경우가 발생

1. host3.kook.com 노드 추가 > 오류 메시지 발생

2. 추가할 host3의 서비스를 확인 > Remote Registry > 시작

3. Server 서비스 > 시작

4. host3번을 다시 추가

참고

https://jkmoon.tistory.com/entry/%EC%9B%90%EA%B2%A9-%EB%A0%88%EC%A7%80%EC%8A%A4%ED%8A%B8%EB%A6%AC%EC%97%90-%EC%95%A1%EC%84%B8%EC%8A%A4%ED%95%98%EC%A7%80-%EB%AA%BB%ED%96%88%EC%8A%B5%EB%8B%88%EB%8B%A4

mssql 라이선스를 standard로 운영 도중 enterprise로 올리고 싶은 경우 가능

enterprise에서 standard로 다운그레이드는 불가능

아래 setup.exe 파일은 enterprise의 실행 파일( enterprise 설치 파일이 로컬 디스크에 있어야함 )

아래 예의 경우 SQL2016SP1_ENT_ENG 폴더에 mssql 설치 파일이 있음

-- InstanceName : 설치된 SQLServer 인스턴스 정보 ( Default : MSSQLSERVER )
-- PID : MSSQL 제품 키 ( 제품키가 포함된 버전은 생략 )
setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid> /IAcceptSQLServerLicenseTerms

참고

https://laigo.tistory.com/786

mssql 운영시 의도치 않게 데이터가 삭제되는 경우, 추적을 위한 방안

'TestDB' DB의 'tblTest'테이블 데이터 삭제 시, BAK_tblTest_Delete 테이블에 삭제 데이터 및 세션 정보 저장

USE [TestDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[delete_info_insert]
ON [dbo].[tblTest]
FOR UPDATE, DELETE AS

	DECLARE   @login_name	nvarchar(128)
		, @program_name	nvarchar(128)
		, @host_name	nvarchar(128)

			
	SELECT    @login_name = ISNULL(original_login_name, ORIGINAL_LOGIN())
		, @program_name = T1.program_name
		, @host_name = T1.host_name
	FROM sys.dm_exec_sessions T1
	WHERE T1.session_id = @@spid

	INSERT INTO BAK_tblTest_Delete ([Seq], [ID], [TestParam], [CreateDatetime], [loginname], [program_name], [hostname])
	SELECT
		[Seq], [ID], [TestParam], [CreateDatetime], @login_name, @program_name, @host_name
	FROM deleted;

"데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다."라는 에러는 MSSQL에서 사용자를 삭제하려고 할때 이 사용자가 스키마를 소유하고 있다면 삭제할 수 없다는 에러 메시지가 나온다.

위 그림처럼 SSMS에서 마우스 클릭을 통해  사용자를 삭제하려고 할때 경고 안내창이 나온다.

아래와 같이 Drop User isoltek; 라는 SQL을 실행하여 삭제하려고 하면 결과 메시지에 삭제할 수 없다는 내용이 나온다.

위 에러는 삭제할 사용자가 소유한 스키마를 dbo 로 이관 시키고 삭제하면 해결된다.

사용자가 소유한 스키마를 dbo로 이관 시키는 방법은 두가지가 있다.

가. 마우스 클릭을 통해 이관하는 방법

나. SQL 창에서 SQL을 실행하여 이관하는 방법

이해할 있도록 이 과정을 모두 캡쳐해서 붙인다.

(아래 붙인 그림들은 클릭하면 모두 원본으로 볼 수 있다. 만약 글씨가 작아 크게 보고자 할경우 그림을 클릭하면 된다.)

여기까지가 마우스를 사용한 과정이다.

이것을 SQL 창에서 실행하면 아래와 같은 명령어를 사용하면 위 그림과 같은 결과를 갖는다.

Select * From [sys].[schemas] 
Where [principal_id]=USER_ID('isoltek');

Alter Authorization On Schema::isoltek To dbo;

이렇게 두줄이면 되는 것을 마우스로 하려면 10개 정도의 과정을 수행해야 한다.

사람마다 어떤 것이 편할지는 모른다. 각자 취향이 있으므로 자신에게 편한 방법으로 수행하면 된다.

이렇게 하고난 후 삭제를 하면 "데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다."라는 에러 없이 삭제할 수 있다.

참고 : https://m.blog.naver.com/reinstate10/220588912597

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