How to kill idle SSAS sessions and connections in a batch

While connections and sessions don’t take much memory compared to what the actual SSAS data does, they still take some memory that can be valuable to free up if a server is under resource pressure. SSAS routinely checks the amount of time elapsed from a session/connection’s last command and if it has been idle for more than a certain amount of time, it will end it.

There are few SSAS settings that control this behavior.

Property	Definition	Default Value in Seconds	Notes
IdleConnectionTimeout	Timeout for inactive connections	0	A value of zero (0) indicates no timeout limit.
IdleOrphanSessionTimeout	Timeout for orphaned sessions	120
MaxIdleSessionTimeout	maximum idle session timeout	0	0 means no timeout but idle sessions will be removed when the server is under memory pressure
MinIdleSessionTimeout	the minimum timeout for idle session	2700	Server is allowed to end idle sessions after this amount of time. It may choose to do so only when it is under memory is needed.

If you catch a server under resource pressure, you can expedite the removal of idle sessions/connections by modifying the IdleConnectionTimeout and MinIdelSessionTimeout properties.

While this is not something you should do routinely, it can occasionally come handy on a very busy server where tons of users may have made connections with various queries and left their reporting sessions (SSMS, PBI, …) open. In which case, you may not want to wait 45 minutes (2700 seconds) to get rid of them.

For a complete list of SSAS properties, see this link.

Warning

Think of this method as an emergency procedure only. As always, have database backups and try this on a development server first. Always take a backup of msmdsrv.ini before you modify any server properties. The default location of the file is this: C:\Program Files\Microsoft SQL Server\MSAS15.MSSQLSERVER\OLAP\Config

If you set the timeout values too low on server that is under resource pressure, you may not be able to get to the server properties using SSMS and change them quickly within the time you set for the timeout. For this reason, I prefer the use of XMLA in this case which makes the process faster.

XMLA code for reverting to default settings

By default, IdleConnectionTimeout is set to 0 (no limit) and MinIdelSessionTimeout is set to 2700 seconds (45 minutes).

Keep this handy to return settings to normal afterwards.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
	<Object/>
	<ObjectDefinition>
		<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
			<ID>ABI1</ID>
			<Name>ABI1</Name>
			<ServerProperties>
				<ServerProperty>
					<Name>IdleConnectionTimeout</Name>
					<Value>0</Value>
				</ServerProperty>
				<ServerProperty>
					<Name>MinIdleSessionTimeout</Name>
					<Value>2700</Value>
				</ServerProperty>
			</ServerProperties>
		</Server>
	</ObjectDefinition>
</Alter>

XMLA code for modified settings

This code sets both IdleConnectionTimeout and MinIdelSessionTimeout to 120 seconds.

Warning: On a busy server, don’t set this to less than 5 minutes at first. You want to have enough time for yourself to start a new XMLA query in SSMS and run the recovery command. If you set it too low, you may not be able to will have to modify the msmdsrv.ini directly which may require a service restart which is not desired.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
	<Object/>
	<ObjectDefinition>
		<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
			<ID>ABI1</ID>
			<Name>ABI1</Name>
			<ServerProperties>
				<ServerProperty>
					<Name>IdleConnectionTimeout</Name>
					<Value>120</Value>
				</ServerProperty>
				<ServerProperty>
					<Name>MinIdleSessionTimeout</Name>
					<Value>120</Value>
				</ServerProperty>
			</ServerProperties>
		</Server>
	</ObjectDefinition>
</Alter>

Example

Here I will show you an example from my development server. I used Windows Perfmon to monitor the current sessions and connections. For the baseline, there were no sessions and connections.

Then I connected to the server using SSMS which opened one connection and one session.

Then I started a new DAX query.

Every DAX query in SSMS, adds one connection and one session.

Then I added a second DAX query:

This increased the connections and sessions each by 1:

Then I added a DMX query to run of of the SSAS DMVs (Dynamic Management Views).

You can get a list of connections/sessions by running the following query in a DMX query window in SSMS:

Select * from $system.DiscoverSessions

Each DMX query adds 2 sessions and 2 connections.

Finally I added another session/connection by opening up DAX Studio and running the exact same query as above:

This added 1 more session and one more connection:

With all the test sessions/connections in place, I got the server properties so that I can change them. This added yet another connection and session:

I set both IdleConnectionTimeout and MinIdelSessionTimeout to 120.

You could set these properties using the XMLA command given previously. On a busy server, the XMLA command is a better choice.

Once the new settings were in place, the connections and sessions started going down and eventually went down to zero after a couple of minutes.

Note: The timeout time does not start from when you set the setting, it starts from the last time the session/connection was active so if anything has been active for more than 2 minutes, you should see them drop very quickly if not immediately. When this happens, you may see a disparity between the number of sessions/connections but they will even out eventually. For example, in this example, there were a few seconds where there were more connections than sessions.

Eventually you should see zero for both. On a real production sever, you will not get to zero because you will have new queries and existing active queries continue.

What does a timeout look like on the client side

The following picture shows the error you would get in SSMS if you try to run any of the queries again.

The following picture shows the error in DAX Stuido.

Summary

In this post I showed you how you can use IdleConnectionTimeout and MinIdelSessionTimeout to drop idle SSAS connections in batch quickly. This is a an emergency fix method only. Ideally you should mange the server resources/query load properly to not get into a situation where you need to do what I showed you!

For managing user queries, you can eliminate individual user sessions/connections by running an XMLA command one at a time.

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ConnectionID>[CID]</ConnectionID>

  <SessionID>[SID]</SessionID>

  <SPID>[SPID]</SPID>

  <CancelAssociated>[true/false]</CancelAssociated>

</Cancel>

You can also use an automated SSIS package that Chris Webb described here to monitor user queries and terminate them on a regular basis.