Can Azure SQL DB trace by some kind of SQL Profiler ?

(0) Share

Report

Posted on by Tony zhong

901

Hi,

I never work with Azure SQL DB before, just wondering, since D365 using Azure SQL DB, can we trace it's query by some kind of SQL Profiler ?

Reason is I need to know what's going on during 1 of the process in D365 F&O. Or is there any idea ? ant other tools to do it ?

I tried ti look at event viewer of my VM, but I don;t know which event either. There are bunch of node in Application and Service Logs / Microsoft / Dynamics, all start with AX, but I did not find what I'm looking for.

The process which I want to know at the query level is Refresh Entity Store..

Many thanks in advance.

I have the same question (0)

All responses (13)

Answers (1)

Verified answer

Rahul Mohta 21,032 on at

Like (0)

Report

Can use Azure data studio and profiler combination

Refere for more

https://www.sqlshack.com/sql-server-profiler-in-azure-data-studio/

Was this reply helpful? Yes No
Suggested answer

Sukrut Parab 71,710 Moderator on at

Like (0)

Report

Your VM uses normal SQL server not Azure SQL , so you should be able to use whatever tools you used already with SQL server. Tier 2 boxes and prod has azure SQL.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,807 Most Valuable Professional on at

Like (0)

Report

First of all, make sure that your environment really uses Azure SQL. It's true that F&O uses Azure SQL in production and environments close to production, but other environments, such as those used for development, run on SQL Server. Since you mentioned "your VM", I think you have SQL Server too.

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

Hi Martin & Sukrut,

Sorry about "my VM" thing, it is actually UAT VM. Do you happened to know the process of "Refresh Entity Store" and how we can trace it if we having error such as this :

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

By looking a the error, I know it is not error coding, I assumed, it is because of some data having duplicate value, but it is difficult because our Entity Store is huge, has many query inside, and I don't know which one causing it.

So I thought I want to try my luck to see from SQL Profiler whether I can get something there. One question though, as the Azure Data Studio suggested by Rahul, we can access UAT Azure SQL DB, but can we use the same tools when the error happened in Production ?

Thanks,

Was this reply helpful? Yes No
Suggested answer

Sukrut Parab 71,710 Moderator on at

Like (0)

Report

I don't think you can use same tool on prod DB as you don't have RDP access to PROD to install. You have to rely on telemetry and query store

docs.microsoft.com/.../monitoring-performance-by-using-the-query-store

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

You can also copy prod DB to a test system and troubleshoot it there, if you have similar issue in prod.

Was this reply helpful? Yes No
Suggested answer

Sergei Minozhenko 23,093 on at

Like (0)

Report

Hi Tony,

You can also check Trace parser (docs.microsoft.com/.../trace-trace-tutorial). You can find in traces sql queries that were run on the environment. After you find the query, you can anylize it in SQL management studio.

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

Hi Sergei,

I cannot open the link, is this the one -> docs.microsoft.com/.../trace-trace-tutorial

It sound promising, I just tried it now, have downloaded the trace file, and it is ".etl", do you know how to open it ? is there an D365 extension to read the file ?

I tried to google but found only for AX2012 version.

Thanks,

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

The file is opened with Trace Parser. You will find it on all D365FO dev VMs.

Was this reply helpful? Yes No
Tony zhong 901 on at

Like (0)

Report

Hi Nikolaos,

I'm sorry, can you be more specific, is it a file inside folder AOSService ? should I need to install it first ?

Thanks in advance,

Was this reply helpful? Yes No