sqlserver怎么查询某次数据库操作的耗时
– – – 解决方案 – – – – – – – – – -setstatisticStimeon;你的陈述setstatisticstimeoff;或使用SQLServerProfiler获取- – – 解决方案 – – – – – – – – – -跟踪SQLProfiler工具。或用MDW数据收集它。- – – 解决方案 – – – – – – – – – -SQLProfiler专门用于跟踪自己。据估计,难以估计每个DDL操作- – – 解决方案 – – – – – – – – – -历史数据是一个摘要值,更难以检查,一旦服务器重新启动,它将清除重新注册信息。 您可以使用此语句来计算统计信息。 如果您真的需要这种需要,您应该做一系列监控: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 )
转载于天翼云,如有侵权,请联系删除,谢谢