View Metrics - crashes the entire tabular service: SQL Server Analysis Services (TABULAR)

DaxStudio / DaxStudio

DAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Desktop, Power Pivot for Excel, and Analysis Services Tabular.

http://daxstudio.org

Other

665 stars 144 forks source link

View Metrics - crashes the entire tabular service: SQL Server Analysis Services (TABULAR) #845

Open raflemmens opened 2 years ago

raflemmens commented 2 years ago

DAX Studio v2.17.3.683 Connection: not connected

Summary of Issue

Running view metrics against a tabular model on the server. After a time I get the message connection lost and my entire instance has stopped running. I have to log on and restart the tabular service on the server.

Steps to Reproduce

1.If needed I can provide a detailed server trace. Please advice on which events you would like to have in the logs.

I have this issue on my DEV, VAL AND PRO servers
using SQL server 2016 standard edition.
server free RAM 54 GB
Screenshot steps :

Connect to tabular model
load model metrics
showing that the services are still running
sql server trace stops and the last event logged is query begin (standard tracing options)
tabular service stopped working
error in dax studio

1 DAXstudio - connected to tabular model 2 DAXstudio - loading model metrics 3 services still running 4 sql server trace stops all of a sudden Query begin is last statement logged 5 tabular service stopped running 6 dax studio error . lastQuery.txt

marcosqlbi commented 2 years ago

It could be a bug in an old version of SSAS 2016. Which exact version/build of SSAS 2016 are you running? If you don't have a current update, can you patch that instance? You can also try to disable the collection of advanced statistics, in case it is causing the issue (Options / Advanced):

raflemmens commented 2 years ago

SQL Server : 13.0.6300.2 - should be the latest available I guess.
Disabled the option "read statistics from data"

With the option disabled the analyzer comes through. Thanks I can now view the needed information!

marcosqlbi commented 2 years ago

Yes, it is the latest one. I'd keep the issue open - if you can run a SQL Profiler session on SSAS 2016 when you run DAX Studio and crash the engine, it's possible that the log could be useful to identify the query that crashes the engine and evaluate a workaround or ask a fix to Microsoft.

dgosbell commented 2 years ago

And for the profiler session we'd probably be interested in seeing the following events:

Command Begin/End
Discover Begin/End
Query Begin/End
Error

But I suspect that given turning off "Read Statistics from data" that it's one of the rowcount queries doing this so we would probably see a QueryBegin with no matching end event. And this is almost certainly a bug in SSAS 2016, no client request should be able to crash a server.

SharmaAntriksh commented 2 years ago

I think he is using DirectQuery mode.

CamMicRio commented 2 years ago

Hi, i have the same problem on a 2017 instance

dgosbell commented 2 years ago

@CamMicRio - so does the fix Marco suggested above also fix this issue for you? I'm pretty sure that this crash is due to a bug in Analysis Services.

CamMicRio commented 2 years ago

Hi @dgosbell yes, disable the Read statistics from data option has fixed the problem. Thank you and grazie Marco ;-)