Imposing Query Timeouts
I’m currently having an interesting email conversation about the lack of a query governor in Analysis Services and how useful it would be, and Darren Gosbell mentioned a new connection string property I wasn’t aware of: timeout, which allows you to specify a timeout in seconds for your queries. To test this you’ll need to open a tool which supports AS connection string properties (SQLMS doesn’t) like MDX Sample app, and simply put
timeout=1
in the connection string. This means that any query which takes longer than one second to execute will fail in one second. How could I not know about something as useful as this?
UPDATE: I’ve just heard from Mosha that apparently all XMLA command properties can be used in the connection string.
OMG! That IS useful! However, like the XMLA "Cancel" command, I think it will suffer from the fact that apparently not all sub-routines in AS are built to handle the cancelling of a query – hence it will not always terminate immediately on the server. It\’s still a great tip, though.
Michael
November 2, 2006 at 2:31 pm
Just to clarify – Timeout is not the new connection string property. It existed since AS2000 SP4. Also, since AS2005 uses XMLA as underlying protocol, _ALL_ XMLA command properties are available as connection string properties both in OLEDB and ADOMD.NET.
Mosha (http://www.mosha.com/msolap)
Mosha
November 2, 2006 at 4:41 pm