Posted by Inna on 11/15/07 14:55

> 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


Hello again, thank you for helping me.
I ran a profiler when I execute dbo.P_main after an hour of not
executing it with EventClass SP:Recompile and in EventSubClass I get 3
which means Object not found at compile time. If I execute this SP all
time without a big pause I don't get this event in profiler, that is
why I guessed that the execution plan is gone.
In the mean time the P_ALL_DB doesn't take long time, if I run it
alone without calling it from the P_Main. BTW the main procedure
executes this sp from 30 databases.
I really cannot understand why such a big difference between the first
and all consecutive executions and how I can fix it. I was thinking to
PIN tables but I would have to do it for 30 databases. Is it a good
idea? Every table used, might have from 50 to 2000 rows depending on
database.

[Back to original message]