3

I am using a BI Model that is a based on SSAS Tabular, using SQL server 2016. Multiple team members are querying it for their needs. I want to find out what Queries are being run and who is running those queries.

Couldn't find DMVs very helpful in my case.

This query only shows the last command run by a user.

Select * from $System.discover_sessions
Njk
  • 41
  • 1
  • 6
  • 1
    We got something similar working for SSAS Multidimensional using extended events. The following might point you in the right direction: https://www.mssqltips.com/sqlservertip/4548/using-extended-events-to-monitor-dax-queries-for-ssas-tabular-databases/ – BarneyL Sep 24 '19 at 12:21
  • 1
    Individual queries are not captured by default so you need to create a trace, using Extended Events as @BarneyL suggested or a Profiler trace. – Dan Guzman Sep 24 '19 at 12:32
  • Have you tried using [SQL Profiler](https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017)? – Attie Wagner Sep 25 '19 at 06:31
  • @Birel Yes i did try it. But I wanted to know a better way. – Njk Sep 27 '19 at 13:35

2 Answers2

1

The simplest of the lot is still SQL Server Profiler. It's been around forever, almost replaced by Extended Events, but works just fine with SSAS, and easy to trace activity with it.

Greg Low
  • 1,526
  • 1
  • 4
  • 4
-1

For the question of "Who is running those queries", we use the "Query Logs" feature of SSAS.

Once you setup a bunch of properties in the SSAS Server, it would start to log to the configured SQL Server table.

To enable the query log, follow these steps:

(1) Create a SQL Server relational database to store the query log.

(2) Grant the Analysis Services service account sufficient permissions on the database. The account needs permission to create a table, write to the table, and read from the table.

(3) In SQL Server Management Studio, right-click Analysis Services | Properties | General, set CreateQueryLogTable to true.

(4) Optionally, change QueryLogSampling or QueryLogTableName if you want to sample queries at a different rate, or use a different name for the table.

Un-fortunately, the Query Logs, does NOT log the queries ! But, it does help in finding who / when the queries are running.

Once you have enabled logging, you can query the table for stats.

SELECT CAST(starttime AS DATE) 'Date'
        , MSOLAP_User 'User'
        , COUNT(1) 'No. of queries'
FROM [dbo].[OlapQueryLog]
GROUP BY MSOLAP_User
        , CAST(starttime AS DATE)
ORDER BY 1 DESC, 3 DESC

There is also the AsTrace tool

For constant monitoring and logging, the ASTrace tool will capture a Profiler trace and write it to a SQL Server table without requiring a GUI. ASTrace also runs as a Windows service allowing it to restart automatically when the server reboots.

Subbu
  • 2,130
  • 1
  • 19
  • 28