Analysis Services Session Terminator App

I ran into a situation where the users of the SQL 2012 tabular cube started running extremely large queries that began to interfere with our weekly PowerPivot model refresh. The user queries were so CPU and RAM intensive that they pushed individual table refreshes from ~30 min to over 60 min which is the hard programmatic single query timeout in PowerPivot. I searched the web assuming that I’m not the only one with the need to cancel user sessions during maintenance time window and surprisingly all I was able to find were XMLA queries to cancel individual sessions based on SPID. I wanted an app where I could:

  • Monitor who is using the server without the overhead of the SQL Profiler
  • Select users that should be allowed to run queries
  • Set a threshold to allow normal users that don’t abuse the server to continue to run their queries and refresh their reports 
  • Schedule it to run for the amount of time necessary to finish highest priority queries

I ended up writing AS Server Session Terminator app that you can download should you find yourself in a similar situation and be blessed with the AS instance administrator privileges. Requires .Net4.51.

The app is pretty much self-explanatory: just enter your AS server name and fill out the Filter input field which is just a standard WHERE clause. Never delete the clause that excludes your session from being on the list. You can add more user aliases to exclude from the “to be terminated” list and you can adjust the level of the CPU usage for the queries that you are willing to tolerate.

UPDATE [1/29/14]: added right-click context menu to the session data grid. This update was based on the first round of feedback from the users inside my company. The context menu provides mouse click copy of the content selected in the grid cells (original version supported only keyboard shortcuts), single click delete of the selected sessions (the ones you want to keep alive when you hit the “Terminate Session(s)” button) or lets you cancel just the selected sessions (you don’t need to hit “Terminate Session(s)” button in this case). It is great to have feedback coming in. The app has been out for only 10 days.

UPDATE [2/4/14]: added status bar by popular demand that shows session count. Also moved “working state” progress notification to the status bar. Layout has been updated to group buttons and increase the size of the filter text box. Button captions have been shortened.

This entry was posted in Computers and Internet and tagged , , , , . Bookmark the permalink.

3 Responses to Analysis Services Session Terminator App

  1. James says:

    Hi

    Im trying to open an excel file that opens fine on a friends computer (he opens it in WPS or something like that – maybe MS Works) ?

    I tried saving it in “Excel 2003/2007 format” but I still get the message “too many cell formats”. After that message a few times it recovers the file but the headings and colors and formatting are all gone. Total mess

    I tried to download one of your tools but that link is going to an error message

    Thanks

    • XLGeek says:

      Hi James, good news: since your file can be opened on your friend’s PC it is still fully recoverable. All tool download links work fine. You probably don’t have Microsoft account to access SkyDrive which is totally free. You can try in browser Silverlight version of the tool if you don’t want to open SkyDrive: http://xlgeekwebapps.cloudapp.net/default.aspx. Silverlight download is only 5-6MB if you don’t have it. Tool works only on XLSX or XLSM files, so no 2003 file formats.

    • XLGeek says:

      Hi James, links indeed got broken because of SkyDrive to OneDrive conversion. I didn’t have access issues since I am the content owner. I updated all broken links with what is currently working with this version of the SkyDrive. Hope that you will be able to fix your file. I really appreciate you posting your comment and not just ignoring the error. I will be happy to look at your file if you still have any issues.

Leave a Comment or Ask a Question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s