Query longer in SP: query performance help needed

Inna (mednyk@.hotmail.com) writes:

Quote:

Originally Posted by

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.

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

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

Quote:

Originally Posted by

>
Pinning tables is very rarely if ever a good idea, and this feature
has been nullified in SQL 2005.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>

Hello,
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@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

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

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.

Quote:

Originally Posted by

Inna (med...@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

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

Quote:

Originally Posted by

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.

>
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, 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- Hide quoted text -
>
- Show quoted text -

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

Quote:

Originally Posted by

The stored procedure in all databases optimised well, all fields are
indexed.

Just because you index all columns, does not mean that you have the
right indexes. You may need multi-column indexes.

Quote:

Originally Posted by

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

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.

Quote:

Originally Posted by

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)

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.

Wednesday, March 21, 2012

query performance help needed

No comments:

Post a Comment

Query longer in SP

Blog Archive

About Me