SQL Server master 데이터베이스 정리

 

·       Version : SQL Server

 

SQL Server에서 master 데이터베이스의 역할은 시스템에 대한 모든 정보를 기록한다. 여기에는 로그인 계정, 끝점, 연결된 서버 시스템 구성설정과 같은 인스턴스 차원의 메타 데이터가 포함된다.

·       master database : https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-2017

 

우리는 가끔 의도하지 않게 master 데이터베이스에 개체를 생성하는 경우가 있다. 대부분 개체를 생성할 USE 문을 생략했을 수도 있고 많은 작업창을 띄어 놓고 사용하다가 실수할 수도 있다. 이번 아티클에서는 master 데이터베이스를 정리하는 방법에 대해서 알아본다. master 데이터베이스는 시스템의 설정을 저장하고 있으므로 해당 작업시 주의가 필요 하다.

 

[master에서 삭제하지 않을 목록]

아래 표에 정으되어 있는 오브젝트 유형은 시스템과 관련있기 때문에 삭제 목록에서 제외한다.

ET

External table

IT

Internal table

PC

Assembly (CLR) stored procedure

PG

Plan guide

RF

Replication-filter-procedure

S

System table

SQ

Service queue

TA

Assembly (CLR) DML Trigger

X

Extended procedure

-

CLR User-Defined Data Type (UDDT)

 

사용자는 자신만의 모듈을 생성하여 시스템 객체로 표시할 수도 있다. 경우 시스템 저장프로시저와 구별 있는 확실한 방법이 없기 때문에 주관적으로 식별해야 한다.

 

[master 에서 삭제할 목록]

오브젝트를 삭제하기 위해서는 약간의 순서가 필요하다. 예를 들어 테이블을 삭제하려면 외부키나 뷰를 제거해야하며, 테이블 함수를 사용하는 테이블을 삭제 때까지 파티션 함수를 제거할 없다. 또한 순환 참조가 있을 있으므로 많은 경우 스크립트를 여러번 실행해야 수도 있다.

 

아래 표시된 유형은 객체를 삭제하면 자동으로 삭제되기 때문에 크게 걱정할 필요가 없다.

C

Check constraint

D

Default constraint

PK

Primary key constraint

TR

DML trigger

UQ

Unique constraint

 

나머지는 아래 순서대로 삭제해야한다.

1

F

Foreign key constraint

Need to be dropped before tables, as described below.

2

V

View

Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.

FN

SQL scalar function

IF

SQL inline table-valued function

TF

SQL table-valued-function

3

P

Stored procedure

Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).

4

U

Table (user-defined)

Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.

5

TT

Table type

Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.

Alias types

 

6

SO

Sequence object

Dropped after tables/modules because they can't be dropped if any table or module references them.

R

Rule (old-style, stand-alone)

D

Old-style CREATE DEFAULT

Partition functions

 

7

Partition schemes

 

Can't be dropped until after partition functions.

8

SN

Synonym

I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).

9

Schemas

 

I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.

10

Roles and Users

 

For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas. 

(For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.) 

For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.

 

[기본 접근]

순환 참조는 여러 개체가 서로를 참조하므로 개체를 삭제하는 올바른 순서를 결정하기가 복잡하다. 그래서 스크립트가 실패하더라도 다시 실행하여 스크립트가 성공할때 까지 반복해서 실행 있는 스크립트로 작성해야 한다. 예를 들어 개의  SCHEMABINDING 뷰를 사용하는 경우 개의 뷰를 모두 삭제해야 테이블이 삭제된다.   순서가 명확하다면 순서대로 하면 되지만 뷰가 여러개 이면서 서로 뷰를 참조하는 경우 순서를 예측하기 어렵기 때문에 삭제 명령을 반복해서 실행한다.

try { drop view 1; go; drop view 2; }

 

 

[삭제 스크립트 (순서대로 실행)]

Foreign keys

-- script to drop Foreign Keys

USE [master];

GO

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER TABLE ' + objectname + N'

    DROP CONSTRAINT ' + fkname + N';

END TRY

BEGIN CATCH

  SELECT N''FK ' + fkname + N' failed. Run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT fkname = QUOTENAME(fk.[name]),

    objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])

  FROM sys.foreign_keys AS fk

  INNER JOIN sys.objects AS t

  ON fk.parent_object_id = t.[object_id]

  INNER JOIN sys.schemas AS s

  ON t.[schema_id] = s.[schema_id]

) AS src;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

View and functions

-- script to drop views

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP VIEW ' + objectname + N';

END TRY

BEGIN CATCH

  SELECT N''View ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])

  FROM sys.views AS v

  INNER JOIN sys.schemas AS s

  ON v.[schema_id] = s.[schema_id]

  WHERE v.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND o.[type] IN ('FN','IF','TF')

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Stored Procedures

-- script to drop procedures

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Procedure ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.[type] = 'P'

  AND o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Tables

-- script to drop user tables

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Table ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.tables AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Table Types

-- script to drop table types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_table_type = 1

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Alias Types

-- script to drop alias types

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Type ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.types AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.user_type_id > 256

  AND o.is_table_type = 0

  AND o.is_assembly_type = 0  -- not a CLR UDT

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Sequences

-- script to drop sequences

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Sequence ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.sequences AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Old-style CREATE RULE

-- script to drop old-style Rules

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Rule ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'R'

  AND parent_object_id = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Old-style CREATE DEFAULT

-- script to drop old-style Defaults

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Default ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.objects AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

  WHERE o.is_ms_shipped = 0

  AND [type] = 'D'

  AND parent_object_id = 0

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Partition functions

-- script to drop partition functions

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition function ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_functions

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Partition schemes

-- script to drop partition schemes

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME([name])

  FROM sys.partition_schemes

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Synonyms

-- script to drop synonyms

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Synonym ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

  FROM sys.synonyms AS o

  INNER JOIN sys.schemas AS s

  ON o.[schema_id] = s.[schema_id]

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Schemas

-- script to drop schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';

END TRY

BEGIN CATCH

  SELECT N''Schema ' + objectname + N' failed - run the script again.'',

    ERROR_MESSAGE();

END CATCH

' FROM

(

  SELECT QUOTENAME(s.[name])

  FROM sys.schemas AS s

  WHERE [schema_id] BETWEEN 5 AND 16383

) AS src(objectname);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to re-route default schemas

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;

 END TRY

 BEGIN CATCH

  SELECT N''User ' + p.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS p

WHERE p.default_schema_name IS NOT NULL

AND NOT EXISTS

(

  SELECT 1

  FROM sys.schemas AS s

  WHERE name = p.default_schema_name

);

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

Roles and Users

-- script to change ownership of roles to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

INNER JOIN sys.database_principals AS u

ON r.owning_principal_id = u.principal_id

WHERE r.[type] = 'R'

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!

AND r.is_fixed_role = 0;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to change ownership of schemas to dbo

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;

 END TRY

 BEGIN CATCH

  SELECT N''Schema ' + name + N' failed - run the script again.'',

    ERROR_MESSAGE(); END CATCH

'

FROM sys.schemas

WHERE [schema_id] BETWEEN 5 AND 16383

AND principal_id BETWEEN 5 AND 16383;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to remove members from roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

' + CASE WHEN CONVERT(tinyint,

  PARSENAME(CONVERT(nvarchar(128),

  SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+

 THEN

  N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER '

   + QUOTENAME(m.name) + N';'

 ELSE

  N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name

    + ''', @membername = N''' + m.name + N''';'

 END + N'

 END TRY

 BEGIN CATCH

   SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_role_members AS rm

INNER JOIN sys.database_principals AS r

ON rm.role_principal_id = r.principal_id

AND r.is_fixed_role = 0

INNER JOIN sys.database_principals AS m

ON rm.member_principal_id = m.principal_id;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop roles

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP ROLE ' + QUOTENAME(r.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''Role ' + r.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS r

WHERE r.[type] = 'R'

AND r.name <> N'public'

AND r.is_fixed_role = 0;

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

-- script to drop users

USE [master];

GO

DECLARE @sql nvarchar(max) = N'';

 

SELECT @sql += N'BEGIN TRY

  DROP USER ' + QUOTENAME(u.name) + N';

 END TRY

 BEGIN CATCH

  SELECT N''User ' + u.name + N' failed - run the script again.'',

     ERROR_MESSAGE();

 END CATCH

'

FROM sys.database_principals AS u

WHERE u.[type] IN ('U','S')

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##';

 

SELECT @sql;

--EXEC sys.sp_executesql @sql;

 

 

지금까지  master 데이터베이스를 정리하는 작업 순서, 스크립트 생성에 대해서 알아보았다. 객체를 삭제하는 명령은 자동화 있지만 모든것은 완벽히 분석할 수는 없다. (사용자 모듈을 시스템 모듈로 생성한 경우 ) 스크립트를 사용하더라도 생성된 스크립트를 반드시 DBA 검토를 해야한다. master 데이터베이스를 정리하는것은 위험한 작업이므로 반드시 많은 테스트와 검증이 필요하다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/4530/cleaning-up-the-sql-server-master-database/

 

 

2019-04-18 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, SSMS, mssql, DBA, master database, sys.database_principals, sys.database_role_members, sys.foreign_keys, sys.objects, sys.partition_functions, sys.partition_schemes, sys.procedures, sys.schemas, sys.sequences,

SQL Server 프로토콜과 SQLCMD 사용한 연결

 

·       Version : SQL Server

 

SQL Server 연결하는 것은 이미 많은 문서를 통해서 알려져 있으며 GUI 제공하는 툴들이 많아서 어렵지 않게 느껴질 수가 있다. 하지만 상황에 따라 복잡해 있다. 만약 TCP 포트에서 수신대기를 하지 않는 SQL Server 인스턴스에는 어떻게 연결할까? 호스트 컴퓨터가 서버  DNS 확인할 없는 경우 어떻게  SQL Server 연결할까? 명명된 인스턴스는 무엇일까? 이번 포트스에서 SQLCMD 사용하여 다양한 프로토콜의 의미와 사용법을 알아 본다.

 

SQL Server 관리하는 다양한 툴들이 있다. 특히SSMS(SQL Server Management Studio) Microsoft에서 공식으로 제공하는 SQL Server관리툴이며GUI 제공한다. 그리고 SQLCMD라는 CLI 있다. SQLCMD 경우 명령줄로 이루어져 있다. SQL Server Linux 지원하면서 우리는 GUI 뿐만아니라 환경에도 익숙해져야 하기 때문에 SQLCMD 대해 다루어 본다.

 

SSMS 사용하여 SQL Server 인스턴스에 연결할  특정 프로토콜을 사용하기 위해서 Connection Properties 탭에서 프로토콜을 선택할 있다.

 

대부분 SQL Server 연결할 , 프로토콜을 지정하지 않으면 TCP/IP 사용한다고 생각할 있다. 하지만 프로토콜을 지정하지 않으면 TCP/IP 사용한다는 보장을 없다. SSMS SQLCMD에는 SQL Server 구성관리자의 프로토콜에 따라 순서가 결정된다.  아래 그림은 SQL Server 구성관리자의 프로토콜 구성이다. 구성관리자에는 32bit, 64bit  가지 클라이언트 구성이 있다. SSIS 사용하는 경우 32bit 커넥터를 사용할 있기 때문에 중요하다. 패키자가 64bit 버전에서 실행되고 64bit 설정을 하지 않을 경우 패키지가 실패한다.

 

[SQLCMD 사용한 SQL Server TCP/IP 연결]

TCP/IP 프로토콜을 사용하여 SQL Server 인스턴스에 연결할 , 인스턴스가 기본 포트가 아닌 경우 서버 IP 주소, 또는 호스트 이름과 포트가 필요하다. 아래 스크립트는 TCP/IP 사용하여 연결하는 일반적인 구문이다.

sqlcmd -S tcp:<computer name>,<port number>

 

IP 주소를 입력하면 TCP/IP 프로토콜을 사용할 것이라는 보장을 없다. TCP/IP 보장하기 위해서 tcp:라는 접두어를 사용하여 연결한다. 예를 들어 Windows 인증을 사용하는 SQL Server 기본 인스턴스를 사용하여 SQL-A라는 서버에 TCP/IP 프로토콜을 사용하여 연결하려는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A

sqlcmd –S tcp:SQL-A –U sa –P Pa$$w0rd

 

IP 사용할 경우 아래 구문을 이용한다.

sqlcmd –S tcp:10.10.10.10

sqlcmd –S tcp:10.10.10.10 –U sa –P Pa$$w0rd

 

명명된 인스턴스를  사용하는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A\TEST

sqlcmd –S tcp:SQL-A\TEST –U sa –P Pa$$w0rd

 

포트를 지정해야 경우 콤마(,) 구분하며 포트를 지정해야 한다. 예를 들어 기본 인스턴스가 1433 포트를 사용하고 TEST 인스턴스가 51613포트를 사용하는 경우 연결 문자열을 아래와 같다.

sqlcmd –S tcp:SQL-A,1433

sqlcmd –S tcp:SQL-A,1433 –U sa –P Pa$$w0rd

 

sqlcmd –S tcp:10.10.10.10,1433

sqlcmd –S tcp:10.10.10.10,1433 –U sa –P Pa$$w0rd

 

sqlcmd –S tcp:SQL-A,51613

sqlcmd –S tcp:SQL-A,51613 –U sa –P Pa$$w0rd

 

sqlcmd –S tcp:10.10.10.10,51613

sqlcmd –S tcp:10.10.10.10,51613 –U sa –P Pa$$w0rd

 

 

[SQLCMD SQL Server 명명된 파이트 연결]

명명된 파이프는 서버와 클라이언트 간의 통신을 위해 명명된, 단방향, 또는 이중 파이프이다. 내부적으로 명명된 파이프의 모든 인스턴스는 동일한 파이프 이름을 갖지만 메시지 기반 통신과 클라이언트 가장을 허용하는 자체 버퍼를 유지한다. 명명된 파이프는 프로세스간 통신(IPC) 의존한다. 명명된 파이프를 사용하여 로컬 인스턴스에 연결하면 해당 파이프가 커널 모드에서 로컬 프로시저 호출(LPC) 실행된다는 점에 유의한다. 일반적으로 프로토콜은 원격 SQL Server 인스턴스에 연결할때 TCP/IP 사용하는것이 바람직하기 때문에 사용되지 않는다. 반면 로컬 인스턴스에 연결할 공유 메모리는 종종 선택되어 사용된다. 아래는 기본 인스턴스의 명명된 파이트 연결구문이다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\sql\query

 

예를 들어 서버 MYSERVER 기본 인스턴스에 대한 명명된 파이프는 다음과 같다.

\\ MYSERVER \ pipe \ sql \ query

 

명명된 인스턴스의 구문은 다음과 같다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\MSSQL$[SQL Server Instance Name]\sql\query

 

예를 들어 서버 SQL-A에서  SQL Server 인스턴스 TEST 명명된 파이프를 사용하려는 경우 구문은 아래와 같다.

\\SQL-A\pipe\MSSQL$TEST\sql\query

 

명명된 파이프 프로토콜을 사용하여 SQL Server 인스턴스에 연결하는 것은 TCP/IP 사용하는것과 크게 다르지 않다. 연결문자열에np: 접두어를 사용한다. 아래 구문은 Windows SQL Server 인증을 사용하여 SQL Server 기본 인스턴스와 명명된 인스턴스에 연결한다.

sqlcmd –S np:\\SQL-A\pipe\sql\query

sqlcmd –S np:\\SQL-A\pipe\sql\query –U sa –P Pa$$w0rd

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query –U sa –P Pa$$w0rd

 

 

[SQLCMD 사용한  SQL Server 공유 메모리 연결]

프로토콜은 클라이언트 연결이 로컬 서버에서 실행될 때만 사용될 있다. 기본적으로 커널 모드에서 실행되는 로컬 프로시저 호출(LPC)이다. MDAC 2.8 이하를 사용하는 경우 공유 메모리 프로토콜을 사용할 없다. 경우 sqlcmd 자동으로 명명된 파이프로 전환한다. 공유 메모리를 사용하는 접두사는 lpc: 이다.

sqlcmd –S lpc:SQL-A

sqlcmd –S lpc:SQL-A –U sa –P Pa$$w0rd

sqlcmd –S lpc:SQL-A\TEST

sqlcmd –S lpc:SQL-A\TEST –U sa –P Pa$$w0rd

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5133/different-ways-to-connect-to-sql-server-using-sqlcmd/

 

 

2019-04-17 / Sungwook Kang / http://sungwookkang.com

 

SQL Server, SSMS, sqlcmd, named pipe, sql server protocol, sql connection, mssql

인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

 

·         Version : SQL Server

 

SQL Server에서 인덱스 재구성 통계 업데이트 작업을 일반적으로 DML(SELECT, INSERT, UPDATE, DELETE)문은 차단하지 않는 것으로 알고 있다. 하지만 인덱스 재구성할 SELECT 문에서 차단이 발생하는 경우가 있다. 아래 실습을 통해서 어떻게 차단이 발생하는지 알아본다.

 

실습을 진행하기 위해 간단한 시나리오를 만든다. 해당 시나리오는 SQL Server Adventurework2014 데이터베이스를 사용하며 데이터 집합을 만들기 위해 아래 링크의 스크립트를 적용하였다.

·         Enlarging the AdventureWorks Sample Databases : https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

 

차단이 발생하는것을 재현하기 위해 ALTER INDE…REORGANIZE 실행하고 UPDATE 구문 SELECT 구문을 실행 한다.

세션 1에서 아래 스크립트를 실행 한다.

ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]

ON [Sales].[SalesOrderDetailEnlarged] REORGANIZE;

 

 

세션2에서 아래 스크립트를 실행한다.

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged] WITH FULLSCAN, ALL;

 

sp_who2 실행하고 프로세스가 모두 실행중인지 확인한다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

59

RUNNABLE

.

AdventureWorks2014

UPDATE STATISTIC

 

 

세션3에서 아래 스크립트를 실행한다.

SELECT *

FROM [Sales].[SalesOrderDetailEnlarged]

WHERE [SalesOrderId]=1302257;

 

sp_who2 실행하면 BlkBy 컬럼 정보를 통해서 블럭킹이 발생한 것을 확인할 있다.

SPID

Status

BlkBy

DBName

Command

57

RUNNABLE

.

AdventureWorks2014

DBCC

58

SUSPENDED

59

dventureWorks2014

SELECT

59

RUNNABLE

57

AdventureWorks2014

UPDATE STATISTIC

 

지금까지는 매우 간단한 시나리오였으며 SELECT UPDATE STATISTICS 의해 차단되고 UPDATE STATISTICS INDEX REORG(DBCC) 의해 차단되었음을 확인할 있다. 실제 서비스에서 sp_who2 실행하면 블로킹 체인의 SPID 한번에 확인하기 어려울 있으므로 아래 스크립트를 사용하면 전체 블럭킹 트리가 생성되고 체인에 포함된 SPID 확인할 있다.

(https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/ )

SET NOCOUNT ON

GO

 

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T

FROM sys.sysprocesses R

CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

GO

 

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS (SELECT

       SPID,

       BLOCKED,

       CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

       BATCH

   FROM #T R

   WHERE (BLOCKED = 0 OR BLOCKED = SPID)

   AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

   UNION ALL

   SELECT

      R.SPID,

      R.BLOCKED,

      CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

      R.BATCH

   FROM #T AS R

   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID

   WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

   )

SELECT

   N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

   CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END

   + CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

GO

 

DROP TABLE #T

GO

 

 

아래 출력을 보면 ALTER INDEX 체인의 머리 부분에 있고 UPDATE STATISTICS 차단되어 있어 간단한 SELECT문을 차단하고 있음을 확인할 있다.

BLOCKING_TREE

HEAD - 57 alter index [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]...

| |------ 59 UPDATE STATISTICS Sales.SalesOrderDetailEnlarged WITH FULLSCAN, ALL

| | |------ 58 SELECT * FROM [Sales].[SalesOrderDetailEnlarged] WHERE [SalesOrderId]=...

 

아래 스크립트를 사용하면 명령문에서 획득한 잠금을 보다 깊게 어떤 잠금이 잠금 경합을 일으키는지 확인할 있다. WHERE절에 SPID 수정해서 사용한다.

SELECT

  tl.request_session_id as spid,tl.resource_type,

  tl.resource_subtype,

  CASE

     WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)

     ELSE ''

  END AS object,

  tl.resource_description,

  request_mode,

  request_type,

  request_status,

  wt.blocking_session_id as blocking_spid

FROM sys.dm_tran_locks tl

LEFT JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

WHERE tl.request_session_id in (57,58,59);

 

 

스크립트 실행결과에서 STATS 자원의 잠금이 차단을 야기하는 것을 있다. ALTER INDEX 보유한 Sch-S(스키마 안정성) 잠금은 UPDATE STATISTICS 획득하려고 시도하는 Sch-M(스키마 수정) 잠금을 차단한다. 이것은 SELECT 쿼리가 획득하려고 하는 Sch-S 잠금을 막는것이다.

AUTO_UPDATE_STATISTICS 활성화 되어 있고 AUTO_UPATE_STATISTICS_ASYNC 비활성화된 경우 조회를 실행하기 전에 통계가 갱신될 까지 SELECT 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할 있다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/

 

 

2019-01-22 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, index reorganize, update statistics, 인덱스 재구성, 통계 업데이트, mssql, DBA

  1. 카카짱 2019.04.03 12:41

    혹시 SELECT 에서 NOLOCK 을 줄 경우에도 잠길까요?

    • Sung Wook Kang 2019.04.03 23:15 신고

      네, 드물지만 발생할 수 있습니다. NOLOCK 을 하더라도 일정이상의 락 리소스 오버헤스가 발생하면 락 에스컬레이션이 발생하기 때문에 가능성이 있습니다.

SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상

 

·         Version : SQL Server 2017 later, Azure SQL Database

 

SQL Server 2017 Azure SQL Database 런타임 환경에 최적화된 쿼리 처리 개선 기능을 도입했다. 이러한 개선 사항에는 Batch mode Adaptive Joins, Batch mode memory grant feedback, Interleaved execution for multi-statement table valued functions 포함된다.

·         Batch mode Adaptive Joins : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-adaptive-joins

·         Batch mode memory grant feedback  : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-memory-grant-feedback

·         Interleaved execution for multi-statement table valued functions : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#interleaved-execution-for-multi-statement-table-valued-functions

 

SQL Server 2019에서는 Intelligent query processing(QP) 제품군에서 가지 새로운 기능으로 쿼리 처리 기능을 더욱 확장하고 있다.

·         Intelligent query processing in SQL databases : https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017

 

이번 포스트에서는 SQL Server 2019 CTP 2.1에서 사용할 있는QP 기능 하나인 Scalar T-SQL UDF  inline 대해서 살펴본다.

 

T-SQL UDF SQL 쿼리에서 코드 재사용 모듈화를 구현한다. 복잡한 비즈니스 규칙과 같은 일부 계산은 명령형 UDF 형식으로 표현하기가 쉽다. UDF 복잡한 SQL 조회를 작성하는 전문 지식 없이 복잡한 논리를 해결하는데 도움이 된다. 하지만 이러한 장점에도 불구하고 열악한 성능문제 때문에 최대한 사용을 자제하거나 금지한다.

Scala UDF inline 기능의 목표는 UDF 실행의 주요 병목인 Scala UDF 호출하는 쿼리의 성능을 향상 시키는 것이다.  Scala UDF 느렸던 이유는 Scala UDF 도입되었을 , 변수 할당, IF-ELSE 분기, 루프 등과 같은 친숙한 구문을 사용하여 사용자가 비즈니스 로직을 표현하였다. 아래 스크립트는 @ckey값이 주어지면 스칼라 UDF 사용하여 고객이 지정한 모든 주문의 가격을 계산한 다음 IF-ELSE 논리를 사용하여 가격을 기준으로 카테고리 값을 반환한다.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)

RETURNS CHAR(10) AS

BEGIN

       DECLARE @total_price DECIMAL(18,2);

       DECLARE @category CHAR(10);

     

       SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey;

  

       IF @total_price < 500000

              SET @category = 'REGULAR';

       ELSE IF @total_price < 1000000

              SET @category = 'GOLD';

       ELSE

              SET @category = 'PLATINUM';

       RETURN @category;

END

 

UDF 여러 조회에서 사용할 있으며 임계 값을 갱신하거나 카테고리를 추가해야하는 경우 UDF에서만 변경해야한다. 아래 스크립트는 위에서 만든 UDF 호출하는 간단한 쿼리이다.

-- Q1:

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

 

SQL Server 2017(호환성 수준 140 이전 버전) 버전에서는 쿼리의 실행 계획은 아래와 같다.


SQL Server CUSTOMER 테이블의 모든 튜플에 대해 UDF 호출하고 결과를 출력한다. 실행계획  매우 비효율적이다. 이러한 쿼리는 다음과 같은 이유로 성능에 좋지 않다.

·         반복 호출 : UDF 대상 튜플 반복적으로 호출된다. 이로 인해 함수 호출로 인한 반복된 컨텍스트 전환의 추가 비용이 발생한다. 특히 본문에서 SQL 조회를 실행하는 UDF 심각한 영향을 준다.

·         비용 증가 : 최적화 중에는 관계 연산자만 비용이 청구되고 스칼라 연산자는 계산되지 않는다. Scala UDF 도입되기 전에 다른 스칼라 연산자는 일반적으로 저렴했으며 비용이 필요하지 않았다. 스칼라 작업에 추가된 작은 CPU 비용으로 충분했다.

·         Interpreted 실행 : UDF 명령문 별로 실행되고 명령문의 일괄 처리로 평가 된다. 명령문 자체는 컴파일 되고 컴파일된 계획은 캐시 된다. 캐싱 전략은 재컴파일이 발생할때 까지 시간을 절약하지만 문은 독립적으로 실행된다. 교차 명령문 최적화는 수행되지 않는다.

·         Serial 실행 : SQL Server UDF 호출하는 쿼리에서 쿼리 병렬 처리를 사용하지 않는다.

 

새로운 Scalar UDF inline 기능으로 변경된 사항은 스칼라 UDF 표현식이나 서브쿼리로 변환되어 UDF 연산자의 호출 조회에서 대체된다. 그러면 이러한 표현식과 서브쿼리가 최적화 된다. 결과적으로 쿼리 계획에는 이상 사용자 정의 함수 연산자가 없지만 또는 인라인 TVF 같은 계획에서 효과가 관찰된다. 이를 이해하기 위해서 먼저 아래 예제를 살펴 본다.

-- Q2 (Query with no UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

쿼리는 광고 항목의 할인 가격 합계를 계산하고 출하 날짜 배송 우선 순위별로 그룹화 하여 결과를 표시한다. 표현식 L_EXTENDEDPRICE * (1 - L_DISCOUNT) 해당 광고 항목의 할인 가격에 대한 수식이다. 할인된 가격을 계산해야하는 곳이면 어디서든 사용할 있도록 계산 함수를 만드는 것이 합리적이다.

-- Scalar UDF to encapsulate the computation of discounted price

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))

RETURNS DECIMAL (12,2) AS

BEGIN

       RETURN @price * (1 - @discount);

END

 

이제 다음과 같이 쿼리 Q2 수정하여 UDF 사용할 있다.

-- Q3 (Query with UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

 

아래 표는 3개의 쿼리를 실행한 결과이다. 앞에서 설명한 이유 때문에 Q3 Q2 비해 성능이 좋지 않다. 이제 Scala UDF inline 통해 SQL Server 스칼라 표현식을 쿼리로 직접 대체하여 UDF 성능의 한계를 극복한다.

[2CPU (12 core), 96GB RAM, SSD, TPC-H 10GB CCI 데이터 사용]

Query

Q2(UDF 없음)

인라인 업이 Q3

인라인이 포함된 Q3

Execution Time

1.6 second

29 minute 11 second

1.6 second

 

수있듯이 인라인 없이 Q3 Q2 비해 속도가 매우 느리다. 그러나 Scala UDF inline 사용하면 Q3 성능은 거의 오버헤드 없이 거의 Q2 동일하다. 쿼리성능을 저하시키지 않으면서 UDF 모든 이점을 누릴 있다. 또한 쿼리나 UDF 수정이 없었음을 확인할 있다. Scala UDF inline 통해 SQL Server 다중문 UDF 인라인 있다. 위에서 주어진 dbo.customer_category 함수와 Q1함수를 살펴보고 이것이 어떻게 동작하는지 살펴보자. 쿼리 Q1 경우 UDF 인라인된 쿼리 계획은 아래와 같다.


위의 계획에서 얻은 가지 주요 관찰 내용은 아래와 같다.

1.       SQL Server CUSTOMER ORDERS 사이의 암시적 조인을 유추했으며 Join 연산자를 통해 이를 명시적으로 만들었다.

2.       SQL Server ORDERS 대한 암시적 GROUP BY O_CUSTKEY 유추했으며 이를 구현하기 위해 Index Spool Stream Aggregate 사용했다.

3.       SQL Server 모든 연산자에서 병렬처리를 사용한다.

 

UDF 논리 복잡성에 따라 결과 쿼리 계획이 커지고 복잡해 수도 있다. 있듯이 UDF 내부의 작업은 이제 이상 블랙 박스가 아니므로 쿼리 최적화 프로그램은 비용을 절감하고 이러한 작업을 최적화 있다. 또한 UDF 이상 계획에 없으므로 반복 UDF 호출은 함수 호출 오버헤드를 회피하는 계획으로 대체된다.

 

Scala UDF inline 장점은 성능 오버헤드에 대한 걱정없이 사용자가 Scala UDF 사용할 있다.  이로써 사용자는 모듈화되고 재사용가능한 응용프로그램을 구축 있다. UDF 사용한 쿼리에 대해 집합 지향, 병렬 계획을 수행할 있을뿐만 아니라 기능은 다른 장점이 있다. Scala UDF 이상 interpreted 되지 않으므로 ( : 명령문마다 실행됨) 데드코드 제거, 상수 폴딩 상수 전파와 같은 최적화가 가능하다. 이러한 기술은 UDF 따라 간단하고 효율적인 쿼리 계획으로 이어질 있다.

 

Scala UDF inline 가능한 범위는 아래 링크를 참고 한다.

·         Inlineable Scalar UDFs requirements : https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sqlallproducts-allversions#inlineable-scalar-udfs-requirements

 

Scala UDF inline 가능한지 여부는 sys.sql_modules 카탈로그 뷰에서 is_inlineable라는 속성이 있다. 1 인라인이 가능함을 나타내고 0 그렇지 않다. 속성값 정의에 의해 인라인 가능하기 때문에 인라인 테이블 반환 함수의 값은 1이다.

 

Scala UDF inline UDF 실행이 쿼리의 주요 병목일때 가장 유용하다. 병목 현상이 다른곳에 있다면 이점이 없을 수도 있다. 인라인은 CREATE /ALTER FUNCTION문에서 INLINE = OFF 옵션을 사용하여 UDF 대해 해제할 있다. Scala UDF inline 테스트하려면 기본적으로 데이터베이스 호환성 150 사용해야한다. SQL Server 2019 CTP 2.1에서 Scala UDF inline 사용하려면 쿼리를 실행할 연결된 데이터베이스에 대해 데이터베이스 호환성 수준 150 활성화 한다.

USE [master];

GO

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 150;

GO

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/introducing-scalar-udf-inlining/

 

 

2018-11-12 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, UDF, Scala UDF Inline, 스칼라 UDF 인라인




+ Recent posts