Posted by Erland Sommarskog on 11/15/07 21:38

Inna (mednyk@hotmail.com) writes:
> 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.

All and all, there is no reason why P_main should be slow on its own.
It reads some fairly small system tables, and runs a cursor over 30 rows.

You say that the various P_ALL_DB runs quickly when they run on their
own, but there is one difference when you run it from P_Main: the
procedure will run in the context of a transaction defined by the
INSERT statement. I'm not really sure how that could matter, but then
again, I don't know what is in those P_ALL_DB. But there could be blocking
issues.

One way to test this is to remove the INSERT, and run P_main and see
how that affects the execution time.

> 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.

Pinning tables is very rarely if ever a good idea, and this feature
has been nullified in SQL 2005.

--
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]