Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a couple queries called from a C# .NET web application which are always fast for me (I am an local admin on the SQL Server) but for a group of users (domain group with required permissions), the query is incredibly slow to the point it times out in the application.

What would cause the exact same query to run differently for different users?

More info:

  • The query is inline SQL in the C# code, not a stored procedure
  • The app uses domain authentication and both the user and myself run the query through the app
  • Seems like the issue is different plans and one was cached so that is why it was different for different users. Something is affecting the cache because now the query is slow for me via the app and fast in SQL Server Management Studio.
share|improve this question
2  
Check the following questions. You might find you're in the same situation. Let's say first try this one and this other one. –  Marian Feb 8 '13 at 22:12
3  
What are the wait types (sys.dm_os_waiting_tasks) on the slow query(ies), and also what are the actual execution plans of each (your fast, their slow)? –  Thomas Stringer Feb 8 '13 at 22:26
2  
Agree with previous comments. My first thought would be parameter sniffing too. Checking to see if the plans are different should be the first step. –  Martin Smith Feb 8 '13 at 22:43
4  
If the parameters are the same (I'm assuming that's what's meant by exact same query), it shouldn't be parameter sniffing (users get a bad plan for the wrong parameter(s)), but rather users are getting different plans for the same parameter(s). It could be because of settings like quoted_identifier and arithabort, which you can compare in sys.dm_exec_sessions for the fast user and the slow user, or it could be because they have different default schemas and objects are referenced without the schema prefix. Parameter sniffing may still be involved (hence why one of them has a bad plan). –  Aaron Bertrand Feb 9 '13 at 0:48
1  
Could be different session settings too, preventing use of an indexed view, indexed computed column...and the like. See Slow in the Application, Fast in SSMS –  Paul White Feb 9 '13 at 2:00
show 5 more comments

3 Answers

If the parameters are the same (I'm assuming that's what's meant by exact same query), it shouldn't be parameter sniffing (users get a bad plan for the wrong parameter(s)), but rather users are getting different plans for the same parameter(s). It could be because of settings like quoted_identifier and arithabort, which you can compare in sys.dm_exec_sessions for the fast user and the slow user, or it could be because they have different default schemas and objects are referenced without the schema prefix. Parameter sniffing may still be involved (hence why one of them has a bad plan).

share|improve this answer
add comment

i've seen two reasons for this: 1, parameter sniffing 2, connection settings are different. If you run whoisactive, it will show you the different connection properties. I actually have a blog post on this, but i haven't cleaned up the company specific information from it. (nor have I enabled my blog yet) ;)

share|improve this answer
add comment

Try: Specify schema on every EXEC and table reference. E.g., EXEC dbo.MyProc

There could be conflicts (as Martin Smith suggests -- 'same default schema'?) or recompiles

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.