004_01.sp_sqlperf SQL SERVER 문서화되지 않은 상태 모니터링

---------------------------------------------------------------------------------
-- 004_01.sp_sqlperf  SQL SERVER  문서화되지 않은 상태 모니터링
---------------------------------------------------------------------------------
-- 작성자 : 추숙
-- 수정자 :
-- 마지막 수정 내용
--  1. NULL :
---------------------------------------------------------------------------------
USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_sqlperf') 
 BEGIN
  DROP  PROCEDURE sp_sqlperf
 END
GO
CREATE  PROCEDURE  dbo.sp_sqlperf 
 @aPerfType TINYINT = NULL -- all 
AS
 SET NOCOUNT ON
 
 IF @aPerfType = 1
  OR @aPerfType IS NULL
 BEGIN
  SELECT '01. data about the transaction log for all of the databases on the SQL Server, including Database Name, Log Size (MB), Log Space Used (%), and Status.'
  DBCC SQLPERF (LOGSPACE);
 END

 IF @aPerfType = 2
  OR @aPerfType IS NULL
 BEGIN
  SELECT '02. data about SQL Server thread management.'
  DBCC SQLPERF (UMSSTATS);
 END

 IF @aPerfType = 3
  OR @aPerfType IS NULL
 BEGIN
  SELECT '03. data about wait types for SQL Server resources'
  DBCC SQLPERF (WAITSTATS);
 END

 IF @aPerfType = 4
  OR @aPerfType IS NULL
 BEGIN
  SELECT '04. data about outstanding SQL Server reads and writes'
  DBCC SQLPERF (RASTATS);
 END

 IF @aPerfType = 5
  OR @aPerfType IS NULL
 BEGIN
  SELECT '05. data about I/O, CPU, and memory usage per SQL Server thread. [7.0, 2000] Updated 3-20-2006.'
  DBCC SQLPERF (THREADS)
 END

 IF @aPerfType = 6
  OR @aPerfType IS NULL
 BEGIN
  SELECT '06. Note, this example was run on a 4 CPU server. There is 1 Scheduler ID per available CPU'
  DBCC SQLPERF (UMSSTATS);
 END

 
GO

-- TEST QUERY
exec dbo.sp_sqlperf -- all
exec dbo.sp_sqlperf 1
exec dbo.sp_sqlperf 2
exec dbo.sp_sqlperf 3
exec dbo.sp_sqlperf 4
exec dbo.sp_sqlperf 5
exec dbo.sp_sqlperf 6