November 4, 2008 at 7:53 am
Could someone please pin point the cause of it? The exact same query finishes in a few seconds with windows auth, but does not finish in 5 mins with sql auth?
Thanks!
November 4, 2008 at 8:10 am
Which one did you run first? Perhaps the faster one is the second one and most of the data as well as the execution plan are already cached in memory. Also, are you seeing the exact same execution plan for both methods? Hate to ask the obvious, but are you sure you're connecting to the same instance/database/server etc?
-Luke.
November 4, 2008 at 8:20 am
Thanks, Luke. I was not exactly sure. The problem was reported by a developer. I just ran them side by side with either of the authen, they took roughly the same time.
November 4, 2008 at 9:45 am
Actually, I re-ran the query a few times with both authentication methods, with exactly the same execution plan, using windows auth does seem faster, about half of the time of sql auth.
November 4, 2008 at 9:52 am
Are you seeing any particular wait type? (check sys.dm_exec_requests)
Is one of the logins sysadmin and the other not?
Do they both have the same default schema?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 4, 2008 at 9:56 am
I have not checked the wait type, I will take a look.
When I run it using my windows account, I am system admin, the sql account is not a sysadmin, but when the developer ran it using her windows account, she is not sysadmin. Why does sysadmin matter?
Yes, the default schema is the same across the board.
November 4, 2008 at 10:12 am
sos_scheduler_yield is the wait type
November 4, 2008 at 10:26 am
KATHLEEN Y ZHANG (11/4/2008)
When I run it using my windows account, I am system admin, the sql account is not a sysadmin, but when the developer ran it using her windows account, she is not sysadmin. Why does sysadmin matter?
It doesn't normally, but there's a case where a sysadmin account will run queries really quick, but any other account runs queries slow. It has to do with an overly-large security token cache. Sysadmin accounts bypass that, but any other has to search through that for cached tokens. The wait type visible for that is CMEMTHREAD though, non a scheduler yield.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply