SQL Server 기본 점검 쿼리

처음 접하는 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