Kentico CMS 6.0 Developer's Guide

SQL queries

SQL queries

Previous topic Next topic Mail us feedback on this topic!  

SQL queries

Previous topic Next topic JavaScript is required for the print function Mail us feedback on this topic!  

SQL query debugging can be turned on and configured either by adjusting certain settings in Site Manager -> Settings -> System -> Debug, or by adding certain keys into the AppSettings section of your web.config file. The following table lists and explains these settings and keys:

 

Setting

Web.config key

Description

Enable SQL query debug

CMSDebugSQLQueries

Enables SQL query debugging and the SQL queries tab in Site Manager -> Administration -> System -> Debug.

Display SQL query debug on live site

CMSDebugSQLQueriesLive

If enabled, SQL query debug information is also displayed at the bottom of each live site page. This option requires SQL query debugging to be enabled.

Debug SQL queries of UI pages

CMSDebugAllSQLQueries

If enabled, SQL queries called by pages of the administration interface (CMS Desk and Site Manager) will also be included in the SQL query debug. This option requires SQL query debugging to be enabled.

Log SQL queries to file

CMSLogSQLQueries

If enabled, SQL query debug log is saved into the logsql.log file in the ~\App_Data folder. This option does not require SQL query debugging to be enabled.

SQL query debug log length

CMSDebugSQLQueriesLogLength

Sets the maximum length of the SQL query debug log on the Debug -> SQL queries tab, i.e. the number of requests for which debug information is preserved and displayed on the tab. If empty, value of the Default log length setting (or the CMSDebugEverythingLogLength key) is used.

Debug SQL connections

CMSDebugSQLConnections

If enabled, SQL connection operations (new, open, close) are logged in the SQL query debug log.

Display stack information

CMSDebugSQLQueriesStack

If enabled, stack is tracked when debugging SQL queries and is displayed in the Context column. This information is only available in the debugging UI and on the live site, not in the debug log written into the logsql.log file.

 

devguide_clip1741

 

It may happen that you specify different configuration in the settings and in the web.config file. In such cases, boolean settings (true/false) need to be enabled at least in one place (in web.config or in settings) in order to be enabled, while log lengths specified in Site Manager -> Settings have higher priority than log lengths specified in the web.config.

 

Here is a list of the keys for easy copy&paste into your web.config:

 

<add key="CMSDebugSQLQueries" value="true" />
<add key="CMSDebugSQLQueriesLive" value="true" />
<add key="CMSDebugAllSQLQueries" value="true" />

<add key="CMSLogSQLQueries" value="true" />
<add key="CMSDebugSQLQueriesLogLength" value="10" />
<add key="CMSDebugSQLConnections" value="true" />

<add key="CMSDebugSQLQueriesStack" value="true" />

 

SQL query debugging can also be enabled using the general settings and keys.

 

User interface

 

On the Debug -> SQL queries tab, you can see a log of the SQL queries called when particular data is loaded. Each record contains the request used to access the given item and the time when it was loaded. Below it, you can find a table listing all SQL queries executed within the context of the request.

 

For each query, there are two lines in the table. The first line contains the exact text of the query and the second shows the number of loaded rows, columns in each row and the total size of the loaded data. The Context column shows the context where the query was called from. The last column of the table displays the exact duration of query execution. The last line of the table displays the total volume of data and loading time for all queries under the given request.

 

Enabling the Show complete context check-box displays complete context (not only the topmost item) in the Context column. Clicking the Clear SQL log button clears all records in this debug log.

 

devguide_clip0371