March 23, 2016 at 10:09 am
We have an Access application that uses SQL Server linked tables to populate forms and these connections leave open transactions in tempDB with a name of sort_init. What we end up seeing during the day is that the tempDB transaction log continues to grow unless some of these sessions are manually killed. Much of the time, these sessions are in a suspended state with a last wait type of ASYNC_NETWORK_IO and show a last batch of several hours ago. Is there something in Access that can be done to manage this better or do we need to manage this issue on the SQL Server side?
March 24, 2016 at 11:20 am
I've not seen that behavior when using Access with linked SQL Server tables, but I am suspicious that your users may be doing repeated sorts of data in Access forms, queries or tables, and those TempDB log entries remain there until such time as the user disconnects from the database. What version of Access and SQL Server are you working with?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 24, 2016 at 12:07 pm
It does seem to be related to a sort that never completes. I'm a DBA and don;t know much about the Access application. It's probably a fairly recent version. This instance is SQL Server 2008 R2 SP3. It would be nice if we could find a way to address this without having to kill sessions periodically.
March 24, 2016 at 1:24 pm
If what you're describing in a scenario where each SQL Server table is added to the MS Access project as a linked table, and then you're joining linked tables together on the client side using MS Access SQL, then that's problematic for performance. This may appear like a normal simple SQL query in MS Access, but it's actually a distributed query that results in pulling across the network more rows than needed to the client, because it's joining and filtering locally within the application.
The following article describes how to leverage views, stored procedures, and pass-through queries to more efficient query from SQL Server. Essentially the goal is to run the SQL query remotely on the server, thus performing all joins, filtering, sorts, etc. server-side, and then return only the final resultset back to the client-side application.
Optimizing Microsoft Office Access Applications Linked to SQL Server
https://technet.microsoft.com/en-us/library/bb188204(v=sql.90).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply