asked on

Divide by zero error encountered.

Hello,

When I execute the following query, the error Divide by zero error encountered. is generated :
SELECT Object_name(qt.objectid),
qs.execution_count AS
[Execution Count],
qs.execution_count / Datediff(second, qs.creation_time, Getdate()) AS
[Calls/Second],
qs.total_worker_time / qs.execution_count AS
[AvgWorkerTime],
qs.total_worker_time AS
[TotalWorkerTime],
qs.total_elapsed_time / qs.execution_count AS
[AvgElapsedTime],
qs.max_logical_reads,
qs.max_logical_writes,
qs.total_physical_reads,
Datediff(minute, qs.creation_time, Getdate()) AS
[Age in Cache]
FROM sys.dm_exec_query_stats AS qs
CROSS apply sys.Dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[dbid] = Db_id()
ORDER BY qs.execution_count DESC
OPTION (recompile);

Why?

Thanks

lcohan

Please check all divisions and make sure none of those columns or record set listed below can return NULL. Add ISNULL() around them or COALESCE()
...
/ Datediff(second, qs.creation_time, Getdate()) AS [Calls/Second],
...
/ qs.execution_count AS ...
....
/ qs.execution_count AS [AvgElapsedTime],

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial