SQL Query runs slow for specific user, fast for another user.
Our company sells a product that uses a MS SQL Server backend. Our product contains a tool that runs a query to extract billing information. Two users run this tool remotely on a terminal server. One user can run the tool and the results are returned in less than a minute. When the other user runs the same tool the query can take up to five minutes. Testing with the same query criteria by either user's account with sequential non-concurrent logins yields the same delay for this one specific user. The only difference in their account profiles is that the account that gets a fast response is a member of domain admins and the account that gets a slow response is a standard domain user. This is for one particular client running SQL Server 2008 R2 which is hosted on a dedicated server. The demand load on the SQL server is low to moderate the majority of the time. Testing with both accounts using RDP from the same computer or different computers at different locations produces the same results. I suspect the problem is with the SQL backend and possibly involves authentication. The SQL Server is set to use Windows Authentication and connection is via TCP/IP.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Would still like some more detail on your database, and explain plans from both users. :)
Do either of these two users own any/all of the objects being queried? Are views being used over the data?
What I'm looking for is a condition where there is a table that is owned by user A and a view with the same name as User A's table that is used by user B.
user A owns the table masterdata
user B could be accessing *UserA.masterdata* via a view
CREATE VIEW Userb.masterdata AS
SELECT * from Usera.masterdata where name = 'ME';
SELECT * FROM masterdata WHERE date < current_date - 100;
When UserA runs the query above, the table is accessed directly, using any appropriate index.
When userB runs the query, the index is used to create the subset of rows that is the result of the view. The result is then scanned via a full table scan to return the rows that have the correct date. If UserB has a lot of rows, this takes a lot of time.
Kent
Do either of these two users own any/all of the objects being queried? Are views being used over the data?
What I'm looking for is a condition where there is a table that is owned by user A and a view with the same name as User A's table that is used by user B.
user A owns the table masterdata
user B could be accessing *UserA.masterdata* via a view
CREATE VIEW Userb.masterdata AS
SELECT * from Usera.masterdata where name = 'ME';
SELECT * FROM masterdata WHERE date < current_date - 100;
When UserA runs the query above, the table is accessed directly, using any appropriate index.
When userB runs the query, the index is used to create the subset of rows that is the result of the view. The result is then scanned via a full table scan to return the rows that have the correct date. If UserB has a lot of rows, this takes a lot of time.
Kent
ASKER
User A is dbo, User B is normal user with all permissions public.
Same query every time with one user doing the testing logging in with both accounts.
Explain data does not change. Query does not change.
Same query every time with one user doing the testing logging in with both accounts.
Explain data does not change. Query does not change.
Please post the Execution plan in each case.
I've got about 10 questions just a few posts up. Can you answer them? They really could be relevant.
Thanks,
Kent
Thanks,
Kent
And I have just got 2 and I still have not got a clear answer. :)
ASKER
Thanks for the help. I appreciate all the responses and I'm splitting the points between you. The problem was rendered moot by the implementation of a newer version of a C# .NET custom query reporting tool. The suggestions and the questions helped us pinpoint issues in our application and we modified both the backend and the query tool to correct for the problems we were having.
ASKER