How to debug issues with differing execution times in different contexts.

Question

The following question seems to be haunting me more consistently than most other questions recently. What kinds of things would you suggest I suggest that they look for when trying to debug "performance issues" like this?

ok, get this - running this in query analyzer takes < 1 second

exec usp_MyAccount_Allowance_Activity '1/1/1900', null, 187128

debugging locally, this takes 10 seconds:

DataSet allowanceBalance =
    SqlHelper.ExecuteDataset(
        WebApplication.SQLConn(), 
        CommandType.StoredProcedure, 
        "usp_MyAccount_Allowance_Activity",
        Params);

same parameters

Answer 1

Horrible question to answer really - code in debugger vs code not in debugger introduces all manner of Heisenbug timing problems, most of which you'll never know about, into the soup of things that can muck things up for you.

Debuggers tend to put their fingers in all the tasty places that may affect performance.

• Debug Events. The debugger gets special events during the application load, execution, dll load/unload, shutdown. The debugger will do whatever it wants in these events. That will be a source of slowdown.
• Debug Output. OutputDebugString() and all the code that uses it (the trace output in .Net, for example) suddenly become active. This is slow.
• The HeapAlloc() family of functions, when run under a debugger start to check for all sorts of heap inconsistencies, which consumes more time.
• If you have Symbol Discovery turned on, there may be delays as various Symbol Servers are queried for symbols and downloaded if required (you'll notice the delay if they are downloaded).