CODEWITHSUNDEEP
sqlsql-serverloggingtrackingdatabase-performance
Ben.Yan
Ben.Yan

Reputation: 118

Configure MS SQL Server to log query and its execution time

I had a program using SQLconnector to connect to MS SQL 2012, I want know SQL query execution time. I am aware there is a SQL Server Profiler, but I can't configure it correctly to capture any query execution time. I am also aware that I can add timer within program but i can not change code easily. All advice welcome. thanks

Upvotes: 0

Views: 2860

Answers (2)

Alex Yu
Alex Yu

Reputation: 3547

AFAIK SQLConnector is for MySQL, so I don't understand how you can work with SQLServer 2012.

If you need profiling in SQLServer - there is nothing better than SQL Server Profiler.

I can't configure it correctly to capture any query execution time

It must be easy:

  1. Event Selecion - you need SQL:Stmt Completed (uncheck everything else). You need to select duration.
  2. Column Filters - create filter by Login Name (make special login if you need it), TextData, Spid (if you can figure out how to obtain it)

Upvotes: 1

Nath_Math
Nath_Math

Reputation: 259

This is not supported by default but you can do this using several techniques depending on what are your needs.

Do you need to capture every statement including SELECT? If yes then I suggest you still use SQL Server Profiler or SQL Server Traces

If you only need to capture DML statements (excluding SELECT) you can try setting up triggers on tables you want to audit.

There are also DDL triggers that can help you catch DDL statements (as far as I know there is no way to capture all DDL statements using DDL triggers).

So, there are many options here but it all depends on what are your needs.

Upvotes: 0

Related Questions