开发者社区> 问答> 正文

如何在存储过程记录执行SQL的日志,检查SQL执行结果?

如何在存储过程记录执行SQL的日志,检查SQL执行结果?

展开
收起
晓风瑟瑟 2021-10-13 23:29:12 954 0
2 条回答
写回答
取消 提交回答
  • 使用spool命令,spool以标准输出方式输出SQL执行结果,如下 spool D:\sql_result.sql select * from dual; spool off;

    2021-10-16 04:22:07
    赞同 展开评论 打赏
  • --某个库中存储过程平均耗时 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 );

    2021-10-14 09:52:57
    赞同 1 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
SQL Server在电子商务中的应用与实践 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载