Analysis Services Diagnostics

blog-img1
12-12-2022
Written by Rob Risetto

Analysis Services Diagnostics

Analysis Services diagnostics can be a real pain at times, trying to understand why processing is not advancing or why a cube query is not coming back in a timely manner. Here’s some of the get out of jail diagnostics queries for SSAS that I gleamed from respected SSAS gurus.

Ever encountered a GB+ sized SSAS log file, well this Powershell command provides a quick way to check the tail of the log.

I’m showing the last 500 lines

Enter Powershell prompt, then run
get-content <yourpath>\OLAP\Log\msmdsrv.log -tail 500

Got some locking going on? Connect to SSAS via SSMS and run the following query.

select * from $system.discover_locks

You may need to know the commands and sessions also

select * from $system.discover_sessions

select * from $system.discover_connections

select * from $system.discover_commands

The below query combines some of the internal views for a more convenient check and allows you to run from a standard Query Window connected to a SQL Server Engine.

Create a linked server on a SQL Server, replace the linked server name in the definition and then run the below query to create the linked server.
EXEC master.dbo.sp_addlinkedserver @server = N'SSASCheck', @srvproduct='MSOLAP', @provider=N'MSOLAP',
@datasrc=N'<Your SSAS server name>'

Then run the Openquery statement below to combine data from some of the relevant SSAS internal views.

SELECT ssasSessions.SESSION_SPID AS [SPID],

ssasSessions.SESSION_USER_NAME AS [User ID],

ssasSessions.SESSION_CURRENT_DATABASE AS [Database],

ssasConnections.connection_host_name AS ‘Client IP address’,

ROUND(CAST(ssasCommands.COMMAND_CPU_TIME_MS AS int)/1000, 2) AS [Command Time(in sec)],

ssasCommands.COMMAND_START_TIME AS [Command Start Time],

ssasCommands.COMMAND_END_TIME AS [Command End Time],

ssasCommands.COMMAND_TEXT AS [MDX Command],

connection_host_application AS ‘Application’,

CHARINDEX(”,ssasCommands.COMMAND_TEXT) AS ProcessRunning

FROM OPENQUERY( SSASCheck ,’select * from $system.discover_sessions’) AS ssasSessions

LEFT JOIN OPENQUERY(SSASCheck,’select * from $system.discover_commands’) AS ssasCommands ON ssasSessions.SESSION_SPID = ssasCommands.SESSION_SPID

LEFT JOIN OPENQUERY( SSASCheck ,’select * from $system.discover_connections’) AS ssasConnections ON ssasSessions.SESSION_connection_id = ssasConnections.connection_id

ORDER BY [Command Start Time] DESC

Testimonials

Kind words from our valued customers

Testimonials Icon “We found the DBInsight Monitor tool to be a highly effective way of monitoring the performance of our mission critical servers. In particular it has provided us with detailed information about our SQL environment, including internal SQL memory pressure, blocking locks root cause and VM Snapshot backup failures due to overruns. This has allowed us to take a more proactive view to problem solving, and therefore a saving in time and other important resources.”

Jim Gordon, IT Manager, Deaf Services Queensland

Testimonials Icon “DB Insight has been utilised by our group of companies for several years. I cannot recommend them highly enough. They are fast, competent and professional. They have solved every problem we have encountered and the service is outstanding. We simply cannot speak highly enough of them. They communicate in laymen’s terms (Plain English), explain what they have done and their charges are very fair and reasonable. We have had no hesitation in recommending Rob and the team to other organisations in our industry. We wish Rob and the team all the best! Thank you sincerely for all your efforts.”

Craig Young, Director, Impact Financial Services, Sydney, NSW, Australia

Testimonials Icon “When we engaged DBInsight to perform a comprehensive SQL Server health check, we had great expectations. But then we got so much more than we expected. Expertise and attention to detail is second to none. Our system is so much better for it now.”

Martin Kammann, Owner, Autag Pty Ltd, Brisbane, QLD, Australia

Testimonials Icon “Thank the 7 gods we found this bloke. We have had a pile of crap dumped on our lap many times thanks to other incompetent IT providers and Rob has been the one to sort it out. In fact, he is considered the Grand Wizard of SQL by us. Rob, despite being a QLD’r, clearly does not sleep. We have had alerts at ridiculous hours and Rob has fixed them. No issue. If only we could sprinkle Rob Dust on all of our IT providers. Now nobody likes IT people because they speak a different language and send you big bills that you understand even less. Rob is unique in that he speaks English (IT perspective) and his bills are modest. In short, we like Rob. We like DB Insight. Use them.”

Craig Young, Director, Impact Financial Services, Sydney, NSW, Australia

Testimonials Icon “As a tech startup with a global market, we had to ensure that our database was scalable, secure and highly available. We engaged DBinsight as our virtual DBA and Rob has been absolutely brilliant. Using SQLRDS Monitoring tools, Rob and team are alerted to and respond to any issues before we are aware of them. I couldn’t recommend DBInsight more highly.
Whether you are a startup or a mature business, if you value your data, then you need to ensure that the right expertise is at hand always. Rob is the best SQL DBA technically, speaks in plain English (not tech speak) and is a pleasure to work with. Do your business a favour and call DBInsight.”

Aidan Gani, Chief Technology Officer, WorkApp Pty Ltd

Testimonials Icon “We have been relying on Rob from DBInsight to help with our Azure SQL Server database since September 2015.We initially engaged him when our database was at 60GB and we were experiencing high CPU/IO, whilst on the highest pricing tier offered by Azure SQL (at the time it was P3).
He quickly got our queries under control, but more remarkably, with his continued help we were able to remain on the same pricing tier as our client base more than doubled and our database grew to over 800GB.
Recently, just as it looked like we were going to have to scale up due to database size, Rob helped out again and reduced the database to less than half – thus allowing us to remain on the same pricing tier for the foreseeable future.
Rob has also provided SQL training to my team which was great in raising the overall awareness of what to consider when writing application code that will invariably require access to the database.
So Rob has saved us money – no doubt about it – but there is something more important and valuable to us – Rob provides us peace of mind – knowing that he is always there to help fix the database when things go wrong – to me, that is priceless.
Having Rob help out when required is a great solution for us, and in my opinion more effective and cost effective than an inhouse DBA. Rob gains experience from helping out a bunch of clients which means that he can always recommend new and improved ways that we could/should do things.
'To Rob, I say, thanks heaps for all your help over the years – you have kept us running.
Not surprisingly, I definitely and unequivocally recommend Rob and DBInsight to any organization with a database – be it, on-prem or cloud.”

Mike Van Radars, Inspect Real Estate

Testimonials Icon “Using the DBInsight monitoring service, has given us invaluable insights to our HADR environment. Along with the support provided by DBInsight it has given us the comfort and confidence to focus on further expanding our business.”

Rob Camilleri, CIO, Money3

Testimonials Icon “Going the extra mile”
“No matter how big or small the task, DBInsight have always shown a high level of professionalism and commitment to doing the task right. DBInsight address more than just the task at hand: going the extra mile to document and plan against possible issues that may arise in the future.”

Roy Adams, RACS Director

Testimonials Icon "The SQLRDS Plus service provides peace of mind that the fundamentals of our mission critical database services are expertly monitored and managed. Beyond this, access to a highly experienced team of DBA consultants has helped us improve application performance, maximise the use of our cloud resources, and enabled us to deliver a more responsive service to our customers."

David Perske, Director, Resolution Technology