What are the @OlapEvent queries hanging around in my SQL Server?
If you look at current activity in SQL Server 2005 or 2008 or 2008 R2, you may notice some some sessions coming from Analysis Services where last_wait_type is TRACEWRITE. For example, when you query like this:
SELECT * FROM sys.dm_exec_requests;
SELECT * FROM sys.sysprocesses;
Since SQL has default traces that run, sometimes a query which is doing nothing but waiting lists the last wait type as TRACEWRITE since that was the only thing it ever waited on. These are the default SQL traces described here http://msdn.microsoft.com/en-us/library/ms175513.aspx and http://technet.microsoft.com/en-us/library/cc293615.aspx
Such is the case with the OlapEvent notification query. Sometimes you see this query coming in from SQL Analysis Services sessions to the SQL database engine (relational engine).
DECLARE @OlapEvent BIGINT;
SELECT @OlapEvent = ObjIdUpdate(2);
SELECT (@OlapEvent & convert(bigint, 0xffff000000000000)) / 0x0001000000000000 AS Status, (@OlapEvent & convert(bigint, 0x0000ffff00000000)) / 0x0000000100000000 AS DbId, @OlapEvent & convert(bigint, 0xffffffff) AS ObjId;
This “@OlapEvent” query is the query used for Proactive Caching Notification when tracking tables are specified.
The query is designed to wait until any data change is made on the sql relational table, and then it returns to AS to signal that a change has happened on the table, acting like a trigger to tell AS new data is available to process into the Cube.
It is expected that this query batch will run a long time (forever hopefully) until new data is written into the notification table in the relational database – an insert, update, delete. This query being “stuck” or “hung” in the list of sessions and current activity in SQL Server is not the indicator of any problem but just an observation of how AS works internally. The query will finish running when data changes are made to the table, and a new copy of the query will start soon after to monitor for more future changes.
In your Analysis Services database design, you can see which partitions have this proactive caching notification enabled via SQL Server Management Studio (SSMS) or the BI Development Studio (BIDS) interfaces.
A> SSMS: You can see the setting which controls this query in the SSMS object explorer > Analysis Services > Dbname > Cubes > Mesaure Groups > MG name > Partitions > Partition Name.
B> In Visual Studio/ BIDS, you can see the partitions by opening the cube from the Solution Explorer, and look at the Partitions tab. Each measure group can have a separate list of partitions, and each partition has specific storage settings. Click the hyperlink that says [Storage Settings…] to get the page where the proactive caching setting is shown.
Then you can view the properties of each partition and note the options button on the Proactive Caching page and the [Options...] button. I imagine some of the partitions are configured with proactive caching enabled, and on the Notifications tab, it says a specific tracking table, which is the table being monitored for changes.
Documentation of what is Proactive Caching - http://msdn.microsoft.com/en-us/library/ms174769.aspx
Some known issues related to this Proactive Caching query: