Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adhoc query timing out


Adhoc query timing out

Author
Message
Abdul Rahman
Abdul Rahman
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1297
Hi
We 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.
I even changed the Arithmetic Abort Enabled to True for the database but still its running for hours.

The same query when run in SSMS finishes within 5-10 mintues and also uses only one thread.

I am pretty confused on fixing this behaviour.
Any insight would be helpful.

Thanks.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
Abdul Rahman (1/23/2013)
Hi
We 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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Abdul Rahman
Abdul Rahman
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1297
The wait types are CXPacket only.
I found online that having Arithabort ON could resolve this issue and tried that.

I did ran Profiler and yes it is the same query, which we had to kill as it was running forever.


It's also an easy way to tell the set statements the connection will force during a connection.


I am not sure on where to check this on the connection ?

The query is being generated by the Application and we cannot add any SET commands to it.
Abdul Rahman
Abdul Rahman
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 1297
I also sense the difference in settings between SSMS and the Application Server.
I will have to check it on the Application Server.
But I am not able to understand why the same query is using so many threads when executed using the application and only a single thread when executed through SSMS ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search