In a previous post, we took a look at SQL Server’s implementation of a time-out and of a user cancel.
Today we’ll look at SSAS and examine the same operations.
In Management Studio (SSMS), I change the execution timeout from the default 0 to 10 :
I then launch a long running query from a remote client, while running both a profiler and a netmon trace.
SSMS indeed returns after 10s with the related error message.
The profiler trace shows a Query End taking place roughly 10 seconds after the query start. If you’re wondering about the nice round “.000” values for milliseconds, you might want to take a look at this previous post.
Now let’s take a look at the netmon trace :
Frame 100 is the Query Begin.
Frame 107 is Query End. Notice that Frame 107 is from server to client. No client to server frame between Query End and frame 107 (at this layer at any rate, we can see TCP keep alives at lower layers)
If we get back to Profiler and check the XMLA properties of the Query Begin, we’re able to confirm our suspicions :
Notice the last property 'Timeout'.
So it appears that in the case of SSAS, the timeout is an extended property of the XMLA query sent by the client upon the initial request. The server is going to control the elapsed time and take care of the timeout logic if requried. If the timeout fires (server-side), the client just receives an early error instead of the query result.
Quite different from SQL Server eh?
SSAS Query Cancel
Now that we’ve seen how SSAS implements a timeout, let’s take a look at the query cancel action. Based on what we just saw, we know that query cancel won’t be able to reuse the timeout implementation, since a query cancel is unpredictable and takes place on the client, whereas the timeout is fired by the server itself. The client cannot 'warn' the server of the query cancel when it fist sends the query.
To be 100% clear, by query cancel I mean the red square button near Execute in SSMS toolbar :
Now this time the profiler should be enough to see what’s taking place :
We see that the cancel query button in SSMS triggered a new connection to SSAS from SSMS (see Login event), which executed a special XMLA command ‘Cancel’ against the client ProcessID of our victim query (here 5040), which resulted in early termination of the query.
Again, quite different from SQL implementation. The cancel actually takes place outside the context of the victim query (so an admin can craft a XMLA cancel for his own purposes).
That's the end of our time-out X-rays, have a good day :)