처음 접하는 SQL Server의 기본 정보를 알아보기 위한 쿼리입니다.
기본정보 수집이 목적이며, 이후 성능분석이나 추가적인 작업 시 참고하기 위함입니다.
버전확인
SELECT @@VERSION
GO
SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ISClusterd') AS Clusterd;
GO
서버명
SELECT @@SERVERNAME
GO
메모리 정보 확인
DBCC MEMORYSTATUS
GO
스레드정보확인
DBCC SQLPERF(UMSSTATS)
GO
파라미터설정정보확인
EXEC SP_CONFIGURE
GO
대기항목정보확인 1
DBCC SQLPERF(WAITSTATS)
GO
대기항목정보확인 2
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms desc
대기항목분석
CREATE TABLE #WAITLOG([WAIT TYPE] VARCHAR(50), [REQUESTS] FLOAT, [WAIT TIME] FLOAT, [SIGNAL WAIT TIME] FLOAT)
GO
INSERT INTO #WAITLOG
EXEC ('DBCC SQLPERF(WAITSTATS)')
GO
SELECT A.TYPE,SUM(A.[REQUESTS]) REQUESTS,SUM(A.[WAIT TIME]) WAITTIME,SUM(A.[SIGNAL WAIT TIME]) SIGNALWAITTIME FROM ( SELECT CASE WHEN SUBSTRING([WAIT TYPE],1,3) = 'LCK' THEN 'LCK' WHEN SUBSTRING([WAIT TYPE],1,5) = 'LATCH' THEN 'LATCH' WHEN SUBSTRING([WAIT TYPE],1,9) = 'PAGELATCH' THEN 'PAGELATCH' WHEN SUBSTRING([WAIT TYPE],1,11) = 'PAGEIOLATCH' THEN 'PAGEIOLATCH' WHEN SUBSTRING([WAIT TYPE],1,9) = 'TRAN_MARK' THEN 'TRAN_MARK' ELSE [WAIT TYPE]END AS TYPE,[REQUESTS],[WAIT TIME],[SIGNAL WAIT TIME] FROM #WAITLOG ) A GROUP BY A.TYPE ORDER BY WAITTIME DESC
DROP TABLE #WAITLOG
GO
Log 사이즈 출력
DBCC SQLPERF(LOGSPACE)
GO
캐시 오브젝트 그룹핑
select cacheobjtype,objtype,count(*) cnt from master.dbo.syscacheobjects group by cacheobjtype,objtype
GO
가장 많이 사용하는 캐시쿼리문 패턴 도출
select substring(sql,1,70) subsql,count(*) cnt,sum(refcounts) refcounts,sum(usecounts) usecounts from master.dbo.syscacheobjects group by substring(sql,1,70) order by cnt desc
GO
데이터파일 정보출력
SET NOCOUNT ON
Create Table #DBFileINFO (name varchar(50), fileid int, filename varchar(1000), filegroup varchar(100), size varchar(100), maxsize varchar(100), growth varchar(100), usage varchar(100) )
declare @name varchar(100), @cmd varchar(2000)
DECLARE dbname CURSOR READ_ONLY FOR SELECT Name FROM master.dbo.sysdatabases
OPEN dbname FETCH NEXT FROM dbname INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
select @cmd = N'use ' + quotename(@name) + N' insert into #DBFileINFO(name, fileid, filename, filegroup, size, maxsize, growth, usage) exec sp_helpfile'
exec (@cmd)
FETCH NEXT FROM dbname INTO @name
END
CLOSE dbname
DEALLOCATE dbname
select * from #DBFileINFO
drop table #DBFileINFO
SET NOCOUNT OFF
GO
로그파일정보 출력
DBCC SQLPERF(LOGSPACE)
DB정보출력 1
SP_HELPDB
DB정보출력 2
SET NOCOUNT ON
Create Table #DBINFO (name varchar(50), dboption varchar(8000) )
declare @name varchar(100), @dbdesc varchar(8000)
set @dbdesc = ''
DECLARE dbname CURSOR READ_ONLY FOR SELECT Name FROM master.dbo.sysdatabases
OPEN dbname FETCH NEXT FROM dbname INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @dbdesc = 'Status=' + convert(sysname,DatabasePropertyEx(@name,'Status'))
SELECT @dbdesc = @dbdesc + ', Updateability=' + convert(sysname,DatabasePropertyEx(@name,'Updateability'))
SELECT @dbdesc = @dbdesc + ', UserAccess=' + convert(sysname,DatabasePropertyEx(@name,'UserAccess'))
SELECT @dbdesc = @dbdesc + ', Recovery=' + convert(sysname,DatabasePropertyEx(@name,'Recovery'))
IF DatabaseProperty(@name, 'IsShutdown') = 0
BEGIN
SELECT @dbdesc = @dbdesc + ', Version=' + convert(sysname,DatabasePropertyEx(@name,'Version'))
SELECT @dbdesc = @dbdesc + ', Collation=' + convert(sysname,DatabasePropertyEx(@name,'Collation'))
SELECT @dbdesc = @dbdesc + ', SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@name,'SQLSortOrder'))
END
IF DatabasePropertyEx(@name,'IsAutoClose') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoClose'
IF DatabasePropertyEx(@name,'IsAutoShrink') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoShrink'
IF DatabasePropertyEx(@name,'IsInStandby') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsInStandby'
IF DatabasePropertyEx(@name,'IsTornPageDetectionEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled'
IF DatabasePropertyEx(@name,'IsAnsiNullDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault'
IF DatabasePropertyEx(@name,'IsAnsiNullsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled'
IF DatabasePropertyEx(@name,'IsAnsiPaddingEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled'
IF DatabasePropertyEx(@name,'IsAnsiWarningsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled'
IF DatabasePropertyEx(@name,'IsArithmeticAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled'
IF DatabasePropertyEx(@name,'IsAutoCreateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics'
IF DatabasePropertyEx(@name,'IsAutoUpdateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics'
IF DatabasePropertyEx(@name,'IsCloseCursorsOnCommitEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled'
IF DatabasePropertyEx(@name,'IsFullTextEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled'
IF DatabasePropertyEx(@name,'IsLocalCursorsDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault'
IF DatabasePropertyEx(@name,'IsNullConcat') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNullConcat'
IF DatabasePropertyEx(@name,'IsNumericRoundAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled'
IF DatabasePropertyEx(@name,'IsQuotedIdentifiersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled'
IF DatabasePropertyEx(@name,'IsRecursiveTriggersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled'
IF DatabasePropertyEx(@name,'IsMergePublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsMergePublished'
IF DatabasePropertyEx(@name,'IsPublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsPublished'
IF DatabasePropertyEx(@name,'IsSubscribed') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSubscribed'
IF DatabasePropertyEx(@name,'IsSyncWithBackup') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup'
Insert into #DBINFO values (@name,@dbdesc)
FETCH NEXT FROM dbname INTO @name
END
CLOSE dbname
DEALLOCATE dbname
select * from #DBINFO
drop table #DBINFO
SET NOCOUNT OFF
GO
테이블 사용량정보
USE MASTER
GO
SET NOCOUNT ON
CREATE TABLE #tblSpace( DBNAME VARCHAR(50), name VARCHAR(50), rows INT,reserved VARCHAR(30),data VARCHAR(30), index_size VARCHAR(30),unused VARCHAR(30))
GO
CREATE PROC SP_TABLERECINFO @CURDB VARCHAR(100)
AS
DECLARE RCur CURSOR READ_ONLY FOR select 'sp_spaceused ''' + NAME + '''' AS SQL from SYSOBJECTS where XTYPE = 'U'
DECLARE @strSQL VARCHAR(2000), @dtStart DATETIME
OPEN RCur FETCH NEXT FROM RCur INTO @strSQL
CREATE TABLE #tblSpace2( name VARCHAR(50), rows VARCHAR(30),reserved VARCHAR(30),data VARCHAR(30), index_size VARCHAR(30),unused VARCHAR(30))
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
INSERT INTO #tblSpace2
EXEC(@STRSQL)
END
FETCH NEXT FROM RCur INTO @strSQL
END
insert into #tblSpace(DBNAME,[name], [rows], reserved, data, index_size, unused)
SELECT A.DBNAME,B.* FROM (SELECT @CURDB AS DBNAME) A ,#TBLSPACE2 B
DROP TABLE #TBLSPACE2
CLOSE RCur
DEALLOCATE RCur
GO
declare @name varchar(100), @cmd varchar(2000)
DECLARE dbname CURSOR READ_ONLY FOR SELECT Name FROM master.dbo.sysdatabases where name not in('master','msdb','model','tempdb','pubs','northwind','DISTRIBUTE','SQLBPA')
OPEN dbname FETCH NEXT FROM dbname INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
select @cmd = N'use ' + quotename(@name) + N' exec SP_TABLERECINFO ' +@NAME
exec (@cmd)
FETCH NEXT FROM dbname INTO @name
END
CLOSE dbname
DEALLOCATE dbname
GO
SELECT * FROM #tblSpace ORDER BY DBNAME, ROWS DESC
DROP TABLE #tblSpace
DROP PROC SP_TABLERECINFO
SET NOCOUNT OFF
GO
VLF 개수(데이터베이스별)
SET NOCOUNT ON
DECLARE @dbname VARCHAR(128)
CREATE TABLE #LOGDB(FileId varchar(100),FileSize varchar(1000), StartOffset varchar(1000), FSeqNo varchar(1000), Status varchar(10),Parity varchar(100), CreateLSN varchar(100))
CREATE TABLE #DBN(DBNAME Varchar(150))
CREATE TABLE #LOGDB2(DBNAME Varchar(150), FileId varchar(100),FileSize varchar(1000), StartOffset varchar(1000), FSeqNo varchar(100), Status varchar(10),Parity varchar(100), CreateLSN varchar(100))
DECLARE dbname CURSOR READ_ONLY FOR SELECT Name FROM master.dbo.sysdatabases
OPEN dbname FETCH NEXT FROM dbname INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #LOGDB
EXEC ('DBCC LOGINFO (''' + @dbname + ''')')
EXEC ('INSERT INTO #DBN values ('''+@dbname+''')')
INSERT INTO #LOGDB2
SELECT * FROM #DBN AS A , #LOGDB AS B
TRUNCATE TABLE #DBN
TRUNCATE TABLE #LOGDB
FETCH NEXT FROM dbname INTO @dbname
END
--SELECT * FROM #LOGDB2
SELECT DBNAME, count(*) VLF_COUNT FROM #LOGDB2 GROUP BY DBNAME
DROP TABLE #DBN
DROP TABLE #LOGDB
DROP TABLE #LOGDB2
CLOSE dbname
DEALLOCATE dbname
GO
VLF조사
DBCC LOGINFO
'Database > SQL Server' 카테고리의 다른 글
SQL Server 연결된서버 쿼리 오류 분석 방법 예시 (0) | 2024.04.12 |
---|---|
SQL Server 컬럼 암호화 방법 step by step (2) | 2024.04.12 |
SQL Server 프로시저 내 TEXT내용으로 검색 (0) | 2024.04.12 |
DBCC Shrinkfile (0) | 2024.04.12 |
Amazon RDS for SQL Server를 S3에 백업 복원 (0) | 2024.04.12 |