ArithAbort is your man!
If ARITHABORT is not set to on, we sometimes get performance problems with connections from the website (because UDL connections (really OLEDB) have ARITHABORT set to off). When this setting is off, I suspect that the optimizer decides to do serial scans in the selects of the nested functions instead of indexed (or more optimal) reads.
We are OK with ARITHABORT set to on because the math involved should NOT cause an arithmetic exception. And, even if it did, we want to fix it.
BTW the default for ARITHABORT in SSMS is on. That is why these queries are always faster when trying execute them in a query window.
To see more, http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx
No comments:
Post a Comment