This information can be very useful for tracking down performance problems caused by slow-running database queries. You can also use the query profiler to see details about any queries running on your server and on some databases, see an estimated execution plan as well as actual timing.
To view the query log:
You will see two summary sections and data.
Below the summary statistics, you'll see a listing of the unique queries with the highest number of invocations. Sort this list of queries by clicking the column headers:
| Column Name | Description |
|---|---|
| Count | The number of times that the server has executed the query since it started, or statistics were reset. |
| Total | The aggregate time of all of the invocations of the query, in milliseconds. |
| Avg | The average execution time, in milliseconds. |
| Max | The longest execution time for the query, in milliseconds. |
| Last | The last time that the query was executed. |
| Traces | This column will show the number of different call stacks from the application code that have invoked the query. If capture of stack traces was enabled on your server at the time of the query, clicking the link shows the actual stack traces, which can be useful for developers to track down any issues. |
| SQL | The query itself. Note that this is the actual text of the query that was passed to the database. It may contain substitution syntax. |
To troubleshoot performance of an action, script, or application, particularly if you are not sure which query might be causing a slowdown, you can Reset All Statistics here, then return to execute the action(s) of interest. When you return to this page, the statistics will report a more focused view of the recent actions.
For the cleanest report, you'll want to use two browser windows. In the first one, open the area that you want to profile. In the second, open the query profiler. Reset all statistics in the query profiler, then in the first browser, perform the action/query. After it completes, refresh the second browser tab and you will now see all the queries that took place during your action.
Click the Max column header to see the queries that took the most time. You can also examine any traces to find out more about particular query invocations.
In addition to performance issues, query profiling can help identify possible problems with many actions and queries. To trace an action, have two browser windows open:
It includes the raw text of the query, as well as one example of actual parameter values that were passed. Note that other invocations of the query may have used other parameter values, and that different parameter values can have significant impact on the runtime performance.
Either panel of trace details can be saved for analysis or troubleshooting by clicking Copy to Clipboard and pasting into a text file.
Below the trace details, you can click Show Estimated Execution Plan to get the execution plan of the query as reported by the database itself. It will give you an idea of the cost, row estimate, and width estimate. While less accurate than showing the actual execution plan, it will run quickly.
Note that on some databases, this option may not be available and the link will not be shown.
Click Show Execution Plan with Actual Timing below the trace details, to see the cost estimate as well as the actual time, rows, and number of loops. The split between planning and execution time is also shown. This option requires running the query, so if you are diagnosing issues with a very long running or failing query, you may want to use the execution plan estimate first.
Watch for differences between the estimate and the actual number of rows in particular. Significant mismatches may indicate that there is a more efficient way to write your query.
| previousnext |
| expand allcollapse all |
