I have a stored procedure in my database. As per my knowledge it's pre-compiled subroutine.

Question 1: If I execute following queries from Hibernate, will database create a separate execution plan for each query? In which case SQL Server update/change stored procedure Execution Plan?

EXECUTE myProcedure 'vicky.thakor', 'Routine Check Up'


EXECUTE myProcedure 'chirag.thakor', 'Routine Check Up'

Question 2: This is bit of programming question but you guys may explain me bit of it. In Java there is something called PreparedStatement. PreparedStatement is similar to pre-compiled query.

PreparedStatement generates query like following and put value in place of ?.

SELECT * FROM user_master WHERE user_name = ?

But database will receive the final query like

SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

then how database help to get best performance when using PreparedStatement.

1 Answer 1


First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything.

Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached.

When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc query.

The point is: as long as the actual SQL statement is IDENTICAL (down to the last comma or space), then a possibly pre-existing, cached execution plan is reused and the whole step of determining an execution plan can be skipped. If the SQL statement text varies - even by a single space - the whole process of analyzing and coming up with an execution plan for that query is run again.

I don't know about how Java handles it's prepared statements, but in C# using ADO.NET, when you run a query something like

SELECT * FROM user_master WHERE user_name = @userName

and you supply a value for @userName (e.g. 'vicky.thakor'), then the SQL Server will NOT received what you seem to think it will receive - the parameter is NOT replaced in the query text - instead, SQL Server will execute this as

EXEC sp_executesql @stmt, N'@userName', @userName = 'vicki.thakor'

SQL Server will receive a parametrized query and a list of parameters and their values.

Therefore, if you call this same query again, with a different value for @userName, the actual SQL statement text is identical and the cached execution plan is reused - only the value of the parameter has changed.

  • 1
    +1 It is important to note that since the same plan can be re-used for different parameters, the plan that's cached using one parameter value may not be optimal for another. Think about LastName = N'Smith' vs. LastName = N'Quixote'. If you have cases where you expect different parameter values to deserve different plan shapes, there are several ways you can try to defeat this problem, e.g. OPTION (RECOMPILE) or OPTIMIZE FOR UNKNOWN. Aug 3, 2014 at 18:31
  • @AaronBertrand: absolutely - one of the trickier aspects of plan caching ...
    – marc_s
    Aug 3, 2014 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.