Divide by zero in calculated items

[hadida]

I try to monitor SQL server. I have created calculated item and met problem devide by zero.

last(perf_counter["\SQLServer:SQL Statistics\SQL Compilations/sec"]) / last(perf_counter["\SQLServer:SQL Statistics\SQL Re-Compilations/sec"])

How can I resolve it?

Thanks in advance

[timbo]

Hi hadida,

Try using avg() instead of last(), for example (note the backslashes before the quotes):

avg("perf_counter[\"\SQLServer:SQL Statistics\SQL Compilations/sec\"]",15m) /avg("perf_counter[\"\SQLServer:SQL Statistics\SQL Re-Compilations/sec\"]",15m)

You don't need to use a 15min average, you could try 5min - as long as you don't receive zeros for the entire period you'll be fine.


For extra credit:
I noticed you're monitoring "SQL Statistics\SQL Compilations/sec", I'm actually using this metric too, but to calculate the "Batches executed per compile (ratio)". Here's the Formula:

avg("perf_counter[\"\SQLServer:SQL Statistics\Batch Requests/Sec\"]",15m) /avg("perf_counter[\"\SQLServer:SQL Statistics\SQL Compilations/Sec\"]",15m)

I was inspired by this article (and the comments):

See: SQL Compilations/Sec
http://www.databasejournal.com/featu...erformance.htm

Hope this helps!

-Timbo