Finding the One Query to Tune in a Multi-Query Batch


When you have a stored procedure or application query that has multiple queries inside of it, how can you tell which query to focus on?

For starters, do NOT believe the percentages you see in execution plans.

Let’s take a classic performance tuning issue: scalar functions. I’ll start with the Stack Overflow database – if you want to play at home, best to use the smallest database you can because this query performs terribly – then create a function and a stored procedure:

CREATE OR ALTER FUNCTION dbo.fnGetPostType ( @PostTypeId INT ) RETURNS NVARCHAR(50) AS BEGIN DECLARE @PostType NVARCHAR(50); SELECT @PostType