ColdFusion 2018 performance monitoring toolset can help monitor a heterogeneous database server environment that may consist any of the ColdFusion supported databases. It helps users in identifying the bottleneck at the database or data source level. It can pin point the ColdFusion queries which are slowing down the entire ColdFusion application, so that developers or database administrators can take the appropriate step to fix these problematic database queries.

Database monitoring is divided into multiple sections, broadly categorized as:

• Database overview
• Data source detailed page
• Query detailed page
• Actively running queries
• Database Pool statistics

Database overview and Data source detailed page contains almost same sort of charts, the only difference is, database overview page shows information of multiple data source. However, data source detailed page shows information only about the select data source. Here are the list of charts available on these pages:

Average query time – This chart shows the average query execution time per data source. We show only 5 data sources by default on this page.

Throughput – This chart shows the number of queries executed per unit of time. This chart is only applicable to the database overview page.

Top slow queries – This is the piece of information which every developer or DBA would be interested in. It really helps in nailing down the culprit query in the ColdFusion application. It sorts all the queries on the basis of time taken to execute the specific query in descending order. This report helps identifying queries by template name, application name and line number. It takes you to the specific instance of a query that is slow, along with the SQL statement for the query. All these queries are actually clickable, which helps user to drill down to the detailed query page. to investigate it further. We will talk about query detailed page later in this blog post. To improve performance of these queries, user might have to tune the queries listed in this table. If the result of a query is static, you can improve performance by caching the query using ColdFusion’s query cache.

Tracking cached queries – The Cached Queries report lists the queries that were cached. You can view a list of cached queries or details about an individual query. If the execution time of a query is low, determine if you really need to cache it. If the execution count is high, tune the cachedafter and cachedwithin settings of the query.

Queries per node – This chart shows all the ColdFusion queries executed across all the ColdFusion nodes as part of cluster or group.

Queries per application – This chart shows the distribution of ColdFusion queries executed against various ColdFusion application.

SQL distribution – This chart categorizes all the ColdFusion queries according to the data manipulation language like, select, insert, update and delete. It helps user to understand, what kind of queries are being used on this ColdFusion data source.

CF transaction – This chart shows the distribution of success or failure of cftransaction tag.

Data source details – This section contains details about the data source. It has information like database name, database server name, database host name and database port. This is applicable only to the data source detailed page.

We have seen above, the type of information which we capture while monitoring database queries. However, there is no information about the SQL statements being executed on the database. How to get to this useful information in order to resolve the issue. We have a query detailed page in Performance monitoring toolset which displays the detailed information about the SQL statement, template path, line number and time taken to execute the query etc. Query detailed page has three tabs viz. Basic info, Invocations and References.

Basic Info tab contains lot of useful information about the SQL query

Invocations tab contains the information about the SQL statement being executed in the ColdFusion template. It has information like SQL statement, minimum execution time, average execution time, maximum execution time and the hit count of the SQL statement.

If you have noticed then you might have observed that we are using Query name to identify the SQL statement in the ColdFusion. There are chances that user might using the same query name multiple times on the same page or across different CFM templates or application. So, how to identify which CFM template is actually using this query name. That is where References tab comes in and show the list of all the CFM templates where this query name has been used.