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.
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
Supergibbs
  • 211
  • 2
  • 6

4 Answers4

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

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
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) ;)

Community
  • 1
rottengeek
  • 690
  • 6
  • 17
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.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
Rob Kraft
  • 1
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

Kip Bryan
  • 101