Sometimes, in the forums I’m following, I face some questions about different execution times between Sql Server Management Studio and ado.net executions.
Questions like “Why my SSMS queries run so fast and my ado.net queries are so slow?”. This is a scaring behavior that you can verify lots of times. Some application’s logs tell to us a too large timing. When you copy/paste the SQLCommand into a new query window using SSMS (also on the incriminated server) the execution time is very short.
This issue can be solved reading the interesting article “Slow in the Application, Fast in SSMS? Understanding Performance Mysteries” by Erland Sommarskog.
One of the most common reasons of this behavior is the ARITHABORT option. Ado.net defaults are different than Management studio settings:
“You might see where this is getting at. Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application. So there you have a likely answer to the initial question of this article. There are a few more possibilities that we will look into in the next chapter, but the by far most common reason for slow in the application, fast in SSMS in SQL 2005 and later is parameter sniffing and the different defaults for ARITHABORT.”
I strongly suggest to read this important article. It’s not a new article (2011) but I think that someone did not read it 🙂
Thanks to Erland.
Stay tuned! 🙂
Alessandro Alpi's Blog 