'SQL SERVER' 카테고리의 다른 글
| bufferpool(버퍼풀) 메모리 사용량 (0) | 2018.10.27 |
|---|---|
| 통계 업데이트 날짜 조회하기 (0) | 2018.10.27 |
| 실행중인 쿼리 확인 (0) | 2018.10.27 |
| SERVER property를 일괄로 보여주는 procedure (0) | 2018.10.27 |
| Index 생성이 안되어 있는 Foreign key 찾기 (0) | 2018.10.27 |
2018. 10. 27. 22:34
SQL SERVER
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
,sum( execution_count) AS Execution_Count
,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
,sum( total_physical_reads) / sum (execution_count) AS AVG_PHYSICAL_READS
, GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
,OBJECT_SCHEMA_NAME( object_id,database_id )
,OBJECT_NAME( object_id,database_id )
ORDER BY AVG_LOGICAL_READS DESC
--정기적 체크를 위해 Batch Job으로 만들경우의 소스
CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT](
[DBName] [nvarchar] (128) NULL,
[SCHEMA_NAME] [nvarchar] (128) NULL,
[OBJECT_NAME] [nvarchar] (128) NULL,
[Execution_Count] [bigint] NULL,
[AVG_CPU] [bigint] NULL,
[AVG_ELAPSED] [bigint] NULL,
[AVG_LOGICAL_READS] [bigint] NULL,
[AVG_LOGICAL_WRITES] [bigint] NULL,
[AVG_PHYSICAL_READS] [bigint] NULL,
[write_time] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo]. [TBL_CHECK_PROC_EXECUTION_COUNT_TEMP] (
[DBName] [nvarchar] (128) NULL,
[SCHEMA_NAME] [nvarchar] (128) NULL,
[OBJECT_NAME] [nvarchar] (128) NULL,
[Execution_Count] [bigint] NULL,
[AVG_CPU] [bigint] NULL,
[AVG_ELAPSED] [bigint] NULL,
[AVG_LOGICAL_READS] [bigint] NULL,
[AVG_LOGICAL_WRITES] [bigint] NULL,
[AVG_PHYSICAL_READS] [bigint] NULL,
[write_time] [datetime] NOT NULL
) ON [PRIMARY]
create procedure usp_chk_proc_execute_count
AS
SET NOCOUNT ON;
INSERT INTO TBL_CHECK_PROC_EXECUTION_COUNT (DBName, [SCHEMA_NAME], [OBJECT_NAME], Execution_Count,
AVG_CPU, AVG_ELAPSED, AVG_LOGICAL_READS, AVG_LOGICAL_WRITES, AVG_PHYSICAL_READS, write_time)
SELECT A .DBName, B. [SCHEMA_NAME], B.[OBJECT_NAME] , B .Execution_Count - A .Execution_Count AS Execution_Count
,B. AVG_CPU,B .AVG_ELAPSED, B. AVG_LOGICAL_READS, B.AVG_LOGICAL_WRITES ,B. AVG_PHYSICAL_READS
, getdate () as write_time
FROM TBL_CHECK_PROC_EXECUTION_COUNT_TEMP AS A
inner JOIN
(
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
,sum( execution_count) AS Execution_Count
,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
,sum( total_physical_reads) / sum (execution_count) AS AVG_PHYSICAL_READS
, GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
,OBJECT_SCHEMA_NAME( object_id,database_id )
,OBJECT_NAME( object_id,database_id )
HAVING sum (execution_count) > 50000
)AS B
ON A .DBName = B .DBName
AND A .[OBJECT_NAME] = B .[OBJECT_NAME]
truncate table TBL_CHECK_PROC_EXECUTION_COUNT_TEMP
insert into TBL_CHECK_PROC_EXECUTION_COUNT_TEMP
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME( database_id)END AS DBName
,OBJECT_SCHEMA_NAME( object_id,database_id ) AS [SCHEMA_NAME]
,OBJECT_NAME( object_id,database_id )AS [OBJECT_NAME]
,sum( execution_count) AS Execution_Count
,sum( total_worker_time) / sum (execution_count) AS AVG_CPU
,sum( total_elapsed_time) / sum (execution_count) AS AVG_ELAPSED
,sum( total_logical_reads) / sum (execution_count) AS AVG_LOGICAL_READS
,sum( total_logical_writes) / sum (execution_count) AS AVG_LOGICAL_WRITES
,sum( total_physical_reads) / sum (execution_count) AS AVG_PHYSICAL_READS
, GETDATE () AS write_time
FROM sys .dm_exec_procedure_stats
group by
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME (database_id) END
,OBJECT_SCHEMA_NAME( object_id,database_id )
,OBJECT_NAME( object_id,database_id )
--ORDER BY AVG_LOGICAL_READS DESC