sys.dm_exec_query_stats is a Dynamic Management View (DMV) in Microsoft SQL Server that provides performance statistics for cached query plans in SQL Server. It’s used for monitoring and identifying performance issues with SQL queries. This DMV can be very useful for database administrators and developers to analyze the performance of SQL queries, understand how often they are executed, and identify which queries are consuming the most resources.
Here is a basic example of how you might use sys.dm_exec_query_stats to get information about query execution times, CPU time, logical reads, and so on:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.execution_count, qs.total_logical_reads, qs.total_logical_writes, qs.total_worker_time, qs.total_elapsed_time, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY qs.total_elapsed_time DESC; |
This query returns statistics about the executed SQL statements, including the number of times each statement was executed (execution_count), total logical reads and writes, total worker (CPU) time, total elapsed time, average elapsed time per execution, and the text of the SQL statement itself.
In PostgreSQL, there isn’t a direct equivalent to SQL Server’s sys.dm_exec_query_stats DMV, but you can get similar insights using a combination of PostgreSQL’s system catalogs and views, particularly the pg_stat_statementsextension. This extension provides a means to track execution statistics of all SQL statements executed by a server.
First, ensure that the pg_stat_statements module is enabled in your PostgreSQL instance. This can usually be done by adding pg_stat_statements to the shared_preload_libraries in your PostgreSQL configuration file (postgresql.conf), and then restarting the PostgreSQL server. You may also need to create the extension in your database with:
|
1 |
CREATE EXTENSION pg_stat_statements; |
Once pg_stat_statements is enabled, you can query its view to get query performance statistics. Here’s an example query similar in spirit to the SQL Server example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT query, calls, total_time, rows, min_time, max_time, mean_time, stddev_time, blocks_hit, blocks_read FROM pg_stat_statements ORDER BY total_time DESC; |
This will give you:
query: Text of a representative query, with some values anonymized.calls: Number of times the statement was executed.total_time: Total time spent in the statement, in milliseconds.rows: Total number of rows retrieved or affected by the statement.min_time,max_time,mean_time,stddev_time: Minimum, maximum, mean, and standard deviation of the execution times for the statement, respectively.blocks_hit: Number of times disk blocks were found already in the buffer cache, avoiding disk reads.blocks_read: Number of disk blocks read.
This view is extremely useful for identifying slow queries, frequently executed queries, and queries that are reading a lot of data from disk.
Keep in mind that pg_stat_statements tracks queries across all databases in the server by default, and its data persists across server restarts until it’s explicitly reset using functions like pg_stat_reset() or pg_stat_statements_reset(). Permissions to access pg_stat_statements data can be managed at the PostgreSQL role level.
