How to investigate the dbFront generated SQL.

+1 vote

How can I look at the SQL that is generated by dbFront? I would like to understand what dbFront is doing and analyze the performance of some of the queries.

in How To by (7.0k points)
recategorized by

1 Answer

0 votes
Best answer

For starters, your database server will have a number of administrative tools you can use to examine the SQL queries generated by dbFront.

dbFront Logs

dbFront can dump out all SQL queries that it runs. The logs entries include timings.

To enable the SQL logging open [ Help ] / [ Settings ] / [ Logging ], enable the Debug Logs, and enable Include SQL.

Warning: This can generate a significant amount of logs so you should disable the SQL logging once done.

dbFront also has a builtin Log Viewer that can be used to view the logs. [ Help ] / [ Log Viewer ]

For more details see: dbFront Logging

SQL Server

One of my favourite, the following SQL Server query dumps out the recent SQL Server query plan cache. Not only does this allow you to see the last number of queries and how often they were run, but it also allows you to easily analyze the queries.

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
ORDER BY cplan.usecounts DESC

Simply run this query after a page load or other operation of your choice and analyse the results.

For details see: Understanding the SQL Server query plan cache

by (64.5k points)
selected by
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
Powered by Question2Answer