Viewing SQL Execution Plans in OutSystems using SQL Sandbox
A lot of what’s happening in our OutSystems app is driven by data and it’s essential that we gain visibility on how queries run inside the database. SQL Execution Plan is a graphical representation of the various steps involved in retrieving data from tables/entities. After executing SQL queries in OutSystems, the database engine promptly creates multiple execution plans and chooses the one that delivers the most efficient performance in producing the results. The execution plan is generated based on the current configuration of the entity and how your query was formed. Certain configurations (e.g. indexes) can be missed thus viewing the execution plan can help to check what needs to be tweaked.
I am very happy to share that I was able to add a capability on the forge component SQL Sandbox to access the Execution Plan of a query which is now available in the latest version — https://www.outsystems.com/forge/component-overview/5900/sql-sandbox
How to generate Execution Plans in OutSystems using SQL Sandbox?
The prerequisite is to have a database connection that has a SHOWPLAN permission.
In an on-premise setup, this can be as simple as granting the OutSystems runtime account with this permission.
For cloud, there is a bit of a process involved because we don’t have control over the runtime account nor OutSystems support can grant this permission. However, OutSystems support can create separate read-only database access using the process defined at https://success.outsystems.com/support/enterprise_customers/maintenance_and_operations/access_the_database_of_your_outsystems_cloud/.
We need then to add it to the Database Connections for SQL Sandbox to use.
Inside the SQL sandbox, you can use the following options, selecting the right Database connection that has the permission.
After executing a download link should appear for the execution plan which can be opened using SQL Server Management Studio (SSMS) -
If you got SSMS installed, opening the sqlplan file should give you this result
A lot of valuable information can be seen in SQL execution plans, most of the time it’s used to check if Indexes are efficiently used. It is crucial to focus on key aspects such as the operator with the highest percentage, the thickest arrow, the largest subtree cost, unordered data, and SCAN operators, among others. Lots of good article covers this in detail — https://www.google.com/search?q=analyze+execution+plan+sql+server
