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://expressprofiler.codeplex.com/.
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.
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
- 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.
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.