Hello,
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.
here is the example:
--main procedure
CREATE procedure dbo.P_main
( @User varchar(20),
@Environment varchar(20)
)
as
SET NOCOUNT ON
Declare @Exec nvarchar(4000),@DBNAme varchar(100) , @err
int

Create Table #Return

(
DBName Varchar(100),
UserId varchar(100),
first_name varchar(32),
last_name varchar(32),
ProjectName Varchar(100),
Project_translated_name nvarchar(100) null,
Project_Language int

)
select @Environment = (case @Environment when 'PR' then 'DBPR
%'
when 'SI' then 'DBSI%'
when 'TR' then 'DBTR%'
when 'DV' then 'DBDV%_V%'
when 'SI' then 'DBSI%'
when 'IT' then 'DBIT%'
when 'PP' then 'DBPP%_V%'
else 'ERROR' end)

if @Environment = 'ERROR'
begin
select 'Wrong environment!!!'
return
end
DECLARE @TEMP_VAR TABLE (NAME SYSNAME)

INSERT INTO @TEMP_VAR
SELECT NAME
From Master.Dbo.Sysdatabases with (nolock)
where Name like @Environment
and mode = 0
and status<32

Declare DBCur Cursor fast_forward local For Select
T.name
FROM @TEMP_VAR AS T
order by T.Name FOR READ ONLY option(KEEPFIXED PLAN )

Open DBCur
Fetch DBCur into @DbName
While @@FEtch_Status<>-1
Begin
if 0=(select mode from master.dbo.Sysdatabases with (nolock)
where name = @DbName and status<32)
begin
Set @Exec = 'If exists (Select * From ' + @DBName +
'.dbo.Sysobjects with (nolock) where name =
''P_ALL_DB'' )
Insert into #Return
EXEC ' + @DBName + '.dbo.P_ALL_DB ' + ''''+ @User
+''''

exec sp_executesql @Exec
end
Fetch DBCur into @DbName
End

Close DBCur
Deallocate DBCur

Select DBName ,
UserId ,
first_name ,
last_name ,

ProjectName ,
Project_translated_name ,

Project_Language,
From #Return option(KEEPFIXED PLAN )

Drop table #Return
SET NOCOUNT OFF
go

--procedure in each database
CREATE PROCEDURE dbo.P_ALL_DB
(@Userid varchar(20))
AS
SET NOCOUNT ON
Select
DB_Name() as DBName,
USERID,
first_name,
last_name,
Project_Name ,
Project_Name Project_translated_name,
language as Project_Language
from table1 with (nolock )
join table2 with (nolock ) on .......
Where USERID = @Userid

SET NOCOUNT OFF
GO

Please tell me what I can do to improve this situation.

Thank you

Inna (mednyk (AT) hotmail (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/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.

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

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I removed the INSERT into temp table and it does runs faster and
actually by a lot. But I need a record set to be returned in one set,
how can I achieve without using temp table.

Thank you very much.

Inna (mednyk (AT) hotmail (DOT) com) writes:
How about posting the code of the procedure you call? Not that I'm
sure that it helps, but right now I am completely in the dark of
what is going on.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Nov 18, 6:00 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
The stored procedure in all databases optimised well, all fields are
indexed. So I was thinking since this is development server , we don't
rebuild indexes and if we go on production maybe it will be better...
There is another trick I was thinking about, which I am not sure is
good and possible to do, maybe I should create a job,
which checks if the execution plan is there (this I am not sure I can
do) and if not, execute this SP in background (I mean P_Main). So I
have a question: First: Is it possible? Second: if it is possible,
would it be a good idea? Third: If First+Second = "YES" , how can I do
it?

Thank you

Inna (mednyk (AT) hotmail (DOT) com) writes:
Just because you index all columns, does not mean that you have the
right indexes. You may need multi-column indexes.

Indeed, fragmentation may be a reason for the differences you are seeing.
Fragmenation certainly does not help when you need to read data from
disk.

Whether the execution plan is there or not has little do with it.
What matters is if the data is in cache, you need to read from
disk. Now, here is an important thing: if you query requires
tables to be scanned, that means more pages to be read, and that
means that there will be more crowded in the cache.

It's very clear that you need to analyse your query plans, and see if
you can improve indexing.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx