Obtaining Execution Plans

For selected databases, the GUI offers the ability to obtain a SQL statement's estimated execution plan directly from the database server. These databases are: SQL Server, Oracle, PostgreSql, MySql and SQLite. The execution plan data is displayed in the format reported by the targeted database.

To obtain the execution plan for a query, select the query in one of the views on the snapshot tab in the GUI and navigate to the Query Plan Statistics tab. If the target database doesn't support execution plans to be obtained in a reasonable form, this tab isn't available.

Make sure the connection string is correct and click 'Get Plan' on the right. It's mandatory that the ADO.NET provider related to the factory selected is available on your system. It will use a quick check (which times out after 1 second) whether the connection succeeds. If the connection takes longer than 1 second to establish the connection open will fail.

PostgreSql specific

If you're using PostgreSql, you have to adjust the DbProviderFactory definition in the ormprofiler.exe.config file, as Npgsql isn't registered in the GAC by default, so the factory isn't found on the system where the client is ran.

A pre-defined example is defined in the ormprofiler.exe.config file, just uncomment it and adjust the version and file location. If the factory isn't found, the client can't obtain the execution plan because it can't instantiate ADO.NET objects without a factory.

SQL Server specific

SQL Server execution plans are obtained over OleDb instead of SqlClient. The reason for this is that SqlClient wraps all queries in a call to sp_executeSql, a stored procedure which makes it impossible to obtain the query plan for the query.

To work around this, we use OleDb and declare the parameters up front. This should give a reasonable estimate of what the query's performance is on the RDBMS. To be able to use this, it's required that the SQL Server instance accepts connections over OleDb.