I have an application that runs ad-hoc queries to a remote SQL server. I'm running into a bizarre problem with the application taking significantly longer to return results when the application is running as a user versus a local administration (to the workstation).

I launched my application normally, and then launched a separate instance by right clicking > run as administrator.

I started profiling the database. Using the exact same steps with the exact same query, the 'user' application takes 15 seconds to return data. When I run the application 'as administrator' it takes only 3 seconds.

I can't find a reason why the same application using the same steps running the same queries is taking significantly longer to return results. I've run the test several times with similar discrepancies.

I can reproduce the issue using multiple workstation and multiple domain user accounts. Anytime the user runs the application 'as administrator' (invoking local admin), it runs much quicker.

Any insight would be helpful here, I'm completely stumped.

EDIT: The application authenticates to the database using SQL authentication in both cases (using the same credentials).

  • 2
    Is the query parameterised? What do the execution plans look like? Particularly the compiled vs runtime value of the parameters. Different logins can share each other's plans as long as they both use the same default schema or the query doesn't rely on implicit resolution and various other session options such as default language are the same. – Martin Smith Jul 10 '15 at 19:58
  • is optimize for adhoc enabled - sp_configure option ? – Kin Shah Jul 10 '15 at 20:41
  • Are all queries occurring over the network, or are the faster ones not over the network? – Max Vernon Jul 10 '15 at 22:15
  • Have you tried from different PC to rule out something pc specific? – Sir Swears-a-lot Jul 11 '15 at 9:12
  • Question by @Marek Cieślicki: Have you tried sql profiler to capture the traffic in both cases and compare them? – ypercubeᵀᴹ Jul 16 '15 at 0:12

After a few hours and some pow-wow time with my developers, we discovered the issue:

I typically uncheck login\logoff auditing in SQL profiler, but it happened to be the window I needed to peek on the cause. When running 'as admin' the application connects using TCP\IP.

When running as just a user, the app connects over Named Pipes. I'll still need to work out why, but in the mean time I can force the app to run in TCP\IP using a different connection string.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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