2

I am tuning an SP and re-written that to optimize. Now I am comparing old code and new code in terms of timing using "setting statistics time on/off".

While setting time statistics off, my new code is performing well at least 4 times better than old code (Old code is taking 4 secs and new code is taking 1 secs to execute), but when I am setting time statistics On, my new code is taking approx 12 secs and old code is taking around 7 secs.

Why new code is performing badly after setting time stats on? Looks like there is some cost of time stats also and in my new code, that cost is very higher in comparison to old code. Am I right? If so what is that?

2

If there are user defined functions in your code, there can be significant overhead to setting STATISTICS TIME ON. See here.

More importantly, be REALLY careful when using scalar UDFs. They will inhibit parallelism for your query, and have a nasty habit of running once per row returned by your query.

The impact they have on STATISTICS TIME should be the least of your concerns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.