4. 런타임 통계 수집

 

 

 

 

 

구글 Document에 의하면 아래와 같이 적혀 있다.

 

"이 프로시저를 사용하려면 먼저 OLE 자동화 프로시저를 일시적으로 사용하도록 설정하여 파일을 디스크에 작성합니다. 테스트한 후에는 사용하지 않도록 설정해야 합니다."

 

 

쿼리문을 실행을 하면 아래와 같이 성공화면이 보인다 

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

 

 

 

 

 

SQL Server Management Studio에서 sp_write_performance_counters 프로시저를 만드는 코드이다. 부하 테스트를 시작하기 전에 Management Studio에서 다음 프로시저를 실행한다.

 

 

 

 

아래 코드 쿼리 실행하기!

 

 

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/***
LogFile path has to be in a directory that SQL Server can Write To.
*/
CREATE PROCEDURE [dbo].[sp_write_performance_counters] @LogFile varchar (2000) = 'C:\\WINDOWS\\TEMP\\sqlPerf.log', @SecondsToRun int =1600, @RunIntervalSeconds int = 2

AS

BEGIN
--File writing variables
DECLARE @OACreate INT, @OAFile INT, @FileName VARCHAR(2000), @RowText VARCHAR(500), @Loops int, @LoopCounter int, @WaitForSeconds varchar (10)
--Variables to save last counter values
DECLARE @LastTPS BIGINT, @LastLRS BIGINT, @LastLTS BIGINT, @LastLWS BIGINT, @LastNDS BIGINT, @LastAWT BIGINT, @LastAWT_Base BIGINT, @LastALWT BIGINT, @LastALWT_Base BIGINT
--Variables to save current counter values
DECLARE @TPS BIGINT, @Active BIGINT, @SCM BIGINT, @LRS BIGINT, @LTS BIGINT, @LWS BIGINT, @NDS BIGINT, @AWT BIGINT, @AWT_Base BIGINT, @ALWT BIGINT, @ALWT_Base BIGINT, @ALWT_DIV BIGINT, @AWT_DIV BIGINT

SELECT @Loops = case when (@SecondsToRun % @RunIntervalSeconds) > 5 then @SecondsToRun / @RunIntervalSeconds + 1 else @SecondsToRun / @RunIntervalSeconds end
SET @LoopCounter = 0
SELECT @WaitForSeconds = CONVERT(varchar, DATEADD(s, @RunIntervalSeconds , 0), 114)
SELECT @FileName = @LogFile + FORMAT ( GETDATE(), '-MM-dd-yyyy_m', 'en-US' ) + '.txt'

--Create the File Handler and Open the File
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OACreate OUT
EXECUTE sp_OAMethod @OACreate, 'OpenTextFile', @OAFile OUT, @FileName, 2, True, -2

--Write the Header
EXECUTE sp_OAMethod @OAFile, 'WriteLine', NULL,'Transactions/sec, Active Transactions, SQL Cache Memory (KB), Lock Requests/sec, Lock Timeouts/sec, Lock Waits/sec, Number of Deadlocks/sec, Average Wait Time (ms), Average Latch Wait Time (ms)'
--Collect Initial Sample Values
SET ANSI_WARNINGS OFF
SELECT
  @LastTPS= max(case when counter_name = 'Transactions/sec' then cntr_value end),
  @LastLRS = max(case when counter_name = 'Lock Requests/sec' then cntr_value end),
  @LastLTS = max(case when counter_name = 'Lock Timeouts/sec' then cntr_value end),
  @LastLWS = max(case when counter_name = 'Lock Waits/sec' then cntr_value end),
  @LastNDS = max(case when counter_name = 'Number of Deadlocks/sec' then cntr_value end),
  @LastAWT = max(case when counter_name = 'Average Wait Time (ms)' then cntr_value end),
  @LastAWT_Base = max(case when counter_name = 'Average Wait Time base' then cntr_value end),
  @LastALWT = max(case when counter_name = 'Average Latch Wait Time (ms)' then cntr_value end),
  @LastALWT_Base = max(case when counter_name = 'Average Latch Wait Time base' then cntr_value end)
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Transactions/sec',
'Lock Requests/sec',
'Lock Timeouts/sec',
'Lock Waits/sec',
'Number of Deadlocks/sec',
'Average Wait Time (ms)',
'Average Wait Time base',
'Average Latch Wait Time (ms)',
'Average Latch Wait Time base') AND instance_name IN( '_Total' ,'')
SET ANSI_WARNINGS ON
WHILE @LoopCounter <= @Loops
BEGIN
WAITFOR DELAY @WaitForSeconds
SET ANSI_WARNINGS OFF
SELECT
  @TPS= max(case when counter_name = 'Transactions/sec' then cntr_value end)   ,
  @Active = max(case when counter_name = 'Active Transactions' then cntr_value end)   ,
  @SCM = max(case when counter_name = 'SQL Cache Memory (KB)' then cntr_value end)   ,
  @LRS = max(case when counter_name = 'Lock Requests/sec' then cntr_value end)   ,
  @LTS = max(case when counter_name = 'Lock Timeouts/sec' then cntr_value end)   ,
  @LWS = max(case when counter_name = 'Lock Waits/sec' then cntr_value end)   ,
  @NDS = max(case when counter_name = 'Number of Deadlocks/sec' then cntr_value end)   ,
  @AWT = max(case when counter_name = 'Average Wait Time (ms)' then cntr_value end)   ,
  @AWT_Base = max(case when counter_name = 'Average Wait Time base' then cntr_value end)   ,
  @ALWT = max(case when counter_name = 'Average Latch Wait Time (ms)' then cntr_value end)   ,
  @ALWT_Base = max(case when counter_name = 'Average Latch Wait Time base' then cntr_value end)
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
'Transactions/sec',
'Active Transactions',
'SQL Cache Memory (KB)',
'Lock Requests/sec',
'Lock Timeouts/sec',
'Lock Waits/sec',
'Number of Deadlocks/sec',
'Average Wait Time (ms)',
'Average Wait Time base',
'Average Latch Wait Time (ms)',
'Average Latch Wait Time base') AND instance_name IN( '_Total' ,'')
SET ANSI_WARNINGS ON

SELECT  @AWT_DIV = case when (@AWT_Base - @LastAWT_Base) > 0 then (@AWT_Base - @LastAWT_Base) else 1 end ,
    @ALWT_DIV = case when (@ALWT_Base - @LastALWT_Base) > 0 then (@ALWT_Base - @LastALWT_Base) else 1 end

SELECT @RowText = '' + convert(varchar, (@TPS - @LastTPS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, @Active) + ', ' +
          convert(varchar, @SCM) + ', ' +
          convert(varchar, (@LRS - @LastLRS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@LTS - @LastLTS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@LWS - @LastLWS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@NDS - @LastNDS)/@RunIntervalSeconds) + ', ' +
          convert(varchar, (@AWT - @LastAWT)/@AWT_DIV) + ', ' +
          convert(varchar, (@ALWT - @LastALWT)/@ALWT_DIV)

SELECT  @LastTPS = @TPS,
    @LastLRS = @LRS,
    @LastLTS = @LTS,
    @LastLWS = @LWS,
    @LastNDS = @NDS,
    @LastAWT = @AWT,
    @LastAWT_Base = @AWT_Base,
    @LastALWT = @ALWT,
    @LastALWT_Base = @ALWT_Base

EXECUTE sp_OAMethod @OAFile, 'WriteLine', Null, @RowText

SET @LoopCounter = @LoopCounter + 1

END

--CLEAN UP
EXECUTE sp_OADestroy @OAFile
EXECUTE sp_OADestroy @OACreate
print 'Completed Logging Performance Metrics to file: ' + @FileName

END

GO

 

 

 

코드를 실행하면서 계속 permission denied in database 'master' 오류가 발생해서 시간이 조금 걸렸는데 다음 포스팅을 통해서 해결하였다!!!!!