14 Replies

We see that a specific DB query is running a lot of times, and would like to create a dashboard which can tell us what users are running this.

We use Database hotspots to find this now, and for each query we can get the pure path and this way find our way to one specific user.

This is a bit cumbersome...

I tried to create a measure for this query by using Transaction based Measures, Database and Execution count. But the SQL statement I can type in in the SQL statement for Database Measure Specific attributes is very short (only 56 characters), and my query is about 500 characters long.

I might be able to use less than 500 characters, but 60 characters is not enough to isolate this query.

Are there any ways to increase the number of characters that you can have in the SQL statement for Database Measure Specific attributes?

There isn't any limit to that field. The better way to do it is to right-click on the DB Statement and select "Create Measure" as this will populate the SQL Statement for you so you don't have to type it in.

You'd then want to create a Business Transaction that filters using the DB Execution Count measure you created and splits by User (You'd need a measure for the User too). You'd want to disable storage of this BT data in the Performance Warehouse to avoid problems with high-splitting. You don't need the data in the PW anyway to accomplish your task.

The result would be a list of users who executed PurePaths containing that DB call. Depending on what you mean by 'users dunning this query' that might be what you're looking for. How do you identify the user?

Which version of dynaTrace do you use? There should not be a 50 character limit on the Execution Count measure - unless this is a limiation of an older dT version that I currently dont have accessible.

But - your approach is correct. You should create a Business Transaction that filters on the Execution of this SQL Statement. If you have UEM you can even do a Visit-based Business Transaction. If not you go with a server-side BT and you might also be able to split by the username or whatever you use to identify the user. With that it should be very easy to identify the user that executes a certain SQL Statement

I tried to create a measure, and used copy/paste, and the limitation seems to be in the paste function. I used your advice, Ted, and managed to get the SQL statement into the Execution Count measure by right-clicking the DB statement.

The next challenge is how to create a measure for the user. We currently run without UEM in production, but I would like to split this by the Client IP on the web requests that contain this SQL in its pure path. Is this possible to achieve?

Just drill down from the database statement to the PurePath dashlet select a PurePath and right click in the PurePath tree on the first node (Web Request) and create measure. There is one called "Web Requests - Client IP" that will capture the IP for you. Is this an internal application with a limited number of IP's? If not please uncheck in the BT settings the "Store results in Performance Warehouse".

I've then created a business transaction with my Execution count measure as filter, and aggregates this on count. I have no splittings (yet. Tried with splittings, but removed them again in case they were causing this to show wrong result)

What I expect to see in my business transaction is how many times my query has been run the last interval, - which should be the same amount of transactions that I see for the query under Database Hotspots

What I do see in my business transaction is way too many results (should have 20 executions, - get several hundred) ... And when I drill down from my business transaction, I get a lot of pure paths that don't run this query at all.

The threshold for the filter uses "Equal to or greater", so if you use a threshold of "0" it is the same thing as creating a Business Transaction with no filter at all, meaning it will give you every single purepath. You should use a threshold of "1" on upper severe.

I've changed the Upper severe threashold to 1, but still I get way too many transactions...

Answer by
Ingunn V.·
Dec 03, 2013 at 03:59 PM

I tried to create a new DB Execution Count measure with a very simple SQL query ("select top 5001") that should get quite a lot of results. I set the Upper Severe threashold to 1. Nothing in the other thresholds.

Your exported session doesnt contain confidential strings - so - I only get to see "select ****". If you can - please send me the purepath including confidential information. If you dontlike to share it on the communtiy feel free to send it to me via email: andreas.grabner@compuware.com