Friday, December 19, 2008

Arithmetic Abort Enabled

I have a stored procedure in SQL Server 2005 which takes a lot of a time to run from my .Net 2.0 Framework application and sometimes even gives a "Timeout expired".
Executing the same stored procedure in SQL Server Management Studio gives the result in less than 2 seconds! Why??

The answer lays in one single setting. When SQL Server Management Studio connects to the database it sets the "Arithmetic Abort Enabled" on. This option triggers the database to create new executions plans for the stored procedures.

Setting this option improved the performance of my .Net 2.0 Framework application!