SSAS多维数据集处理日志 [英] SSAS Cube processing logs
问题描述
SSAS多维数据集处理(不是错误，不是飞行记录器)日志存储在哪里?
Where are SSAS cube processing (not error, not flight recorder) logs stored?
我们有一个运行SQL Server Analysis Services命令的SQL代理作业.在其中，有一些DMX处理每个维度，然后处理多维数据集数据库(包含两个多维数据集)
We have a SQL Agent Job running a SQL Server Analysis Services command. In there is some DMX which processes each dimension then processes the cube database (containing two cubes)
我想知道各种查询所花的时间.每个维度有一个查询，每个度量值组有一个查询
I want to know how long each of the various queries are taking. There is one query per dimension and one query per measure group
该多维数据集耗时20分钟，而现在耗时2个小时.
The cube was taking 20 minutes now it's taking 2 hours.
我们正在使用SSAS 2008 R2
We are using SSAS 2008 R2
我经过漫长而艰苦的搜索，据我所知没有这样的日志.
I have searched long and hard and as far as I can tell there is no such log.
不是重复重复的问题:
我不想使用Profiler.我想看看每个查询至少花了多长时间才建立了最后一个多维数据集.如果以交互方式运行，我可以看到所有这些信息.从作业运行时如何记录此信息?
I don't want to use Profiler. I want to see how long each query took in the last cube build at least. I can see all this info if I run interactively. How do I make it log this info when run from a job?
推荐答案
有两个选项.您可以按照自己的方式继续处理多维数据集，但是开始记录所有处理事件.除了Profiler GUI之外，还有三种主要的方法可以做到这一点:
There are a couple of options. You could just continue processing the cube just the way you are but start logging all processing events. Other than the Profiler GUI, there are three main ways to do that:
-
服务器端跟踪将.trc文件写入SSAS服务器上的磁盘，而开销很小: > http://www.bp -msbi.com/2012/02/counting-number-of-queries-executed-in-ssas/
A server side trace writes a .trc file to disk on the SSAS server with little overhead: http://blogs.msdn.com/b/karang/archive/2009/11/02/sql-2005-sql-2008-analysis-services-server-side-tracing.aspx Then you can load it into SQL server to analyze later via PowerShell: http://www.bp-msbi.com/2012/02/counting-number-of-queries-executed-in-ssas/
安装名为ASTrace的社区维护服务，该服务使用Profiler API(无GUI)并将选择的Profiler事件直接直接写入SQL Server实时. https://github.com/Microsoft/Analysis-Services/tree/master/AsTrace
Install a community maintained service called ASTrace which uses the Profiler APIs (without the GUI) and writes the Profiler events you choose directly to SQL Server real-time. https://github.com/Microsoft/Analysis-Services/tree/master/AsTrace
记录XEvents并在以后对其进行分析: http://blog.crossjoin.co .uk/2012/05/05/using-xevents-in-ssas-2012/ 或者:
Log XEvents and analyze them later: http://blog.crossjoin.co.uk/2012/05/05/using-xevents-in-ssas-2012/ Or: https://francescodechirico.wordpress.com/2012/08/03/identify-storage-engine-and-formula-engine-bottlenecks-with-new-ssas-xevents-5/
所有这些选项将记录所有正在处理的事务.您可以选择要记录的事件(例如，处理事件而不是查询).
All of those options will log all processing transactions. You get to choose just which events you want to log (processing events not queries, for example).
但是另一种替代方法是使用会话跟踪.您可以在SQL Agent中停止使用"SQL Server Analysis Services命令"步骤类型，而开始使用PowerShell步骤类型并执行以下操作.本示例正在使用SessionTrace从PowerShell运行SSAS备份，以仅监视该会话的所有事件探查器"事件: https://gallery.technet.microsoft.com/scriptcenter/Backup-ssas-databases-with-da62b084
But another alternative is to use a session trace. You could stop using the "SQL Server Analysis Services Command" step type in SQL Agent and start using a PowerShell step type and do something like the following. This example is running an SSAS backup from PowerShell with a SessionTrace to watch all the "profiler" events just for that one session: https://gallery.technet.microsoft.com/scriptcenter/Backup-Ssas-Databases-with-da62b084
这篇关于SSAS多维数据集处理日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
