Whilst I would say the actual execution plan is the most useful, estimated execution plans have their place – sometimes you just need to see estimates or a quick verification that a change you have made has had some effect on plan shape. I find them helpful to quickly see if a change made to the code, an index, database setting or similar has had an effect on the execution plan without having to wait for the query to finish (especially if I am performance tuning a query that has a long run time).

sp_executesql is also useful – it helps us to execute dynamically created queries and supports parameterisation. It is also used by the .NET SqlCommand class to issue queries to the database engine in a parameterised form, assisting with SQL injection prevention along the way.

I recently found myself troubleshooting some code coming from an application. It was a Slow in the app, fast in SSMS problem so I was using sp_executesql to replicate the application behaviour. I also wanted to get the estimated execution plan. Here is an example query and what I was greeted with when I asked for the estimated plan:

This demonstration query uses the Stack Overflow Database. I am using the small 2010 version provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump and running in compatibility level 160 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM. The query gets all the posts for a given display name, ordered by score. My real-life query was larger and was long-running for many minutes, and so I could not wait for an actual execution plan.

DECLARE @sql NVARCHAR(500) = N'
	SELECT	u.DisplayName,
			p.Title,
			p.Body,
			p.Score
	FROM	dbo.Users u
			JOIN dbo.Posts p
				ON u.Id = p.OwnerUserId
			JOIN dbo.Comments c
				ON c.PostId = p.Id
	WHERE	u.DisplayName = @DisplayName AND
			p.Body LIKE ''%sql%'' AND
			c.Text LIKE ''%sql%''
	ORDER BY p.Score DESC;';

EXEC sp_executesql @sql, N'@DisplayName NVARCHAR(40)', @DisplayName = N'John';

If I get the estimated execution plan:

The top plan was not particularly surprising as that is just the variable assignment, however, the sp_executesql query execution just tells me “Execute Proc” – great, thanks for that!

In contrast, when I get an estimated execution plan for the query without using sp_executesql, I get a more useful estimated execution plan:

DECLARE @DisplayName NVARCHAR(40) = N'John';

SELECT	u.DisplayName,
		p.Title,
		p.Body,
		p.Score
FROM	dbo.Users u
		JOIN dbo.Posts p
			ON u.Id = p.OwnerUserId
		JOIN dbo.Comments c
			ON c.PostId = p.Id
WHERE	u.DisplayName = @DisplayName AND
		p.Body LIKE '%sql%' AND
		c.Text LIKE '%sql%'
ORDER BY p.Score DESC;

So what’s with the different behaviour? Well, the devil is in the detail documentation, which states:

“The Transact-SQL statement or batch in the sp_executesql @stmt parameter isn’t compiled until the sp_executesql statement is executed. The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql“

This is why we get this behaviour – we actually have to execute the query to compile it, without compiling it, we can’t get the estimated execution plan. SQL Server doesn’t know what the query in sp_executesql is until it actually executes it.

The workaround I used (I am sure there are more) to quickly verify if changes I made had an effect on the execution plan was to execute the query and in another window and run sp_WhoIsActive with @get_plans = 1. According to the sp_WhoIsActive documentation, this parameter returns in the result set, the XML of the execution plan which is:

…indeed the “actual” plan that’s running at the time; in other words, the plan will not be recompiled into some other plan by the time Who is Active can get it.

The query needs to be running at the moment you execute sp_WhoIsActive – on a genuinely long-running query this is straightforward. The test query here is relatively quick on my hardware, but the two wildcard predicates and a three-table join mean it ran long enough for me to capture a screenshot from another window for demonstration purposes.:

Your mileage may vary with the sp_WhoIsActive method – clearly that requires specific permissions that you may not have.

References / Further Reading

Adam Machanic – sp_WhoIsActive documentation

Erland Sommarskog – Slow in the app, fast in SSMS

Guy Glantser – Advanced Query Tuning Techniques

Microsoft – sp_executesql (Transact-SQL)