'분류 전체보기' 카테고리의 글 목록 (6 Page)

분류 전체보기

SQL Server 접속 네트워크 프로토콜 2020.09.24
Find and Kill all the Blocked Process/Query 2020.09.22
오래된 커서 2020.09.11
NUMA를 이용한 포트별로 물리적 CPU 분산 2020.09.10
SQL Server 인덱스 활성 / 비활성 하기 2020.08.31
쿼럼 리소스 이동 2020.08.29
테이블 명세서 2020.08.29
sp_who3 2020.08.29
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) 2020.08.29
악성 쿼리 찾아내기 - ReadTrace 2020.08.29

SQL Server 접속 네트워크 프로토콜

ㅎ2 2020. 9. 24. 09:27

2020. 9. 24. 09:27

Client 에서 SQL Server로 연결하기 위해서는 어떠한 프로토콜들을 사용할까요?

많은 분들은 IP를 사용하죠. (IP 또는 컴퓨터이름)

그렇다면, 접속 시 사용하는 프로토콜은 어떠한 것들이 있을까요?

공유 메모리, TCP/IP, 명명된 파이프, VIA 가 그것들입니다.

해당 프로토콜의 사용유무는 SQL Server 구성관리자(SQL Server Configuration Manager)에서 확인과 설정이 가능합니다.

1. 공유 메모리(Shared Memory)

A. 서버에서 직접 접속할 때 사용할 때 쓸 수 있습니다. 즉 로컬컴퓨터가 서버인 경우에만 됩니다.

B. 접속형태는 <servername>[\instancename] 입니다.

C. “.”이나 “(local)” 또는 “localhost”와 같은 형태로 사용이 가능합니다.

D. 동일한 컴퓨터인 경우 가장 우수한 성능을 보입니다.

E. 포트를 지정할 수 없습니다.

F. 접속상태를 확인하면 다음과 같습니다.

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

2. TCP/IP

A. 가장 많이 사용하는 형태입니다.

B. 서버주소를 IP 주소 또는 컴퓨터 이름을 사용합니다.

C. 기본 포트는 1433입니다.

D. TCP: <servername>[\instancename>],<port> 또는 TCP:<ipaddress>[\instancename],<port>와 같은 형태로 사용이 가능하며,

“TCP:” 는 생략할 수 있습니다. 또한, 기본 포트 1433을 사용할 때에는 포트번호를 생략할 수 있습니다.

E. 로컬컴퓨터라도 “127.0.0.1”과 같이 IP Address를 사용하면 공유메모리가 아닌 TCP/IP로 동작합니다.

F. 접속상태를 확인하면 다음과 같습니다.

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

3. 명명된 파이프(Named Pipe)

A. 간혹 사용하는 경우가 있지만 그다지 많이 사용하지 않는 방식입니다.

B. 구성관리자에 명시된 이름을 사용합니다.

C. 기본포트는 445 이며, 포트를 변경할 수 없습니다.

D. np:\\<ipaddress>\pipe\sql\query 또는 np:\\<computer_name>\pipe\sql\query 의 형태로 사용이 가능하며,
“np:”는 생략할 수 있습니다.

E. 위 예제는 제가 일부러 \\.\pipe\sql\query 에서 \\.\pipe\sql\query1 로 바꾼 것이며,

F. 구성관리자에서 다음과 같이 변경하였습니다.

G. 파이프명이 일치하지 않을 경우 다음과 같이 접속오류가 발생합니다.

H. 접속상태를 확인하면 다음과 같습니다.

SELECT net_transport

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

4. VIA(Virtual Interface Adapter)

A. VIA 프로토콜은 하드웨어와 함께 동작하기 때문에 거의 사용하지 않고 있습니다.

B. 또한, 다음 버전부터는 제거된다고 합니다.

참고 URL : http://msdn.microsoft.com/ko-kr/library/ms190611.aspx

출처: https://windtrap.tistory.com/75 [곰탱이의 개인블로그]

'Database > SQL Server' 카테고리의 다른 글

SQL Server 메모리 내 데이터베이스 개체의 내부 데이터 구조를 모니터링하는 방법 (0)	2020.09.24
SQL Server의 메모리 내 OLTP에 대한 간략한 개요 (0)	2020.09.24
NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10
SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
쿼럼 리소스 이동 (0)	2020.08.29

Find and Kill all the Blocked Process/Query

ㅎ2 2020. 9. 22. 15:12

2020. 9. 22. 15:12

–Find All the Blocked Processes

SELECT

spid,

status,

loginame=SUBSTRING(loginame,1,12),

hostname=SUBSTRING(hostname,1, 12),

blk = CONVERT(char(3), blocked),

dbname=SUBSTRING(DB_NAME(dbid),1, 10),

cmd,

waittype

FROM master.dbo.sysprocesses

WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–Kill all the Blocked Processes of a Database

DECLARE @DatabaseName nvarchar(50)

Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL = ”

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

and spid IN (SELECT blocked FROM master.dbo.sysprocesses)

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

Or You can use the following script

— Script to kill all blocked processes

declare @max_count int, @count int, @sqlstring varchar(100)

declare @spid_table table (spid int NOT NULL)

INSERT @spid_table

select spid

from master.dbo.sysprocesses

where spid in (select blocked from master.dbo.sysprocesses where blocked <> 0) and blocked = 0

select @max_count = MAX(spid) FROM @spid_table

select top 1 @count = spid from @spid_table

while @count <= @max_count

begin

select @sqlstring = ‘kill ‘ + CONVERT(varchar(4), @count)

exec(@sqlstring)

print @sqlstring

IF @count = @max_count

begin

break

end

ELSE

BEGIN

select top 1 @count = spid FROM @spid_table where spid > @count

end

Similarly DBA can kill all the Processes of a Database as

— Kill all the Processes of a Database

DECLARE @DatabaseName nvarchar(50)

–Set the Database Name

SET @DatabaseName = N’Datbase_Name’

Select the current Daatbase

SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)

SET @SQL = ”

SELECT @SQL = @SQL + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’

FROM MASTER..SysProcesses

WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

–You can see the kill Processes ID

–SELECT @SQL

–Kill the Processes

EXEC(@SQL)

—Identify the blocking query

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

-- Script to view all current processes / sessions on the server

select * from master.dbo.sysprocesses

'Database > Query' 카테고리의 다른 글

DELETE 복원 프로시저 (0)	2020.10.20
EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
오래된 커서 (0)	2020.09.11
테이블 명세서 (0)	2020.08.29
sp_who3 (0)	2020.08.29

오래된 커서

ㅎ2 2020. 9. 11. 11:19

2020. 9. 11. 11:19

SELECT creation_time, cursor_id, name, c.session_id, login_name   
FROM sys.dm_exec_cursors(0) AS c   
JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id   
WHERE DATEDIFF(HH, c.creation_time, GETDATE()) >= 1;  -- 시간
GO

'Database > Query' 카테고리의 다른 글

EXEC와 동적(adhoc) 쿼리 (0)	2020.10.05
Find and Kill all the Blocked Process/Query (0)	2020.09.22
테이블 명세서 (0)	2020.08.29
sp_who3 (0)	2020.08.29
특정 문자열 포함하는 프로시저 (0)	2020.08.28

NUMA를 이용한 포트별로 물리적 CPU 분산

ㅎ2 2020. 9. 10. 15:38

2020. 9. 10. 15:38

1. 물리적 CPU 리소스 분산이 필요한 경우

하나의 SQL Server에서 다른 타입의 쿼리가 수행되는 경우에

원활한 서비스를 위해 물리적인 리소스(CPU)를 분산 시켜 주어야 하는 이슈가 있을 수 있습니다.

예를 들어보면, 하나는 CPU자원을 많이 사용하지는 않지만, 많이 요청되는 OLTP성 쿼리,

다른 하나는 많이 수행되지는 않지만 CPU자원을 많이 사용하는 배치성 쿼리인 환경을 볼 수 있습니다.

이러한 경우에 배치성 쿼리로 인해 OLTP 쿼리 성능에 영향을 줄 수 있습니다.

이때 해결 할 수 있는 방법으로는 각각의 쿼리 타입마다 물리적인 리소스(CPU)를 할당하여
같은 다른 타입의 형태의 쿼리가 같은 CPU를 사용하지 못하게 한다면, 위와 같은 문제를 처리할 수 있습니다.

2. 해결할 수 있는 방법들

가장 먼저 떠오르는 방법은,
하나의 물리적인 머신에 두 개의 인스턴스를 올리고, affinity mask를 설정하는 것입니다.

또한 물리적인 메모리를 각각의 인스턴스에서 설정하여, 메모리 크기도 조절할 수 있는
장점이 있습니다. 하지만, 인스턴스가 다른 관계로 데이터를 동기화 해주어야 하는 부담이
존재하게 되고 전체적인 운영비용이 높아질 수 밖에 없습니다.

뭐~ 다른 방법으로 그냥 MAXDOP를 줘서 CPU사용을 제한 하는 것도 방법일 수 있지만,
MAXDOP의 경우 물리적인 CPU 지정 및 사용량을 지정을 할 수 없기에
배치가 수행되는 CPU에서 수행되는 OLTP성 쿼리는 여전히 문제가 될 수 있습니다.

음~ 그럼 SQL Server 2008의 리소스 관리자를 생각할 수 있습니다.

하지만 SQL Server 2008 Enterprise에서만 지원하는 제약이 있습니다.

3. 또 하나의 방법 NUMA

여기서 소개하려는 방법은 NUMA 를 이용한 방법 입니다.
상황에 따라 최선의 솔루션은 달라지기에 모든 상황에 대한 최선의 솔루션이라고는 볼 수 없습니다.
그리고 여기서는 NUMA를 소개하고자 하는 것이
아니기에 NUMA에 대한 소개는 건너뛰겠습니다.

요즘 판매되는 네할렘 기반의 프로세스는 메모리 컨트롤러가 CPU내부에 위치하고 있어
기본적으로 2CPU 이상인 경우 NUMA를 지원하고 있습니다.

만약 NUMA를 지원하지 않는 환경이라도 soft NUMA를 설정 할 수 있기에 크게 상관은 없습니다.

그럼 NUMA를 가지고 어떻게 할 수 있을까요?

BOL을 보면 NUMA 노드 별로 TCP/IP 포트를 설정할 수 있는 방법을 소개 하고 있습니다.
바로 이 설정으로 쿼리 타입별 CPU 리소스를 분리 할 수 있습니다.
만약 하드웨어 NUMA로 지원되는 노드가 아니라 더 세분하게 CPU 리소스를 분산하고자 한다면
soft NUMA를 추가적으로 설정해서 처리할 수 있습니다.

간단하게 예를 들면 1000포트는 0번 NUMA 노드, 2000번 포트는 1번 NUMA 노드,
3000번 포트는 모든 NUMA를 사용하게 설정 할 수 있습니다.

이렇게 설정하게 된 후 각각의 클라이언트에서 각 쿼리 타입별로 사용 포트를 변경해서

사용하면 CPU 리소스 간섭 없이 처리 할 수 있습니다.

간단한 구성 방법은 아래를 참고하시길 바랍니다.

[soft NUMA 설정 방법] http://msdn.microsoft.com/ko-kr/library/ms345357.aspx

Soft NUMA를 설정하거나, NUMA를 지원하는 장비라면 sp_readerrorlog에서 아래와 같은 메시지를 확인할 수 있습니다.

2009-08-28 15:10:23.940 서버 Node configuration: node 0: CPU mask: 0x000000aa Active CPU mask: 0x000000aa.
2009-08-28 15:10:23.940 서버 Node configuration: node 1: CPU mask: 0x00000055 Active CPU mask: 0x00000055.

[NUMA 노드에 TCP/IP 포트 매핑] http://msdn.microsoft.com/ko-kr/library/ms345346.aspx

TCP/IP의 포트를 NUMA노드에 맵핑 후 재시작 하면 SP_READERORLOG 에서는 아래와 같이 확인할 수 있습니다.

2009-08-28 15:10:24.860 서버          Server is listening on [ 'any' <ipv4> 3000].
2009-08-28 15:10:24.860 서버          SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000003.
2009-08-28 15:10:24.860 서버          Server is listening on [ 'any' <ipv4> 1000].
2009-08-28 15:10:24.860 서버          SQL Server Network Interfaces initialized listeners on node 0 of a multi-node (NUMA) server configuration with node affinity mask 0x00000001.
2009-08-28 15:10:24.860 서버          Server is listening on [ 'any' <ipv4> 2000].
2009-08-28 15:10:24.860 서버          SQL Server Network Interfaces initialized listeners on node 1 of a multi-node (NUMA) server configuration with node affinity mask 0x00000002.

각각의 포트별로 스트레스를 주고 작업관리자를 확인해 보면, NUMA 노드 만을 사용하는 것을 확인할 수 있습니다

1000번 포트 사용	2000번 포트 사용	3000번 포트 사용

출처: https://hyoksong.tistory.com/42 [Hyok Song]

'Database > SQL Server' 카테고리의 다른 글

SQL Server의 메모리 내 OLTP에 대한 간략한 개요 (0)	2020.09.24
SQL Server 접속 네트워크 프로토콜 (0)	2020.09.24
SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
쿼럼 리소스 이동 (0)	2020.08.29
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29

SQL Server 인덱스 활성 / 비활성 하기

ㅎ2 2020. 8. 31. 09:01

2020. 8. 31. 09:01

SQL Server 인덱스 활성 / 비활성 하기

Version : SQL Server 2005, 2008, 2008R2, 2012

데이터베이스를 운영하다 보면 수 많은 인덱스를 접하게 된다. 하지만 여기 있는 인덱스가 모두 사용 되는 것일까? 인덱스의 유용 여부를 파악하기란 쉽지 않다. 그렇다고 잘 쓰지 않을 것 같다고 생각되는 인덱스를 무심코 삭제 했다가는 정말 난감한 상황이 발생 한다. 만약 어떤 인덱스가 있을 때 이 인덱스의 유용 여부에 대해서 판단하고 싶을 때 어떻게 할까?

SQL Server 2005 이상 버전에서는 테이블 또는 뷰에 대해서 인덱스를 활성 / 비활성 할 수 있다. 인덱스 사용을 비활성화 하면 인덱스를 사용하지 않아 인덱스에 따른 영향을 평가 할 수 있다.

우선 현재 인덱스를 살펴 보자. 다음과 같이 클러스터드 인덱스가 생성되어 있으며 활성 상태인 것을 확인 할 수 있다.

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

[인덱스 비활성화 만들기]

T-SQL을 이용하여 비활성 상태로 변경하자.

ALTER INDEX CI_TBL_A_COL1 ON TBL_A DISABLE

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

SSMS에서 UI로도 가능 하다. 인덱스에서 마우스 오른쪽을 클릭하여 [사용 안 함]을 선택 한다.

또는 인덱스 속성 창에서 [옵션] – [인덱스 사용] 체크 박스를 해제 하면 비활성화 된다.

[인덱스 활성화 만들기]

인덱스를 활성화 하는 방법에는 어떤 방법이 있으까?

T-SQL을 이용하여 활성화 상태로 변경하자. 비활성 상태로 만들기 위해서 DISABLE 명령어를 사용하였다면 활성하는 REBUILD 명령어를 사용해야 한다.

ALTER INDEX CI_TBL_A_COL1 ON TBL_A REBUILD

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

또는 인덱스를 새로 만들어야 한다.

CREATE CLUSTERED INDEX CI_TBL_A_COL1 ON TBL_A (COL1)

WITH (DROP_EXISTING = ON)

SELECT

NAME AS INDEXNAME, TYPE_DESC, FILL_FACTOR,

CASE WHEN IS_DISABLED = 0 THEN 'ENABLED'

WHEN IS_DISABLED = 1 THEN 'DISABLED' END AS [INDEXUSAGE]

FROM SYS.INDEXES

WHERE OBJECT_ID = OBJECT_ID('TBL_A')

SSMS의 UI에서도 가능 하다. 인덱스에서 마우스 오른쪽을 클릭하여 [다시 작성]을 선택 한다.

또는 인덱스 속성 창에서 [옵션] – [인덱스 사용] 체크박스를 선택하면 인덱스가 활성화 된다.

'Database > SQL Server' 카테고리의 다른 글

SQL Server 접속 네트워크 프로토콜 (0)	2020.09.24
NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10
쿼럼 리소스 이동 (0)	2020.08.29
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29
악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29

쿼럼 리소스 이동

ㅎ2 2020. 8. 29. 18:28

2020. 8. 29. 18:28

클러스터 그룹 이름 확인

C:\> cluster group

통상적으로 영문일 경우 "Cluster Group" 한글일 경우 "클러스터 그룹" 으로 표시됨

쿼럼 리소스 이동

위 환경에서는 클러스터 그룹의 이름이 한글이므로 한글로 아래와 같이 입력

C:\>Cluster group "클러스터 그룹" /move:이동할서버(노드명)

* 클러스트 그룹이 영문일 경우 아래와 같이 이름만 변경

C:\>Cluster group "Cluster Group" /move:이동할서버(노드명)

Windows 2012에서의 쿼럼리소스 이동

Windows 2012 Failover cluster의 경우 Core 리소스 이동이라는 메뉴로

쿼럼리소스 및 디스크 이동을 클러스터 관리자(GUI)에서 할 수 있음

'Database > SQL Server' 카테고리의 다른 글

NUMA를 이용한 포트별로 물리적 CPU 분산 (0)	2020.09.10
SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29
악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29
Master Key (0)	2020.08.29

테이블 명세서

ㅎ2 2020. 8. 29. 18:28

2020. 8. 29. 18:28

/****** Object:  StoredProcedure [dbo].[sp_table_Spec]    Script Date: 04/25/2014 08:16:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCedure [dbo].[sp_table_Spec]
as
-- exec sp_table_Spec

Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000), @tablename varchar(4000)

create Table #Tables  (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000), [tablename] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))

print '
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/html; charset=ks_c_5601-1987">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 14">
<link rel=File-List href="통합%20문서3.files/filelist.xml">
<style id="Styles">
<!--table
	{mso-displayed-decimal-separator:"\.";
	mso-displayed-thousand-separator:"\,";}
.font712580
	{color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;}
.xl1520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:11.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl6320912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl6420912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6620912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6720912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6820912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl6920912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl7020912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:1.0pt solid windowtext;
	border-bottom:none;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7120912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:none;
	border-bottom:none;
	border-left:1.0pt solid windowtext;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7220912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl7320912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	background:white;
	mso-pattern:black none;
	white-space:normal;}
.xl7420912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:1.0pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:none;
	border-bottom:.5pt solid windowtext;
	border-left:.5pt solid windowtext;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7620912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:none;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7720912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7820912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:none;
	border-bottom:1.0pt solid windowtext;
	border-left:.5pt solid windowtext;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl7920912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:none;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl8020912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:none;
	mso-background-source:auto;
	mso-pattern:auto;
	white-space:nowrap;}
.xl8120912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:normal;}
.xl8220912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:normal;}
.xl8320912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:center;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:normal;}
.xl8420912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:.5pt solid windowtext;
	border-right:1.0pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:none;
	background:#D9D9D9;
	mso-pattern:black none;
	white-space:nowrap;}
.xl8520912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:left;
	vertical-align:middle;
	border-top:1.0pt solid windowtext;
	border-right:.5pt solid windowtext;
	border-bottom:.5pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:#DAEEF3;
	mso-pattern:black none;
	white-space:normal;}
.xl8620912
	{padding-top:1px;
	padding-right:1px;
	padding-left:1px;
	mso-ignore:padding;
	color:black;
	font-size:9.0pt;
	font-weight:700;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-number-format:General;
	text-align:general;
	vertical-align:middle;
	border-top:none;
	border-right:.5pt solid windowtext;
	border-bottom:1.0pt solid windowtext;
	border-left:1.0pt solid windowtext;
	background:#DAEEF3;
	mso-pattern:black none;
	white-space:normal;}
ruby
	{ruby-align:left;}
rt
	{color:windowtext;
	font-size:8.0pt;
	font-weight:400;
	font-style:normal;
	text-decoration:none;
	font-family:"맑은 고딕", monospace;
	mso-font-charset:129;
	mso-char-type:none;}
-->
</style>
</head>

<body>
<div id="div" align=center x:publishsource="Excel">
'


set nocount on




--create Table #Tables  (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000), [tablename] varchar(4000))

insert into #Tables (Object_id, Name, Type, [description],  [tablename])

Select o.object_id,  '[' + s.name + '].[' + o.name + ']', 
    case when type = 'V' then 'View' when type = 'U' then 'Table' end,  
    cast(p.value as varchar(4000)),
    CONVERT(varchar(4000),p.value)
    from sys.objects o 
       left outer join sys.schemas s on s.schema_id = o.schema_id 
       left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' and p.name = '테이블명'
--            where type in ('U', 'V') 
    where type in ('U')
--안쓰는 테이블 제외 
					and left(o.name, 2) in ('bi','co','ei','in','mk','mp','qc','sc')
--					and left(o.name, 2) in ('ac','fx')
--					and right(o.name, 5) not in ('excel','ackup') and right(o.name, 3) not in ('bak','web') 
--					and left(o.name, 2) not in ('tr','wy','as','ls','ds','dy','tb') and left(o.name, 5) not in ('cowrk','biacd')
    order by type, s.name, o.name
    
Set @maxi = @@rowcount


set @i = 1
While(@i <= @maxi)
begin
   --table header
   select @tablename = name, @description = [description] from #Tables where id = @i
      
   print '
<table border=0 cellpadding=0 cellspacing=0 width=838 style="border-collapse:
 collapse;table-layout:fixed;width:629pt">
 <col width=24 style="mso-width-source:userset;mso-width-alt:768;width:18pt">
 <col width=99 style="mso-width-source:userset;mso-width-alt:3168;width:74pt">
 <col width=194 style="mso-width-source:userset;mso-width-alt:6208;width:146pt">
 <col width=115 style="mso-width-source:userset;mso-width-alt:3680;width:86pt">
 <col width=118 style="mso-width-source:userset;mso-width-alt:3776;width:89pt">
 <col width=288 style="mso-width-source:userset;mso-width-alt:9216;width:216pt">
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 width=24 style="height:15.95pt;width:18pt"></td>
  <td class=xl6320912 width=99 style="width:74pt"></td>
  <td class=xl6320912 width=194 style="width:146pt"></td>
  <td class=xl6320912 width=115 style="width:86pt"></td>
  <td class=xl6320912 width=118 style="width:89pt"></td>
  <td class=xl6320912 width=288 style="width:216pt"></td>
 </tr>
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8520912 width=99 style="width:74pt">Table ID</td>
  <td colspan=4 class=xl7520912 style="border-right:1.0pt solid black;
  border-left:none">'+ isnull(@tablename, '') +'</td>
 </tr>
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8620912 width=99 style="width:74pt">Description</td>
  <td colspan=4 class=xl7820912 style="border-right:1.0pt solid black;
  border-left:none">' + isnull(@description, '') + '</td>
 </tr>
'
   --table columns
	truncate table #Columns 
   
  insert into #Columns  (Name, Type, Nullable, [description])
  --FOR 2005   
  Select c.name, 
           type_name(user_type_id) + (
           case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
              then '(' + cast(max_length as varchar) + ')' 
            when type_name(user_type_id) = 'decimal'  
                 then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar)   + ')' 
           else ''
           end            
           ), 
           case when is_nullable = 1 then 'Y' else 'N'  end,
           cast(p.value as varchar(4000))
  from sys.columns c
        inner join #Tables t on t.object_id = c.object_id
        left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id  = c.column_id and p.name = 'MS_Description' 
  where t.id = @i
  order by c.column_id
    
   Set @maxj =   @@rowcount
   set @j = 1
   
   
print '   
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl7120912>Table Columns</td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl7020912>　</td>
 </tr>
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8120912 width=99 style="width:74pt">Sr.</td>
  <td class=xl8220912 width=194 style="width:146pt">Name</td>
  <td class=xl8220912 width=115 style="width:86pt">Datatype</td>
  <td class=xl8220912 width=118 style="width:89pt">Nullable</td>
  <td class=xl8320912 width=288 style="width:216pt">Description</td>
 </tr>
'

   While(@j <= @maxj)
   begin
      select	@Output = '
				 <tr height=21 style="mso-height-source:userset;height:15.95pt">
				  <td height=21 class=xl1520912 style="height:15.95pt"></td>
				  <td class=xl6420912 width=99 style="width:74pt">' + Cast((@j) as varchar) + '</td>
				  <td class=xl6520912 width=194 style="width:146pt">' + isnull(name,'')  + '</td>
				  <td class=xl6520912 width=115 style="width:86pt">' +  upper(isnull(Type,'')) + '</td>
				  <td class=xl6620912 width=118 style="width:89pt">' + isnull(Nullable,'N') + '</td>
				  <td class=xl6720912 width=288 style="width:216pt">' + isnull([description],'') + '</td>
				 </tr>'
         from #Columns  where id = @j      
      print    @Output    
      Set @j = @j + 1;
   end    
   --Indexes 
   truncate table #Indexes
   
   print ' <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl7120912>Indexs</td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl6320912></td>
  <td class=xl7020912>　</td>
 </tr>'  
      insert into #Indexes  (Name, type, cols)
         select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end,  col_name(i.object_id, c.column_id)
            from sys.indexes i 
               inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id 
               inner join #Tables t on t.object_id = i.object_id
            where t.id = @i
            order by i.name, c.column_id
  

   Set @maxj =   @@rowcount
   
   set @j = 1
   set @sr = 1
   if (@maxj >0)
   begin

      print ' 
 <tr height=21 style="mso-height-source:userset;height:15.95pt">
  <td height=21 class=xl1520912 style="height:15.95pt"></td>
  <td class=xl8120912 width=99 style="width:74pt">Sr.</td>
  <td class=xl8220912 width=194 style="width:146pt">Name</td>
  <td class=xl8220912 width=115 style="width:86pt">Type</td>
  <td class=xl8220912 width=118 style="width:89pt">Columns</td>
  <td class=xl8420912>　</td>
 </tr>' 
      set @Output = ''
      set @last = ''
      set @current = ''
      While(@j <= @maxj)
      begin
         select @current = isnull(name,'') from #Indexes  where id = @j
                
         if @last <> @current  and @last <> ''
            begin   
            print
			'
			 <tr height=21 style="mso-height-source:userset;height:15.95pt">
			  <td height=21 class=xl1520912 style="height:15.95pt"></td>
			  <td class=xl6420912 width=99 style="width:74pt">' + Cast((@sr) as varchar) + '</td>
			  <td class=xl6520912 width=194 style="width:146pt">' + @last + '</td>
			  <td class=xl6520912 width=115 style="width:86pt">' + @typ + '</td>
			  <td class=xl6620912 width=118 style="width:89pt">' + @Output  + '</td>
			  <td class=xl6720912>　</td>
			 </tr>'
            set @Output  = ''
            set @sr = @sr + 1
            end
         
            
         select @Output = @Output + cols + '<br />' , @typ = type
               from #Indexes  where id = @j
         
         set @last = @current    
         Set @j = @j + 1;
      end
      if @Output <> ''
            begin   
            print '
			  <tr height=21 style="mso-height-source:userset;height:15.95pt">
			  <td height=21 class=xl1520912 style="height:15.95pt"></td>
			  <td class=xl6420912 width=99 style="width:74pt">' + Cast((@sr) as varchar) + '</td>
			  <td class=xl6520912 width=194 style="width:146pt">' + @last + '</td>
			  <td class=xl6520912 width=115 style="width:86pt">' + @typ + '</td>
			  <td class=xl6620912 width=118 style="width:89pt">' + @Output  + '</td>
			  <td class=xl6720912>　</td>
			 </tr>'
            end
   end

    Set @i = @i + 1;
    print '
    <tr height=21 style="mso-height-source:userset;height:15.95pt">
	  <td height=21 class=xl1520912 style="height:15.95pt"></td>
	  <td class=xl6820912 width=99 style="width:74pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6920912 width=194 style="width:146pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6920912 width=115 style="width:86pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6920912 width=118 style="width:89pt" style="border-top:1.0pt solid black;">　</td>
	  <td class=xl6320912 style="border-top:1.0pt solid black;"></td>
	 </tr>
	 <tr height=21 style="mso-height-source:userset;height:15.95pt">
	  <td height=21 class=xl1520912 style="height:15.95pt"></td>
	  <td class=xl6820912 width=99 style="width:74pt">　</td>
	  <td class=xl6920912 width=194 style="width:146pt">　</td>
	  <td class=xl6920912 width=115 style="width:86pt">　</td>
	  <td class=xl6920912 width=118 style="width:89pt">　</td>
	  <td class=xl6320912></td>
	 </tr>'
end

print'
</table>
</div>
</body>
</html>
'
drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes 
set nocount off

'Database > Query' 카테고리의 다른 글

Find and Kill all the Blocked Process/Query (0)	2020.09.22
오래된 커서 (0)	2020.09.11
sp_who3 (0)	2020.08.29
특정 문자열 포함하는 프로시저 (0)	2020.08.28
Full Scan, Index Scan Query (0)	2020.08.28

sp_who3

ㅎ2 2020. 8. 29. 18:27

2020. 8. 29. 18:27

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_who3')
DROP PROCEDURE sp_who3
GO
CREATE PROCEDURE sp_who3 @x NVARCHAR(128) = NULL 
WITH RECOMPILE
AS
/****************************************************************************************** 
   This is a current activity query used to identify what processes are currently running 
   on the processors.  Use to first view the current system load and to identify a session 
   of interest such as blocking, waiting and granted memory.  You should execute the query 
   several times to identify if a query is increasing it's I/O, CPU time or memory granted.
   
   *Revision History
   - 31-Jul-2011 (Rodrigo): Initial development
   - 12-Apr-2012 (Rodrigo): Enhanced sql_text, object_name outputs;
								  Added NOLOCK hints and RECOMPILE option;
								  Added BlkBy column;
								  Removed dead-code.
   - 03-Nov-2014 (Rodrigo): Added program_name and open_transaction_count	column
   - 10-Nov-2014 (Rodrigo): Added granted_memory_GB
   - 03-Nov-2015 (Rodrigo): Added parameters to show memory and cpu information
   - 12-Nov-2015 (Rodrigo): Added query to get IO info
   - 17-Nov-2015 (Rodrigo): Changed the logic and addedd new parameters
   - 18-Nov-2015 (Rodrigo): Added help content
*******************************************************************************************/
BEGIN
	SET NOCOUNT ON;
	IF @x IS NULL
		BEGIN
			SELECT r.session_id, se.host_name, se.login_name, Db_name(r.database_id) AS dbname, r.status, r.command,
				   CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
					+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
					+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
				   r.blocking_session_id AS BlkBy, r.open_transaction_count AS NoOfOpenTran, r.wait_type,
				   CAST(ROUND((r.granted_query_memory / 128.0)  / 1024,2) AS NUMERIC(10,2))AS granted_memory_GB,
				   object_name = OBJECT_SCHEMA_NAME(s.objectid,s.dbid) + '.' + OBJECT_NAME(s.objectid, s.dbid),
 				   program_name = se.program_name, p.query_plan AS query_plan,
				   sql_text = SUBSTRING	(s.text,r.statement_start_offset/2,
						(CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), s.text)) * 2
							ELSE r.statement_end_offset	END - r.statement_start_offset)/2),
					r.cpu_time,	start_time, percent_complete,		
					CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
					+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
					+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
					dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
			FROM   sys.dm_exec_requests r WITH (NOLOCK) 
			JOIN sys.dm_exec_sessions se WITH (NOLOCK)
				ON r.session_id = se.session_id 
			OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s 
			OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p 
			WHERE  r.session_id <> @@SPID AND se.is_user_process = 1;
		END
	ELSE IF @x = '1'  OR @x = 'memory'
		BEGIN
			-- who is consuming the memory
			SELECT session_id, granted_memory_kb FROM sys.dm_exec_query_memory_grants WITH (NOLOCK) ORDER BY 1 DESC;
		END
	ELSE IF @x = '2'  OR @x = 'cpu'
		BEGIN
			-- who has cached plans that consumed the most cumulative CPU (top 10)
			SELECT TOP 10 DatabaseName = DB_Name(t.dbid),
							sql_text = SUBSTRING (t.text, qs.statement_start_offset/2,
										(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), t.text)) * 2
										ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),
							ObjectName = OBJECT_SCHEMA_NAME(t.objectid,t.dbid) + '.' + OBJECT_NAME(t.objectid, t.dbid),
							qs.execution_count AS [Executions], qs.total_worker_time AS [Total CPU Time],
							qs.total_physical_reads AS [Disk Reads (worst reads)],	qs.total_elapsed_time AS [Duration], 
							qs.total_worker_time/qs.execution_count AS [Avg CPU Time],qs.plan_generation_num,
								qs.creation_time AS [Data Cached], qp.query_plan
			FROM sys.dm_exec_query_stats qs WITH(NOLOCK) 
			CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
			CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
			ORDER BY DatabaseName, qs.total_worker_time DESC;
		END
	ELSE IF @x = '3'  OR @x = 'count'
		BEGIN
			-- who is connected and how many sessions it has 
			SELECT login_name, [program_name],No_of_Connections = COUNT(session_id)
			FROM sys.dm_exec_sessions WITH (NOLOCK)
			WHERE session_id > 50 GROUP BY login_name, [program_name] ORDER BY COUNT(session_id) DESC
		END
	ELSE IF @x = '4'  OR @x = 'idle'
		BEGIN
			-- who is idle that have open transactions
			SELECT s.session_id, login_name, login_time, host_name, host_process_id, status FROM sys.dm_exec_sessions AS s WITH (NOLOCK)
			WHERE EXISTS (SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id)
			AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id)
		END
	ELSE IF @x = '5' OR @x = 'tempdb'
		BEGIN
			-- who is running tasks that use tempdb (top 5)
			SELECT TOP 5 session_id, request_id,  user_objects_alloc_page_count + internal_objects_alloc_page_count as task_alloc
			FROM    tempdb.sys.dm_db_task_space_usage  WITH (NOLOCK)
			WHERE   session_id > 50 ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC
		END
	ELSE IF @x = '6' OR @x = 'block'
		BEGIN
			-- who is blocking
			SELECT DB_NAME(lok.resource_database_id) as db_name,lok.resource_description,lok.request_type,lok.request_status,lok.request_owner_type
			,wat.session_id as wait_session_id,wat.wait_duration_ms,wat.wait_type,wat.blocking_session_id
			FROM  sys.dm_tran_locks lok WITH (NOLOCK) JOIN sys.dm_os_waiting_tasks wat WITH (NOLOCK) ON lok.lock_owner_address = wat.resource_address 
		END
	ELSE IF @x = '0' OR @x = 'help'
		BEGIN
			DECLARE @text NVARCHAR(4000);
			DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10);
			SET @text = N'Synopsis:' + @NewLineChar +
						N'Who is currently running on my system?'  + @NewLineChar +
						N'-------------------------------------------------------------------------------------------------------------------------------------'  + @NewLineChar +
						N'Description:'  + @NewLineChar +
						N'The first area to look at on a system running SQL Server is the utilization of hardware resources, the core of which are memory,' + @NewLineChar +
						N'storage, CPU and long blockings. Use sp_who3 to first view the current system load and to identify a session of interest.' + @NewLineChar +
						N'You should execute the query several times to identify which session id is most consuming teh system resources.' + @NewLineChar +
						N'-------------------------------------------------------------------------------------------------------------------------------------' + @NewLineChar +
						N'Parameters:'  + @NewLineChar +
						N'sp_who3 null				- who is active;' + @NewLineChar +
						N'sp_who3 1 or ''memory''  	- who is consuming the memory;' + @NewLineChar +
						N'sp_who3 2 or ''cpu''  	- who has cached plans that consumed the most cumulative CPU (top 10);'+ @NewLineChar +
						N'sp_who3 3 or ''count''  	- who is connected and how many sessions it has;'+ @NewLineChar +
						N'sp_who3 4 or ''idle'' 	- who is idle that has open transactions;'+ @NewLineChar +
						N'sp_who3 5 or ''tempdb'' 	- who is running tasks that use tempdb (top 5); and,'+ @NewLineChar +
						N'sp_who3 6 or ''block'' 	- who is blocking.'
			PRINT @text;
		END
END;
GO

'Database > Query' 카테고리의 다른 글

오래된 커서 (0)	2020.09.11
테이블 명세서 (0)	2020.08.29
특정 문자열 포함하는 프로시저 (0)	2020.08.28
Full Scan, Index Scan Query (0)	2020.08.28
모든 인덱스 다시 리빌드, 통계 업데이트 (0)	2020.08.28

SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE)

ㅎ2 2020. 8. 29. 18:25

2020. 8. 29. 18:25

SQL Server 서비스가 시작될 때마다 새로운 로그 파일이 생성되게 됩니다. 즉, 서비스를 재시작하지 않으면 Error Log 파일 size 가 크게 늘어날 수 있습니다.

기본적으로 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log 폴더에 ERRORLOG ~ ERRORLOG.n(1~6) 까지의 파일이 생성되게 됩니다. 기본값으로 로그 파일 개수를 6개로 제한하였기 때문입니다. 이것은 SQL Server 를 6번 재시작 한다면 그 이전 로그는 사라진다는 것을 의미합니다.

필요에 따라 Error Log 파일 개수를 변경할 수 있습니다.

SSMS - Management - SQL Server Logs - Configure - '재활용 이전의 오류 로그 파일 수 제한' 변경

아래 자료는 10개로 변경한 뒤 그 결과를 확인한 그림입니다.

T-SQL 로 확인해 봅니다.

EXEC xp_enumerrorlogs

보관 # 날짜 로그 파일 크기(바이트)

----------------------------------------------------------------

0 09/21/2009 10:47 1708

1 09/21/2009 10:47 1976

10 09/18/2009 11:13 311706

2 09/21/2009 10:47 1976

3 09/21/2009 10:47 1976

4 09/21/2009 10:47 1976

5 09/21/2009 10:47 1976

6 09/21/2009 10:47 1976

7 09/21/2009 10:47 11480

8 09/21/2009 10:25 11814

9 09/18/2009 11:58 13948

추가로 Registry NumErrorLogs 값을 변경할 수도 있습니다.

HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10

현재 설정된 값을 확인하기 위해서도 Reg 명령을 사용할 수 있습니다.

C:\>reg query "HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10.mssqlserver\mssqlserver" /v NumerrorLogs

그리고 또 하나!

SQL Server Error Log 파일의 size 가 늘어나면 SSMS 에서 불러오는 데 작은 고통을 느낄 수 있습니다. 서비스를 재시작 하지 않고 단지 로그 파일만 순환(CYCLE) 시키는 방법이 있으니 아래 T-SQL 을 참조하시면 되겠습니다.

EXEC sp_cycle_errorlog ;

[참고자료]

sp_cycle_errorlog(Transact-SQL)

http://msdn.microsoft.com/ko-kr/library/ms182512.aspx

출처: https://laigo.kr/356 [Lai Go's Blog]

'Database > SQL Server' 카테고리의 다른 글

SQL Server 인덱스 활성 / 비활성 하기 (0)	2020.08.31
쿼럼 리소스 이동 (0)	2020.08.29
악성 쿼리 찾아내기 - ReadTrace (0)	2020.08.29
Master Key (0)	2020.08.29
[DMV] 특정 프로시저의 실행계획이 바뀌었다? (0)	2020.08.29

악성 쿼리 찾아내기 - ReadTrace

ㅎ2 2020. 8. 29. 18:25

2020. 8. 29. 18:25

일반적으로 튜닝은 전체적으로 다음과 같은 단계들로 이루어진다.

무엇이 문제인지를 확인하고 현재 상태를 진단하여 튜닝의 기준선(Baseline)을 정의
병목이 발생하는 지점이 어디인지를 확인
확인된 병목을 해소할 방법을 모색
해소방법을 적용한 후 다시 성능을 측정하여 (1단계 기준선 대비) 실제로 성능이 향상되었는지를 확인

그래서 튜닝의 고수들은 가장 중요한 것이 "성능의 측정기준을 확인하는 것"이라고도 하지만, 나같은 하수에게는 아무래도 그것보다는 병목을 찾아내는 부분이 더 관심이 가게 마련이다. 6^^

보통 Profiler 를 사용하여 현재 DB에서 실행되는 SQL을 확인하고 악성 SQL을 확인하게 되는데... 이 작업을 하다 보면 다음과 같은 문제가 발생한다.

Type 1 : 분당 300건씩 실행되는 SQL
SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '29472' -- 실행시간 500ms

SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '38749' -- 실행시간 500ms
SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '32945' -- 실행시간 500ms
...

Type 2 : 분당 1건씩 실행되는 SQL
SELECT SERIAL, IP_ADDR, START_DATE, END_DATE FROM IP_TABLE WHERE EMPNO NOT IN (SELECT EMPNO FROM INSATABLE WHERE STATUS = 'R') -- 실행시간 1000ms

위와 같은 두가지 유형의 SQL 중 어느것이 더 악성일까? 당연히 실행 빈도가 훨씬 높은 Type 1이다. 하지만 문제는 이걸 엄청나게 많은 SQL들이 섞여 있는 Profiler에서 육안으로 판단해내기가 쉽지 않다는 것이다. 따라서 악성 SQL을 확인할 때는 다음과 같이 SQL을 일반화해서 비교해야 한다.

Type 1 :
SELECT IP_ADDR FROM IP_TABLE WHERE SABUN = '##' -- 평균 실행시간 500ms, 총 실행시간 150000 ms

Type 2 :
SELECT SERIAL, IP_ADDR, START_DATE, END_DATE FROM IP_TABLE WHERE EMPNO NOT IN (SELECT EMPNO FROM INSATABLE WHERE STATUS = '##') -- 평균 실행시간 1000ms 실행시간 1000 ms

SQL Server 2000 에서는 수집한 SQL을 일반화하기 위해 Read80Trace 프로그램을 사용했었는데... SQL Server 2005에서 수집한 WorkLoad를 분석시키니.. 에러가 나고 제대로 실행이 되지 않았다.
알고 보니 SQL Server 2005에서는 ReadTrace 라는 2005 버젼의 SQL 일반화 Tool 이 따로 제공되고 있었다.
(프로그램 이름에서 80이 떨어진 걸 보니 이 프로그램은 추후 업그레이드될 SQL Server 버젼에서도 일반적으로 사용가능한 모양이다.)

이 프로그램을 사용하는 단계는 전체적으로 다음과 같다.

RML 패키지 설치
부하(Workload) 수집
수집한 Workload 를 분석
분석 결과 확인

※ RML Utility 도움말에서는 SQLDiag.exe로 시스템 정보를 수집하는 내용이 있지만, 이것은 튜닝대상 서버의 환경을 분석하여 베이스 정보를 수집하기 위한 과정이고, Report 생성에 필요한 과정은 아니라고 도움말에 나와있다. ^^;

좀 더 자세히 살펴보면 다음과 같다.

Step 1. RML 패키지 설치
Workload 분석 툴인 ReadTrace와 Workload Replay 툴인 O'Stress로 이루어진 RML Utility (Replay Markup Language Utility)를 다운받아 설치한다. 가급적이면 분석 대상인 DB서버에 바로 설치하는 것이 여러 모로 편하다. 다음의 URL에서 다운로드할 수 있다.

http://support.microsoft.com/kb/944837

설치 중에 RML Reporter 프로그램을 설치하겠냐고 묻는데 함께 설치한다.

Step 2. DB 에서 부하(Workload)를 수집
Profiler나 SQLDiag, SQL을 수집하는 스크립트(첨부) 등을 사용하여 현재 DB에서 수행되는 SQL (Workload)을 수집하고 trc 파일로 저장한다.
Profiler를 사용하는 방법은... 쉽기는 한데 Workload 수집시에 서버 부하가 높고 내부적으로 ReadTrace가 요구하는 모든 이벤트를 수집해야 하는데 이 조건을 만족시키는 템플릿을 구하기가 어렵고 (내가 만들긴 귀찮고^^)... 모 등등 해서 아래의 SQL문으로 수집하는 것이 낫다. (스크립트 출처 : RML Utility 도움말)

TraceCaptureDef.sql

해당 DB서버에서 SQL Server Management Studio로 열고 trc 파일 저장위치('InsertFileNameHere') 부분을 'D:\Test'와 같이 수정하여 실행해준다. trc 확장자는 자동으로 append되므로 붙일 필요가 없으며, 만약의 경우를 대비하여 OS가 설치된 파티션이나 mdf/ldf 파일이 존재하는 파티션과 다른 파티션에 저장하는 것이 좋다.

실행하면 아래와 같은 결과가 SELECT되는데 이 때 내가 실행시킨 수집(Trace) 세션의 ID (아래 그림에서는 "2")를 기억해놓도록 한다.

"메시지" 탭을 선택하면 이 수집(Trace) 작업을 중지시킬 때 뭐라고 입력해야 하는지를 친절하게 알려주고 있다.

탐색기에서 아래와 같이 trc 파일에 Workload 결과가 쌓이는 것을 볼 수 있다. 처음에는 한동안 trc 파일 사이즈가 0KB로 남아있을 수 있는데, 새로고침을 누르면서 몇분간 기다리면 파일 사이즈가 늘어나는 것을 확인할 수 있다.

충분한 Workload가 수집되면 다음의 SQL을 실행하여 Trace를 중지시킨다.

exec sp_trace_setstatus 2, 0 -- stop trace job
exec sp_trace_setstatus 2, 2 -- delete and deallocate trace job

Step 3. 수집한 Workload 를 분석

이제 수집된 Workload를 분석하기 위해 ReadTrace 프로그램을 사용한다.
ReadTrace 프로그램을 DB서버 Local에 설치했다면 이 작업도 당연히 DB서버 로컬에서 수행해야 한다.
ReadTrace.exe 는 커맨드 창에서 실행시켜야 하는 프로그램인데, 설치된 경로를 PATH 환경변수에 지정하기 위해 RML Utility에서 지원하는 RML Command창을 사용한다.

커맨드 창을 연 후 수집한 Workload 파일(trc 파일)이 있는 곳으로 이동하여 ReadTrace.exe를 실행시킨다.
(만약 RML 커맨드창이 아니라면 ReadTrace.exe 파일의 Full Path를 타이핑해주어야 한다.)

실행시키는 명령어는 ReadTrace /? 를 쳐보면 Usage를 볼 수 있으나 기본적으로는 아래와 같은 한가지 파라메터만 주면 된다.

D:\Temp\ReadTrace -ITraceFileName.trc

위와 같이 주면 기본적으로 분석 결과가 PerfAnalysis 라는 DB에 저장된다. 이 경우 여러번 분석하게 되면 이전 결과를 덮어쓰게 되므로 다음과 같이 DB 이름도 지정해줄 수 있다. (당연한 얘기지만... DB가 없으면 생성된다.)

D:\Temp\ReadTrace -ITraceFileName.trc -dPerfDB_20081218_1

Step 4. 분석 결과 확인

ReadTrace를 설치할 때 자동으로 함께 설치되는 RML Reporter 프로그램을 사용하여 분석된 결과를 Visual한 Report로 확인하고 악성 SQL을 찾아낸다. 최초 1번은 자동으로 실행되며 이후 레포트를 다시 보려면 다음과 같이 수동으로 Reporter 프로그램을 실행시키면 된다.

레포트의 첫 페이지는 아래와 같다.
중앙의 큰 그래프는 Workload 수집 시간 중의 부하 변화를 그래프로 보여준다.

여기서 부하를 어떤 기준으로 Grouping할지를 선택할 수 있는데, "Application Name"을 클릭할 경우 아래와 같이 Grouping할 Application을 선택할 수 있다. (한번 클릭할 때마다 탭이 새로 열린다.)

여기서 원하는 Application을 선택하면 아래와 같이 해당 Application이 실행시킨 SQL을 분석하여 보여준다.

위의 그래프를 보면 CPU Usage, Duration, Logical Read, Logical Write 등 다양한 기준 별로 사용율 Top 10 SQL을 보여주고 있다. 그래프에서는 1, 2, 3.. 이렇게 SQL의 번호만 표시되고 있는데, 스크롤 바를 조금만 내려보면 각 번호에 해당하는 SQL을 아래와 같이 보여준다.

위의 SQL중 하나를 클릭하면 아래와 같이 각 SQL에 대한 상세 정보를 볼 수 있다.

솔직히 난... 그래피컬하고 화려한 ReadTrace의 레포트보다 일목요연하고 가지고다니기 편한 Read80Trace의 html 레포트가 더 좋아보인다. (뭐 ReadTrace의 레포트도 Excel 내보내기를 지원하긴 한다.)

이렇게 악성SQL을 찾아내면... 그 다음은 개선만 하면 된다.
뭐, CPU사용율과 Logical Reads가 높은 SQL이라면 인덱스 쪽을 고려해볼 것이고, CPU사용율에 비해 Duration이 높은 경우라면 Blocking을 의심해볼 수 있을 것이다.

'Database > SQL Server' 카테고리의 다른 글

쿼럼 리소스 이동 (0)	2020.08.29
SQL Server Logs 파일 수 변경 및 로그 순환(CYCLE) (0)	2020.08.29
Master Key (0)	2020.08.29
[DMV] 특정 프로시저의 실행계획이 바뀌었다? (0)	2020.08.29
SQL Server2012 Checkpoint 제어 (0)	2020.08.29

PREV 이전 1 ···3 4 5 6 7 8 9 ···12 NEXT 다음

SQL