StoredProcedure 調查

SELECT P.name
     , P.object_id
     , PS.cached_time
     , PS.execution_count
     , PS.total_worker_time
     , PS.total_worker_time/PS.execution_count AS avg_worker_time
     , PS.last_worker_time
     , PS.min_worker_time
     , PS.max_worker_time
     , PS.total_elapsed_time
     , PS.total_elapsed_time/PS.execution_count AS avg_elapsed_time
     , PS.last_elapsed_time
     , PS.min_elapsed_time
     , PS.max_elapsed_time
     , PS.total_physical_reads
     , PS.total_physical_reads / PS.execution_count as avg_physical_reads
     , PS.last_physical_reads
     , PS.min_physical_reads
     , PS.max_physical_reads
     , PS.total_logical_writes
     , PS.total_logical_writes / PS.execution_count as avg_logical_writes
     , PS.last_logical_writes
     , PS.min_logical_writes
     , PS.max_logical_writes
     , PS.last_execution_time
FROM sys.dm_exec_procedure_stats PS RIGHT OUTER JOIN sys.procedures P ON PS.object_id = P.object_id
ORDER BY PS.total_worker_time/PS.execution_count DESC;
GO


SELECT OBJECT_NAME(I.object_id) AS TableName
    , I.name AS IndexName
    , case I.Type when 1 then N'Clustered' when 2 then N'NonClustered' end AS type
    , user_scans
    , user_seeks
    , user_lookups
    , user_updates
    , last_user_scan
    , last_user_seek
    , last_user_lookup
    , last_user_update
FROM sys.dm_db_index_usage_stats IUS RIGHT OUTER JOIN sys.indexes I ON IUS.object_id = I.object_id AND IUS.index_id = I.index_id 
WHERE I.object_id > 100;
GO