This query runs for almost an entire week

Howard 2013-05-20 22:59:38

Hi everyone. I am trying to figure out why this query SPID stays open for the entire duration of a batch job. The batches usually take a week to complete and during that time, this SPID is open the entire time.

I have run the query myself and while it is large and returns 4.5 million rows, it completed in about 4 minutes.

What could be causing the query to stay open, locking resources, for the duration of the weekly batch?

Thanks for reading.

SQLkiwi 2013-05-20 23:06:10
What consumes the rows produced by this query? If the rows are gradually consumed over the course of the run, that would keep the query "open".
Howard 2013-05-20 23:50:00
It is an application on another server. It is possible that the remote application is taking it in bit by bit. ASYNC_NETWORK_IO wait type seems to suggest that the application is causing the wait.

Thanks for responding.

SQLkiwi 2013-05-20 23:57:28
Yes, that is exactly what ASYNC_NETWORK_IO is telling you. If you can afford the space, you could write the results to a (temporary) table and let the application read from that. That would avoid holding locks etc.
Howard 2013-05-21 00:21:47
I would love to do that but I don't think we can control the vendor application in that way. Out of all of the SQL instances I support, this one is the most tricky because I can't do much maintenance when locks are held by a single SPID for a week at a time. Thanks for responding.
rlDawson 2013-05-21 16:00:05
I would expect that the third party app had to have been configured at some point to tell it where to get the data from in the first place. If you can change that configuration you should be able to do as SqlKiwi suggested above.

I freely admit I am working from ignorance on what is involved, but if the app looks for data something had to tell it where to look.