I am unable to see any of the main stats for the database

[charlene-gif]

Hi,

The basic graphs appear but when i try to view the worst performing queries etc, i am presented with the following:

This item is currently hidden so it is not automatically updating. Non of the database stats are visible when the workbook is applied. Sorry if its a silly question. I am now to workbooks

[BobbyH49]

Hi,

Which workbook are you currently using? I've made some changes today so want to rule out any new bugs introduced.

When you open the workbook, make sure you click Done Editing from the top menu bar. This will hide anything not being used and make the workbook easier to view.

You should then see tabs appear which you can click to move between charts and Query Store. Query Store only works if you have Diagnostic Settings setup for the database(s) you are looking to monitor. If this is already setup then you should be able to select the Log Analytics Workspace and view the Query Store information.

Once Query Store information starts to be populated you will see the charts appear and below them you should see the statistics. As you then select a database you will get Query info for the database underneath. Selecting a Query will give you the Plan info.

A few of the data tables open up a new table as you select each row.

If you're still struggling then please share a screenshot of the issue.

Hope this helps.

[charlene-gif]

Hi, thank you for this.. None of the in-depth database stats are coming through. Do i need to change some of the parameters or is it because i am missing some monitoring somewhere? I have gone through each database and turned on log analytics login via diagnostic settings but nothing is being displayed. Only the basics like cpu etc at the very top.

Do i need to change some of the parameters via the .json script?

[BobbyH49]

Hi,

So looking at the screenshot, there should only be 2 things that you need to do.

1. Click Done Editing from the top menu bar just above "Azure SQL Database Performance Monitor". This will remove anything that should be hidden such as all the parameters currently set to True in your screenshot. It should also make the workbook easier to navigate.
2. Assuming this is the Azure SQL Database workbook, you should see 2 tabs (Metrics and Query Store). Select Query Store and you should see some parameters referring to the Log Analytics workspace where your Query Store data should be captured. Select the workspace that applies to your SQL Database(s). At the moment from the screenshot, I can see this is set to \<unset>.

[charlene-gif]

Hi, I have used just updated the .json code to the most recent updated "Azure SQL Database Performance Monitor" script.

I have two tabs as you mentioned. One says "Metrics" and the other says "Databases and Query Store". When i click on the "Databases and Query Store" tab, i can see a list of sql managed instances in one box. I can click on them individually. That will take me to the main instance page - where i can see the memory usage etc..

[charlene-gif]

[BobbyH49]

Hi,

When you initially run the code for the Managed Instance you should get a screen like this. Click Done Editing marked by the Red Box.

Go to the Databases & Query Store tab and then click on any row in your instances to view the databases underneath. However, you must click somewhere that doesn't contain a hyperlink (e.g. not under ResourceId).

The hyperlinks are for anything that is classed as a Resource or Container and clicking on these will take you straight to the resource or container. If you do click on a hyperlink and want to return to the workbook then just click the breadcrumb.

Underneath the instances and database tables you should see some more parameters allowing you to select the log analytics workspace. As long as you have permissions to see and read from the workspace, and diagnostic settings have been setup. You should be able to select the workspace.

Once the workspace has been selected you should see all of the Query Store charts and the Database(s) table populate below.

If you see these error messages about the 'extend' operator then the charts and tables were unable to retrieve the data from the Log Analytics workspace. Check to ensure you have selected the right one for you Managed Databases and check the Time Range (one of the first parameters) is valid.

When you scroll down to the Query Store stats by database section, you should be able to select each database to view Query information by Id or Hash underneath. You can then click on each query to get another table showing Plans by Id or Hash. There is a parameter to toggle between Id or Hash, as well as an Order by parameter to order by a particular metric.

Hope that makes a bit more sense now.

[charlene-gif]

Morning,

Thank you for this. Yes i am making progress now. The only thing now is:

1. I can see the graphs, but no data is on them. I tried changing the time rage from 24hours to a few days. Still, nothing is displaying. Does that mean there is something wrong with my actual logic app data collection?

2. There are two query tabs that are displaying an error.

Sorry to be a pain.. I am most certainly in learning mode :-)

[BobbyH49]

No, that's absolutely fine, it's a complicated workbook.

I'm wondering if the diagnostic settings have been setup correctly because it sounds like you are using Logic Apps to do this rather than from the database itself.

1. Go to the Overview page of your Managed Instance and then from the left menu select Data management and then Databases.
2. On the Databases page select the Database that you wish to monitor. If you have several databases that you want to monitor then you will need to do this for each. It will be possible to do this using Azure Powershell but for now we will just use the GUI.
3. From the Overview page of your Database select Monitoring and then Diagnostic settings from the left menu.
4. You now have the option to create a new diagnostic setting or edit any that may already exist.
5. Give the setting a name and then tick Query Store Runtime Statistics, Query Store Wait Statistics. Tick Send to Log Analytics workspace and then select the workspace you wish to log to and click Save.
6. Once the diagnostic setting has been set it can take anything up to 30 minutes before data starts to be captured. Make sure there is plenty of activity on the database so that Query Store is active.

[charlene-gif]

Thank you very much for your help with this. I really appreciate it a lot.

I think i now see the problem. I dont have Query Store Runtime, Query Store Wait Statistics as an option.

[BobbyH49]

Ah, that's because you're looking at the Diagnostic settings at the Managed Instance level. Unfortunately, Query Store is measured at the database level and not the instance level.

All you need to do is go back to your Resource Group and look for resources of type Managed Database and then do the Diagnostic Settings for each of these.

How many databases do you have on your instance?

[charlene-gif]

Thanks @BobbyH49 , it looks like i dont have the option to use QueryStore at the moment, which can only mean, we have not yet downloaded it..

I have 4 databases on one instance. I now see, all problems will be resolved once we get Query Store... QQ:. Is Query Store the only way i can get the stats you have put into your monitoring dashboard with ease or can i use one of the applications already configured.. As seen in the screenshot.

[BobbyH49]

From the image you've just sent me, go to Data management marked in Red.

You should then see Databases appear. Click that and then for each database listed, click into it and then select Diagnostic Settings.

At the moment you're trying to setup the Diagnostic Settings at the Managed Instance level but Query Store is at the database level. Therefore, you need to set it up for each of the 4 databases.

Query Store can be disabled on Managed Instance databases but it is enabled by default. So, unless someone has purposely disabled Query Store it should already be available to use.

[charlene-gif]

Got it @BobbyH49 . I was being silly.. i was collecting errors, insights but no statistics.. i will get this switched on for all databases now. Thank you :-)

[charlene-gif]

Just one query left and its all working :-)

[BobbyH49]

Perfect, so this issue is down to the new workbook having a bug. The Pageserver counter only applies to Azure SQL Databases so I've removed it from the Managed Instance workbook.

Please can you refresh the github page and then download the latest version.

[charlene-gif]

I have done the update and it is now working perfectly. Thank you very much for all of your help and i will look out for other cool dashboards :-)

Thank you.

[charlene-gif]

@BobbyH49 , if i wanted to change the workbook to max instead of avg so i can catch each spike that takes place at a specific time, can i just look for all 'avg' and replace it with 'max' in the code or are there a few formulas in there.

[BobbyH49]

You may have to change some headings and labels but in terms of changing the metric just find and replace the following.

from "aggregation": 4

to "aggregation": 3

[charlene-gif]

Okay thank you

[BobbyH49]

Just spotted a bug with QueryMemoryUsedMB which was not showing the right value in the Query Store section. This should now be fixed.