I need to provide our application group with a "Top SQL" report that shows the sql text and the database from which it ran. I've tried to build a report by instance and by database but do not see where the Top SQL Long Text or even the SQL handle are located in the list of metrics or properties.
We had been using the Top SQL Batches as a starting point - however, since there are 9 databases in the instance, we need to show a breakdown by database name. Could this report be modified? The best case is to show the specific SQL text - not the batch text since the application team is not concerned with SQL batches.
Any direction or assistance with reporting SQL text would be appreciated - thank you!
If you have PI installed for the instance you want the report on, you can use the custom top sql view to build a custom dashboard for the application group.
it can show top sqls by database name (or by user, or by program, or a combination).
Below is an example. will that work for you?
Thank you, Anat. We have PI installed, and this looks like the information we want.
I would like to create this custom dashboard - I think it could help with our app group adoption of Foglight. However, they have specifically requested scheduled reports with this info to be emailed every morning to a list of developers and managers. Could you provide guidance on how to create the custom top sql view that's in the screenshot? As well as a guidance on creating a report from it?
There is a general Foglight ability that can export the view and create a report from it.
The report can also be scheduled. Since you are interested in the SQL Statements themselves - you should create the report as an excel file, to avoid having the text truncated.
We are creating a knowledge base doc that can be publicly shared to explain how this can be achieved.
In the mean time - please provide me you email address and I will forward you the instructions.
Thank you for your help!
The doc can be found in the following link:
Any chance I could also get these instructions, or are they now avaliable in the KB
Instructions are indeed in the KB