Abdul Rahman (1/23/2013)
HiWe have simple Adhoc queries running from an application.
The query runs for hours and gets timed out.
At the same time I could see from Activity Monitor that it uses multiple threads.
That by itself isn't necessarily an issue. What wait types did you have besides CXPacket?
I even changed the Arithmetic Abort Enabled to True for the database but still its running for hours.
What did you hope to accomplish with that choice? Not being rude, we just don't know what you're seeing that you thought it would affect it.
The same query when run in SSMS finishes within 5-10 mintues and also uses only one thread.
This is telling. First, I'd check on all the settings that the connection is using vs. the ones you default to for SSMS. Connections also default setings, like SET ANSI_NULL on, things like that. This may help you locate part of your issue.
Secondly, I'd confirm that it's the EXACT same query by using profiler and trapping the query on its way in from the application. Very minor differences can make a huge difference. It's also an easy way to tell the set statements the connection will force during a connection.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA