I have a query that performs differently depending upon the user executing it. This was first brought to my attention because an Excel spreadsheet was taking a long time to load data (Excel 2010, Sql Server 2008 R2). The query was awful, I rewrote it and got better performance. But when the user tried running it from Excel, still had bad performance. After a bit of head scratching I saw that the connection from Excel had a username/password specified, whereas when I ran it from SSMS I was logging in using my username via windows integrated security (and I am dbo). So, I tried a few things:
1. In two windows in the same SSMS, run the query simultaneously. One as me, the other using "Execute as Login =".
2. Start up two instances of SSMS. Connect in one as me, in the other as the specified login.
In any case, when I run the query as me, it takes about 7 1/2 minutes. When I run as the specified user, it takes 12+ minutes (today's measurements. Other days when I run as me I can have it run in as little as 2 minutes, but these times have been consistent today).
In any case, I am always executing the exact same statement (SELECT * FROM a table valued function) with the same parameters. The only difference is "who" is logged in.
Why would two different user logins have different performance profiles? What should I be looking at to track this down?
Thanks in advance.