Sql-server – Stored procedure executes too long

querysql serversql-server-2008stored-procedures

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?

Best Answer

Take care when comparing stored procedure execution times to inline SQL execution times.

A stored procedure 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 stored procedures can and do generate different plans with different performance characteristics.

For your particular case, can you show us the definition of the stored procedure?

Related Question