Using Linked Servers with SSAS and Timeout issues
Why use Linked Servers with SQL Server Analysis Services?
One way of connecting to a SQL Server Analysis Services (SSAS) Server is using a Linked Server.
My initial use of a Linked Server for SSAS was to create a script to loop through all of the Databases on the Server, performing the same action on each. I was not able to find a way to do this using XMLA.
Creating a Linked Server on a SQL Server to reference the SSAS Instance, you can execute commands against this Linked Server. Linked Servers can be used to execute both static and dynamic SQL.
I could have used an SSIS Package to perform the same task, but building it in T-SQL is simpler and easier to maintain.
I have experienced some issues with timeouts using this method. Specifically when performing Backups or Restores.
Considerations for SQL Server Timeouts
Whenever faced with a Timeout, I consider two things:
- What can I do to reduce the time of the operation? This would be the preferred method of fixing the problem. When companies such as Microsoft set a Timeout on an operation, it’s generally for a far longer period than my patience when waiting for something to complete.
- If I increase the timeout value then what else might be affected?
As an example, a Timeout can be a good thing. It puts a precise stop on how long an operation can take before giving up. As I write this Blog Post, I have a sand timer running which gives me an idea of just how long I’ve spent on this particular task. As the sand runs out, I get more of an idea of how much time has been spent and it provides a prompt for me to evaluate whether I should spend more time here or stop. It certainly reduces the chance of a 15-minute job taking all day.
Further downstream, an application might rely on a timeout to provide a “reasonable” user experience (note how I used “reasonable”, not “good” – it has timed out!). For example, if a user selects a function in an application and the timeout is set to five seconds, if there’s been a problem then the user will know about it after just five seconds. Consider what would happen if someone increased the overall timeout from five to thirty seconds – users would be waiting far longer for the application to return. The same issue (query taking too long) still needs to be fixed but it has become more disruptive.
There is a “Query Timeout” parameter on a Linked Server. This value is in seconds. By default, this is set to 600 – i.e. 10 minutes. The Timeout can be removed by setting this value to 0. In theory, this would mean that queries through the Linked Server would run indefinitely. There is, however, another Timeout value to be aware of.
The SQL Server itself has a “remote query timeout” setting, which is also set to 10 minutes by default.
If the Linked Server “Query Timeout” is set to 0, then the “remote query timeout” setting also needs to be considered. This can be increased to a higher value, or (not recommended) set to 0 to allow queries to run for as long as they require. Be careful not to create a problem by fixing an issue.
