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 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?
I've seen this in the past where the query plan is "wildly wrong" - it's worth remembering that you will use different query plans when executing this via SSMS to via an application (such as one written in .Net).
You can demonstrate this if you SQL server has a linked server; connect to your "real" server, and see it run fast; reconnect to the linked server, and execute again, and you'll see the performance difference.
It was for SQL Server 2000, but maybe will apply in your case: https://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure
It was an opposite situation, but maybe also there is a problem with ANSI NULLS?
