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.