使用spool命令,spool以标准输出方式输出SQL执行结果,如下 spool D:\sql_result.sql select * from dual; spool off;
--某个库中存储过程平均耗时 SELECT TOP ( 25 ) p. name AS [SP Name ] , qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] , qs.total_elapsed_time , qs.execution_count , ISNULL (qs.execution_count / DATEDIFF( Second , qs.cached_time, GETDATE()), 0) AS [Calls/ Second ] , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime] , qs.total_worker_time AS [TotalWorkerTime] , qs.cached_time FROM sys.procedures AS p WITH ( NOLOCK ) INNER JOIN sys.dm_exec_procedure_stats AS qs WITH ( NOLOCK ) ON p.[object_id] = qs.[object_id] WHERE qs.database_id = DB_ID() ORDER BY avg_elapsed_time DESC OPTION ( RECOMPILE );
--这是普通查询语句的 SELECT qs.execution_count , qs.total_rows , qs.last_rows , qs.min_rows , qs.max_rows , qs.last_elapsed_time , qs.min_elapsed_time , qs.max_elapsed_time , SUBSTRING(qt.TEXT, qs.statement_start_offset / 2 + 1, ( CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2) AS query_text FROM sys.dm_exec_query_stats AS qs WITH ( NOLOCK ) CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.execution_count DESC OPTION ( RECOMPILE );
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。