bedbmsguru

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

  

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

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 )

SELECT
SERVERPROPERTY('ServerName') AS sql_instance,
s.session_id
,r.STATUS
,CONVERT(varchar,r.start_time,20) start_time
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time 
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time 
,r.open_transaction_count
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id != @@SPID
ORDER BY start_time

  

alter  proc sp_list_server_property

DECLARE @props TABLE ( propertyname sysname PRIMARY KEY)

INSERT INTO @props( propertyname )

SELECT 'BuildClrVersion'

SELECT 'Collation'

SELECT 'CollationID'

SELECT 'ComparisonStyle'

SELECT 'ComputerNamePhysicalNetBIOS'

SELECT 'Edition'

SELECT 'HadrManagerStatus'

SELECT 'EngineEdition'

SELECT 'InstanceName'

SELECT 'IsClustered'

SELECT 'IsFullTextInstalled'

SELECT 'IsIntegratedSecurityOnly'

SELECT 'IsSingleUser'

SELECT 'LCID'

SELECT 'LicenseType'

SELECT 'MachineName'

SELECT 'NumLicenses'

SELECT 'ProcessID'

SELECT 'ProductVersion'

SELECT 'ProductLevel'

SELECT 'ResourceLastUpdateDateTime'

SELECT 'ResourceVersion'

SELECT 'ServerName'

SELECT 'SqlCharSet'

SELECT 'SqlCharSetName'

SELECT 'SqlSortOrder'

SELECT 'SqlSortOrderName'

SELECT 'FilestreamShareName'

SELECT 'FilestreamConfiguredLevel'

SELECT 'FilestreamEffectiveLevel'

SELECT propertyname , SERVERPROPERTY ( propertyname ) FROM @props

 UNION ALL

SELECT 'EditionID' AS  propertyname,

                                 CASE SERVERPROPERTY ( 'EditionID' )

                                                 WHEN 1804890536 THEN 'Enterprise'

                                                 WHEN 1872460670 THEN 'Enterprise With CORE Base Liserence'

                                                 WHEN 610778273 THEN 'Enterprise Evaluation'

                                                 WHEN 284895786 THEN 'Business Intelligence'

                                                 WHEN - 2117995310 THEN 'Developer'

                                                 WHEN - 1592396055 THEN 'Express'

                                                 WHEN - 133711905 THEN 'Express with Advanced Services'

                                                 WHEN - 1534726760 THEN 'Standard'

                                                 WHEN 1293598313 THEN 'WEB'

                                 END AS EditionId

UNION ALL                                                                                                           

SELECT 'IsHadrEnabled' AS propertyname,

                                 CASE SERVERPROPERTY ( 'IsHadrEnabled' )

                                                 WHEN 0 THEN 'AlwaysOn 가용성 그룹 기능을 사용하지 않습니다.'

                                                 WHEN 1 THEN 'AlwaysOn 가용성 그룹 기능을 사용합니다'

                                 END AS IsHadrEnabled

UNION ALL                                                                                                           

SELECT 'HadrManagerStatus' AS propertyname,

                                 CASE SERVERPROPERTY ( 'HadrManagerStatus' )

                                                 WHEN 0 THEN '시작되지 않았습니다. 통신 보류 중입니다.'

                                                 WHEN 1 THEN '시작되어 실행 중입니다.'

                                                 WHEN 2 THEN '시작되지 않고 실패했습니다.'

                                 END AS HadrManagerStatus