I have a select query that is executed by a hosted database application (ASP).
It is the same select query used by both groups (identical in every way).
They are both using the same application and same servers (SQL and Application).
Everything is identical except the end-users login details and the machine they are connecting from.
Regardless of the end-users login details, all queries are executed using the same SQL login. Yet, for some reason, the query consistently runs slower for some users than others.
Since it's coming from two different machines, you should check the connection settings on those machines to see if there are differences. The main differences that might lead to different plans are from the ANSI settings. What you can do is look at the execution plans themselves, in the first operator, SELECT/DELETE/UPDATE/INSERT. There you will find the settings that the plan is based on. You can compare the two to look for differences.
You'll also find a query hash and a query plan hash. With the hash values, you can validate differences between the plans, because maybe the queries aren't really identical (it's a possibility).
In the event you're dealing with bad parameter sniffing, that same location will show you compile time parameter values that could be leading to different execution plans.
That's where I'd start. You should get most of the information you need from there to determine exactly what's up with the queries.
answered Nov 15, 2012 at 10:05 AM
Grant Fritchey ♦♦
Where are you measuring the timings from?
If at the Client end then you could be suffering router issues or user desktop resource contention to name a couple of factors. Are the Clients on different subnets? Are there hosts files on the systems? Is your ASP server multi-NICed?
SQL Server has to be examined holistically.
answered Nov 15, 2012 at 02:05 PM