/************************************************************************************************
-- sp_missing_index9 
-- @top_num : Top() °ª(default=100)
-- ´©¶ôµÈ À妽º¿¡ ´ëÇÑ Á¤º¸ º¸±â
************************************************************************************************/
USE master;
GO

IF OBJECT_ID(N'dbo.sp_missing_index9') IS NOT NULL
	DROP PROCEDURE sp_missing_index9;
GO

CREATE PROC sp_missing_index9 
    @top_num INT = 100
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- ±â ÇÁ·Î½ÃÀú´Â http://hyoksong.tistory.com ¿¡¼­ ÂüÁ¶ µÇ¾ú´Ù.

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql)
SELECT TOP (@top_num)
       db_name "DATABASE"
     , sp_name "PROCEDURE"
     , sp_text "PROCEDURE_TEXT"
     , statement_text "MISSING_INDEX_TEXT"
     , X.MISSING_INDEX_TABLE
     , X.MISSING_INDEX_IMPACT
     , creation_time "CREATION_TIME"
     , ExecutionCount "EXECUTION_COUNT"
FROM
(SELECT db_name(qt.dbid) AS 'db_name'
      , qt.text AS 'sp_text'
      , substring(qt.text, (qs.statement_start_offset/2)+1
                 , ((case qs.statement_end_offset
                    when -1 then datalength(qt.text)
                    else qs.statement_end_offset
                    end - qs.statement_start_offset)/2) + 1) as statement_text
      , qs.creation_time
      , qs.execution_count AS 'ExecutionCount'
      , cast(qp.query_plan as xml) as query_plan
      , OBJECT_NAME(qp.objectid,qp.dbid) as sp_name
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
 CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,qs.statement_start_offset,qs.statement_end_offset) qp
) Y
CROSS APPLY 
(SELECT c.value('(./@Impact)[1]','float') "MISSING_INDEX_IMPACT"
      , c.value('(./sql:MissingIndex/@Database)[1]','varchar(100)') + '.'+
        c.value('(./sql:MissingIndex/@Schema)[1]','varchar(100)') + '.'+
        c.value('(./sql:MissingIndex/@Table)[1]','varchar(100)') "MISSING_INDEX_TABLE"
 FROM Y.query_plan.nodes('//sql:MissingIndexGroup') B(C)
) X

RETURN (0); --sp_missing_index9;
GO
                       
EXEC sp_MS_marksystemobject N'dbo.sp_missing_index9'
GO
  
GRANT EXECUTE ON dbo.sp_missing_index9 TO PUBLIC
GO


/************************************************************************************************
-- sp_procedure_info9 
-- @top_num : Top() °ª(default=100), @order_num : Á¤·Ä ±âÁØ °ª
-- ÀúÀå ÇÁ·Î½ÃÀú ¼º´É Á¤º¸ º¸±â
************************************************************************************************/
USE master;
GO

IF OBJECT_ID(N'dbo.sp_procedure_info9') IS NOT NULL
	DROP PROCEDURE sp_procedure_info9;
GO

CREATE PROC sp_procedure_info9 
    @top_num INT = 100
  , @order_num TINYINT = 0
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT TOP (@top_num)
       DB_NAME(st.dbid) "DATABASE"
     , object_schema_name(st.objectid, dbid) "SCHEMA"
     , OBJECT_NAME(st.objectid, dbid) "PROCEDURE"
     , SUM(qs.execution_count) "EXEC_cnt"
     , CAST(SUM(qs.total_worker_time) / (SUM(qs.execution_count) * 1.0)
            AS NUMERIC(20,2)) "AVG_CPU_ms"
     , CAST(SUM(qs.total_elapsed_time) / SUM(execution_count)
            AS NUMERIC(20,2)) "AVG_ELAPSED_ms"
     , SUM(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) 
       / SUM(execution_count) "AVG_TOTAL_io"
     , CAST(SUM(qs.total_physical_reads) / (SUM(execution_count) * 1.0)
            AS NUMERIC(20,2)) "AVG_PHYSICAL_READ_io"
     , CAST(SUM(qs.total_logical_reads) / (SUM(execution_count) * 1.0)
            AS NUMERIC(20,2)) "AVG_LOGICAL_READ_io"
     , CAST(SUM(qs.total_logical_writes) / (SUM(execution_count) * 1.0)
            AS NUMERIC(20,2)) "AVG_LOGICAL_WRITE_io"          
FROM sys.dm_exec_cached_plans cp 
INNER JOIN sys.dm_exec_query_stats qs 
ON cp.plan_handle=qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL 
AND cp.objtype='proc'
GROUP BY DB_NAME(st.dbid)
       , object_schema_name(objectid,st.dbid)
       , OBJECT_NAME(objectid,st.dbid)
ORDER BY CASE @order_num WHEN 0 THEN SUM(qs.execution_count)
                         WHEN 1 THEN SUM(qs.total_worker_time) 
                                     / (SUM(qs.execution_count) * 1.0)
                         WHEN 2 THEN SUM(qs.total_elapsed_time) 
                                     / SUM(execution_count)
                         WHEN 3 THEN SUM(qs.total_logical_reads) 
                                     / (SUM(execution_count) * 1.0)
                         WHEN 4 THEN SUM(qs.total_logical_writes) 
                                     / (SUM(execution_count) * 1.0)
         END DESC
       , DB_NAME(st.dbid)
       , object_schema_name(st.objectid,dbid)
       , OBJECT_NAME(st.objectid,dbid);


PRINT '** Á¤·Ä ±âÁØ **'
PRINT '0 : EXECUTION_COUNTN'
PRINT '1 : AVERAGE CPU TIME'
PRINT '2 : AVERAGE ELAPSED TIME'
PRINT '3 : AVERAGE LOGICAL READS IO'
PRINT '4 : AVERAGE LOGICAL WIRTES IO'

RETURN (0); --sp_procedure_info9;
GO
                       
EXEC sp_MS_marksystemobject N'dbo.sp_procedure_info9';
GO
  
GRANT EXECUTE ON dbo.sp_procedure_info9 TO PUBLIC;
GO


/************************************************************************************************
-- sp_index_usage9
-- ÇØ´ç Å×À̺í À妽ºÀÇ PAGE IO ¿¹»ó
************************************************************************************************/
USE master;
GO

IF OBJECT_ID(N'dbo.sp_index_usage9') IS NOT NULL
	DROP PROCEDURE sp_index_usage9;
GO

CREATE PROC sp_index_usage9 
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT SCHEMA_NAME(SCHEMA_ID) "SCHEMA"
     , OBJECT_NAME(i.OBJECT_ID) "TABLE"
     , ISNULL(i.name, N'(Heap)') "INDEX"
     , p.index_id "INDEX_ID"
     , ROWS "ROWS"
     , au.type_desc "TYPE_DESCRIPTION"
     , total_pages "TOTAL_PAGES"
     , used_pages "USED_PAGES"
     , data_pages "DATA_PAGES"
FROM sys.indexes i 
INNER JOIN sys.tables t
ON i.OBJECT_ID = t.OBJECT_ID
AND t.TYPE = N'U'
INNER JOIN sys.partitions p 
ON p.OBJECT_ID=i.OBJECT_ID 
AND p.index_id=i.index_id
INNER JOIN sys.allocation_units au
ON p.partition_id=au.container_id 
ORDER BY 1,2;

RETURN (0); --sp_index_usage9;
GO
                       
EXEC sp_MS_marksystemobject N'dbo.sp_index_usage9';
GO
  
GRANT EXECUTE ON dbo.sp_index_usage9 TO PUBLIC;
GO