查询SQL Server存储过程的执行信息(转)

create VIEW view_job_info as
SELECT b.[name] [Job名称],CASE WHEN b.enabled=1 THEN '启用' ELSE '禁用' END [是否启用]
, a.step_name [步骤名称],a.command
FROM msdb.dbo.sysjobsteps a INNER JOIN 
msdb.dbo.sysjobs b ON a.job_id=b.job_id

SELECT b.*,
OBJECT_NAME(object_id, database_id) 存储过程名, 
d.cached_time 编译时间, d.last_execution_time 最近一次执行时间ms, 
d.total_elapsed_time/1000 总花费时间ms, 
d.total_elapsed_time/d.execution_count*1000 AS 平均执行时间ms,
d.last_elapsed_time 最近一次执行花费时间ms, d.execution_count 执行次数
FROM sys.dm_exec_procedure_stats AS d INNER JOIN view_job_info b ON b.command LIKE 

'%'+OBJECT_NAME(object_id, database_id) +'%'
where OBJECT_NAME(object_id, database_id) IN (SELECT name FROM sys.objects WHERE  
TYPE='p' 
and modify_date>'2008-2-24 17:32:50')
ORDER BY 6 DESC;

执行sql次数和逻辑次数

 想通过数据库层分析sql server系统性能,以下代码可以用来分析系统运行一段时间后，那些语句是系统忙的sql语句

就是在测量功能时，先以下命令清除sql server的缓存： 

dbcc freeProcCache 

 SELECT  creation_time  N'语句编译时间'

        ,last_execution_time  N'上次执行时间'

        ,total_physical_reads N'物理读取总次数'

        ,total_logical_reads/execution_count N'每次逻辑读次数'

        ,total_logical_reads  N'逻辑读取总次数'

        ,total_logical_writes N'逻辑写入总次数'

        , execution_count  N'执行次数'

        , total_worker_time/1000 N'所用的CPU总时间ms'

        , total_elapsed_time/1000  N'总花费时间ms'

        , (total_elapsed_time / execution_count)/1000  N'平均时间ms'

        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset 

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END 

            - qs.statement_start_offset)/2) + 1) N'执行语句'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset 

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END 

            - qs.statement_start_offset)/2) + 1) not like '%fetch%'

ORDER BY  total_elapsed_time / execution_count DESC;

执行计划重用次数

select total_elapsed_time / execution_count 平均时间,total_logical_reads/execution_count 逻辑读,

usecounts 重用次数,SUBSTRING(d.text, (statement_start_offset/2) + 1,

         ((CASE statement_end_offset 

          WHEN -1 THEN DATALENGTH(text)

          ELSE statement_end_offset END 

            - statement_start_offset)/2) + 1) 语句执行 from sys.dm_exec_cached_plans a

cross apply sys.dm_exec_query_plan(a.plan_handle) c

,sys.dm_exec_query_stats b

cross apply sys.dm_exec_sql_text(b.sql_handle) d

where a.plan_handle=b.plan_handle and total_logical_reads/execution_count>4000

ORDER BY total_elapsed_time / execution_count DESC; 

索引使用效率评估

  user_seeks : 通过用户查询执行的搜索次数。 

 个人理解： 此统计索引seek的次数

user_scans: 通过用户查询执行的扫描次数。 
 个人理解：此统计表扫描的次数，无索引配合
user_lookups: 通过用户查询执行的查找次数。 
 个人理解：用户通过索引查找，在使用RID或聚集索引查找数据的次数，对于堆表或聚集表数据而言
和索引配合使用次数
user_updates:  通过用户查询执行的更新次数。 
 个人理解：索引或表的更新次数

--- 使用很少的索引排在最先
declare @dbid int
select @dbid = db_id()
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
            , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
            sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id

order by (user_seeks + user_scans + user_lookups + user_updates) asc