Posted by Erland Sommarskog on 11/14/07 22:33

Inna (mednyk@hotmail.com) writes:
> I have a question
> I created a SP that by using dynamic sql access SP in all databases
> with certain name and get the output into a temp table and returns
> it's contents.
> The stored procedure in other databases is very simple couple of joins
> and where clause for the parameter passed..
> The problem is, there are a lot of activities happening on server, so
> when the execution plan is flashed, it might take a minute to execute,
> in the mean time the second execution will take 1 to 5 second.

Not really sure what you mean when you say "the execution plan is
"fl[u]shed". Do you mean the execution plan of the procedures you
call? Keep in mind that each procedure has its own plan. It may be
the same code, but as far as SQL Server are concerned, they are the
same procedure. So if you start with an empty cache, there will be a
compilation for each database.

However, it does not sound that the execution plan is the issue. For
procedure with a single SELECT statement with a few joins, it should
not take a minute to build the execution plan. It is more likely than
that it depends on whether the data is in cache or not. Since I don't
know about the query or the tables, I cannot really say what you should
do about - except than to examine the execution plans, and see if you
could add indexes to help. Also, you should investigate whether you
have the same plan in all databases.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]