2 Replies Latest reply on Feb 14, 2017 11:26 AM by Karthik Venkatachalam

    Abandoned DB Queries - Tableau Server

    Karthik Venkatachalam

      I am seeing a situation where Tableau server is not telling the DB to drop/cancel a query. i.e, When a user closes the browser/workbook, the query still keeps executing long after the browser is closed, thus consuming SQL Server resources - what we can call as a "abandoned/orphaned query". Tableau Desktop can cancel a query, and you will see it dropped from DB as well. But I don't see this is being handled by Tableau server. This becomes a problem in production where users may close a running workbook (after triggering a large set of queries).  This quickly adds up in a production scenario and will negatively impact the DB server. You can use SQL server Activity monitor (refresh every second) to look at "Active Expensive Queries". I searched SQL server forums, which seems to indicate, application should tell the DB to cancel the query, there is another way SQL server would know this information.

       

      I also submitted a case with tableau support, who are suggesting "By default, VizQL sessions are kept in memory even when a user navigates away from a view. This consumes a good deal of session memory. Instead, you can end sessions when users move away from a view by changing the value of the vizqlserver.clear_session_on_unload setting to true (the default is false).". But this doesn't sound completely right to me, so I have not tried this. I still want to keep the Vizql session, and still be able to drop abandoned queries.

       

      We are on Tableau 10 server/desktop and SQL server 2014.

       

      Thanks.

        • 1. Re: Abandoned DB Queries - Tableau Server
          Russell Christopher

          In Server, I don't think we cancel an in-flight query unless it exceeds the querytimeout value that an admin has set. This sounds like a feature request to me. You might want to post same in the "Ideas" section - but you'll really want to quantify the actual impact to usability on your SQL Server. Everyone likes to keep their servers pristine, but a few users doing this aren't going to impact your server much one way or the other unless they're firing really expensive queries - and then it's the DBA's job make 'em clean up their SQL

           

          SQL Server does listen for "attention events" - the client telling to do something, like cancel a query. So this is completely possible.

           

          I'm not really sure clear_session_on_unload will help you here, because I've noticed that even when the sucker is turned on, I never saw sessions getting cleared up immediately upon the browser getting closed...It always seemed like this stuff would happen a minute or two later - which is an eternity for query execution

          • 2. Re: Abandoned DB Queries - Tableau Server
            Karthik Venkatachalam

            Have created an Idea here. This is definitely in the interest of most folks who has a DB connection to their books. If you like to increase performance of your architecture (by avoiding overheads and unnecessary ), please take a moment to vote up.

             

            https://community.tableau.com/ideas/7107