Re: query performance help needed

Inna (mednyk@xxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: query performance help needed
    ... how that affects the execution time. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ... rebuild indexes and if we go on production maybe it will be better... ...
    (comp.databases.ms-sqlserver)
  • Re: Slow UPDATE and DELETE on SQL Server 2000
    ... I've increased the size of the TempDb database and noticed ... When I ran the update query I got an error message about not being ... I did read the execution plans incorrectly. ... > SQL Server process, memory, as well as the SQL Server specific counters. ...
    (microsoft.public.sqlserver.server)
  • Re: DTS ActiveX Scripting
    ... SQL Server Agent Account if the owner of the job is a SQL Server sysadmin ... As you can see the first> ActiveX script runs ok. ... DTSRun: Loading... ... ActiveX Scripting> encountered a Run Time Error during the execution of the> script. ...
    (microsoft.public.sqlserver.dts)
  • Re: Order of execution in logical expressions
    ... expressions. ... But this has nothing to do with execution order. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.tools)
  • Re: How to capture SQL statement messages in DTS log file
    ... I have several SQL Server 2000s and a couple of SQL Server 2005 servers ... Then instead of PRINT in your DTS package, ... The execution of the following DTS Package succeeded: ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.dts)
Loading