0

Is there a way to log all Microsoft SQL Server queries executed on a certain server, with their respective durations?

I'd like to be able to analyze all queries will a tool like what PGBADGER does for PostgreSQL. While we're at it: do you know of any such tools?

5

What is your actual goal? Are you trying to track down a troublesome query, performance tune your whole environment, or is this just out of curiosity?

Ultimately, I wouldn't recommend trying to track and log all queries. I suspect you only really care about poorly performing queries which are an actual business impact.

On most production servers, you will have far more queries that either run quickly enough that even a 10-20% performance gain is barely noticeable, or are already optimized well enough to make any performance tuning poor use of your time.

So instead I'd suggest targeting your performance tuning to queries that actually need it.

In any case, you have a few options:

Query Store

If you are running SQL 2016 or higher, the Query Store is my preferred method. It is very low overhead and provides some neat methods to track query performance over time. Books Online has a good article on using it here. Note that it needs to be enabled per-database, and will only capture information once it has been enabled.

DMVs

sys.dm_exec_query_stats stores aggregated (by query plan) stats around query execution. This can be useful for a high level view of what queries are consuming the most resources on a server. You will not get individual execution statistics (unless it's only run once), so it's not really suitable for identifying "one off" performance issues with normally well behaved queries.

It doesn't include the statement text, but you can get this using the sys.dm_exec_sql_text DMF. As an example:

select top 100 qt.text, qs.execution_count, qs.total_worker_time, qs.total_physical_reads, 
                        qs.total_logical_reads, qs.total_physical_reads, qs.total_logical_writes, 
                        qs.creation_time, qs.last_execution_time
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
order by total_worker_time desc

Extended Events

If you are really set on capturing every single query, your best bet will be to use XEs. Understand that there will be a significant performance overhead if you go down this route (especially if you're capturing things like the callstack, or doing something crazy like filtering by sql text with a predicate), and it's not something I'd recommend doing by any means.

If you set something like this up, I would suggest being extremely careful and not allowing it to run without monitoring its impact.

| improve this answer | |
2

Read about the features of PGBadger, and with those features, Query Store is definitely the way to go, and i have only a few things to add to the answer from Dan.

The data in the DMV's are reset when the server is restarted for whatever reason. This is not the case for the QueryStore and is one of the big features of this. Also for the DMV sys.dm_exec_query_stats, some of the data can be flushed if the server gets under memory pressure.

In SqlServer 2017, latest CU, the query store also contains wait statistics, which you would need to help you understand why a query is regressing.

This is not the case in SqlServer 2016 (hoping for a future SP to include it) You will therefore have to setup some logging of wait statistics on your own. i have build a version of the code from this article to do that: http://www.patrickkeisler.com/2013/12/collecting-historical-wait-statistics_10.html (be sure to read the articles he is linking to as well)

If you are on an earlier version of SQL Server, you need to collect the query statistics yourself (the data from sys.dm_exec_query_stats) on a busy server, this needs to be done rather frequently, since the data can be flushed in pressure situations as i mentioned.

As Dan, i am not sure what you are wanting to use the 'duration' for but if it is for finding ressource/query issues, you need to monitor more numbers and changes in numbers, like I/O statistics, Wait Statistics, Memory Usage and more

before going into to much detail, it would be nice to hear what exactly you are trying to achieve

For a complete out of the box tool that can deliver all of the stuff that PGBadger delivers, you will have to get a commercial product like the ones from Idera, RedGate and others

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.