This question sounds really easy! The query performance depends essentially on the execution plan, which in its turn isn’t dependent on the client. Therefore, theoretically, running a query through two different clients (i.e. ASP.Net and SQL Server Management Studio) should reveal similar performances.
But before we start answering this question, I would like to share with you the scenario which led me to deal with this question. A client of ours asked for Madeira’s help to deal with their very slow web site. We first used Profiler to identify the source of the problem. Our conclusions were clear but at the same time very confusing. The website (written in ASP.Net 2.0) called often a specific query which took almost a minute to return the data. However, running the same query directly through SQL Server Management Studio took only 15 seconds!
Let’s try and analyze the different stages that lead from the client and until the Database engine returns the data in order to find the cause of our problem. As you all know clients don’t connect directly to the server, they use a provider to do so. Performance can change from one provider to another or even from two different versions of the same provider. Remember, for better resource management and performance, it’s a good idea to use the latest versions of your provider. But yet, is it possible that two different clients running the same query and using the same provider would result with different performance?
You’re right, the answer is yes! Let’s continue and analyze the chain. The provider passes on the query to the Database Engine which verifies if a compiled version of the query residues in the memory. However, the process of which the Database Engine decides if it can use the compiled version is pretty complex. The motivation is of course to use the compiled version but the Engine must be sure the data returned is perfectly similar in both cases (compiled query and the current query). To do so, a set of criteria have to match in order to be sure the returned data is the same. Among those criteria, are the Session settings (ANSI_NULLS, ARITHABORT etc…) Thus, if a session runs a query with different session settings than the compiled version, the engine will have to recompile the query and maybe use a different execution plan and resulting with a different performance. This is exactly what happened in our case. SQL Server Management Studio has set by default ARITHABORT = 1 and ASP.Net has set ARITHABORT = 0. (for info on arithabort see: http://msdn.microsoft.com/en-us/library/aa259212(SQL.80).aspx). All was left to do in order to fix the problem was to set ARITHABORT = 1 while calling the query through ASP.Net.
P.S You can check if ARITHABORT = 1 in SQL Server Management Studio’s options (Query Execution > SQL Server > Advanced). In the current picture, you can see it’s set to 1.