11

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.
Improve this question
9
  • 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, 2013 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, 2013 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, 2013 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, 2013 at 0:48
  • 1
    RE: Your edit do you have the same default schema as the other users? Have you captured the execution plans for slow and fast runs yet?
    – Martin Smith
    Feb 12, 2013 at 9:49

4 Answers 4

Reset to default
5

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).

Improve this answer
3

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) ;)

Improve this answer
0

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

Improve this answer
0

This appears to be a bug in SQL Server. I experience this bug with SQL Server 2008. I have not tested new versions. I can log in as an admin and run this query and get a response in 0 seconds:

select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES ORDER BY ROUTINE_NAME

Then I log in as a user with fewer permissions, run the exact same query and the response takes 45 seconds.

This is consistent over and over. If I bounce back and forth between my two query windows, one for the admin and one for the non-admin, the non-admin always takes about 45 seconds and the admin takes 0 seconds.

Improve this answer
2
  • As asked in the comments to the question - do both users have the same default database, and are the queries being executed in the same database? And, can you point to some sort of documentation that states this is a bug, or is it your opinion? Not saying you're wrong, just looking for something beyond an anecdote.
    – RDFozz
    Jul 3, 2018 at 15:34
  • The problem you've identified in your answer appears to not be repeatable on SQL Server 2008. select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES ORDER BY ROUTINE_NAME consistently returns data immediately for a non-SA login that has no explicity granted rights at all.
    – Hannah Vernon ♦
    Jul 3, 2018 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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