We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management Studio takes 2 seconds. SQL Server is under load when I catch those queries with memory and CPU at the 20 percent.
What I can do?
Take care when comparing StoredProcedure execution times to inline SQL execution times.
A StoredProcedure gets compiled into ONE execution plan to fit all possible parameters. Depending on your query this may not be a good plan for Any parameters, but instead the least-worst for all parameters.
When executing Inline, the optimiser can see both the query AND the parameters. It can then compile a plan specifically for that case. It can take conditions such as
OR @param IS NULL and turn it into
OR TRUE and then be able to pick indexes and approaches that fit that specifically.
In short, Inline and StoreProcedures can and do generate different plans with different performance characteristics.
For your particular case, can you show us the definition of the StoreProcedure?Tweet