March 31, 2011 at 6:50 pm
Hi everyone, i hope this is the correct forums that i am posting in.
Currently, i have a stored procedure which has multiple join conditions between views. These views itself has a very complex logic built in it to cater to our user requirements. This query is taking such a long time, and also resource intensive which is causing long waiting time to my report server (SSRS) users. Also, when this query starts to run halfway through, 1 of the application service connecting to the server via JDBC will get disconnected. I have a gut feeling that the disconnection could be due to the long running query but however i cannot see a direct link between the long running query causing the disconnection here.
What i want to know is that whether long running queries, CPU intensive or IO intensive will cause connections to the server to be disconnected?
Server: MS SQL Server 2005 Ent. Ed. (SP3) on Windows Server 2003 (SP2)
April 1, 2011 at 5:05 am
You'll only get disconnections if you have a timeout, either on the server or on the app, that is exceeded. Otherwise, the connection will way for the blocking process to clear.
BTW, nesting views and joining views to views, generally a very bad idea. I would look into refactoring that approach.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 3, 2011 at 6:33 pm
Thanks Grant for your insight.
I am almost 100% sure that the long running query is causing the application connection (JDBC, ReportServer) to be disconnected.
Sure, i would go about to modify the query because there is an obvious performance issue with it.
But still, i didn't find out why is it that queries with performance issue would close the connection between the server and the application.
April 3, 2011 at 9:09 pm
Conray (4/3/2011)
Thanks Grant for your insight.I am almost 100% sure that the long running query is causing the application connection (JDBC, ReportServer) to be disconnected.
Sure, i would go about to modify the query because there is an obvious performance issue with it.
But still, i didn't find out why is it that queries with performance issue would close the connection between the server and the application.
I've known JDBC to be a bit sensative if not configured and used correctly. Twice I've seen connection pooling cause issues where the app was closing connections but JDBC app was keeping them open. When the app requested a new connection JDBC wasn't presenting an existing open connection, it was opening a new connection. The result was hundreds of open connections in SQL that hadn't been used for days.
It wouldn't surprise me if the long running query is causing a disconnect for the waiting connetion, but causing JDBC to disconnect other connetions as well. Have you enabled logging on the connetion app to see what is going on?
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 5, 2011 at 6:34 am
get a copy of the awesome (and free) sp_whoisactive from Adam Machanic. It can help you identify blocking chains, among many other things.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply