

Source

If we write code working with databases, usually it is beneficial to preview what SQL is used during the system use. It is the most useful when we want to use some ORM like Entity Framework. Each query executed by Entity Framework is transformed into some SQL query, the it is executed on database. Profilers is mostly use to:

complexity check of generated SQL query

query optimization

checking of existence of common querying problems (e.g N+1 problem)

In my current projects I use SQL Server the most often. That’s why one of my favourite tool to profile SQL queries is Express Profiler. It supports all versions of Sql Server Database (also Express).

How to download?

To start using this program we only need to download the proper file and execute given package. You can download it directly from Express Profiler web page: https://github.com/OleksiiKovalov/expressprofiler.

How to use?

If we download this program we can execute it and configure a database inside.

Application configuration is easy and reduced only to setting a proper database connection information. On screen above you can see 4 fields in configuration:

name of DB server

type of authentication

optionally, user and password

That’s all required configuration. After that, we can just press the “Start trace” button. Then we can get back to testing application and perform some operation, which we want to test.

Tracing

We can notice that Express Profiler window will be getting populated.

It is a list of queries executed on the database. For each one of this we can see:

complete query text

database which they are executed on

duration

number of reads and writes

This is the basic set of information but it gives us a sufficient level of knowledge to investigate what query is executed.

Filtering

That is the main feature of Express Profiler. But it also provide us some help to maintain the systems where it is many queries executed at a single point of time. We can set up a filtering for multiple parameters (e.g. database name, duration or query text.

This is a very simple tool but very powerful for me. It is also free.