I have a web page with a database lookup that contains multiple optional parameters. When I select a couple of parameters and run the query, it returns in less than two seconds. However, when another person logs in, and runs the same query with the same parameters on my machine, it takes 30+ seconds to come back. Web browser doesn't matter, it behaves the exact same way in multiple different browsers for each user. Both users have all the needed permissions on the database to run the query as they go through the same database connection using tcp/ip. At this point, the only difference is who is actively logged in, which doesn't make any sense to me. Does anyone have any ideas as to why the person would make a difference when everything else appears to be the exact same? Thanks
There could be different reasons - I'll speculate some of them:
- You could have row level security and have different rows returned for different callers
- You could have resource governor enabled with different resource sizings for each user based on a login trigger
Perhaps the different users are on different machines and there are subtle differences in how the tests are run - for example, one user is always first and gets to compile the query not in the cache or there is a parallel plan that is parallel for one user and not the other due to load on the machine at the time
Maybe the queries are slightly different in text on the two machines and you are actually getting different query plans somehow for the two cases.
It usually helps to either do a profiler or XEvent trace to look at the exact runtimes and showplans at runtime to get insight into why one is slower. On SQL 2016+, Query Store makes it easier (as is the case on SQL Azure), so if you have the option to try the repro on a later version it might help you find the answer. Good luck!