Performance decline in parallel SPs execution

Hi,

We have a process that builds our data warehouse.

The processes execute SPs in serial order.

Each SP builds separate table.

Each table is build destructive (truncate and Insert Into).

I've tried to change the configuration by running 4 SP in parallel by SSIS to shorten the update time.

I've noticed in two declines in performance:

1. Each SP execution time is higher in the parallel execution in around 50% then in the serial execution. The CPU utilization is the same.

2. On each parallel execution we have a decline in performance of around 5 -10% compare to the previous parallel execution.

Do you have any directions to inquire?

Btw – we have Itanium 64bit x8 with 32GB memory

Thanks,

Assaf

It sounds like the stored procedures may be blocking each other when running in parallel.

You can verify if there is blocking by querying sys.dm_exec_requests and looking at the blocking_session_id column.

If you do see blocking you will want to look at the locks that stored procedure is taking, and determine if there is any way to reduce the locks held.

|||

Hi,

Sorry that I forgot mention it.

There is no locks between tables.

each SP right to only single table and Other SP do not read from other written tables.

Also validated it on server level.

Any other ideas?

Thanks,

Assaf

|||

If the stored procedures are inserting into tables in the same database then they could be contending for both physical IO on the data device and also be writing to the same log devices. The storage devices are quite often the bottlenecks in systems like this, and regardless of how fast the processors can run the disk can only write at a certain rate.

As for the 10% degredation I'm not so sure. Is the 10% cumulative run on run or are the subsequent run all just 10% faster than the first? What is the recovery model of your database? If it is not Simple then it may be having to extend the logs if they are not being cleared between runs. On a data warehouse which is completely rebuilt and does not then have data written to it you can probably just use Simple recovery and do a full backup after the load if necessary.

Performance anomalies with sp_executesql

Hi all,
I am facing a performance problem with a piece of dynamic
SQL (on SQL Server 2000 SP4), and it really has me stumped.
The execution times for the same query executed using
sp_executesql and directly from Query Analyzer differ by
many orders of magnitude, and I have so far been unable to
find out why.
The statement in question looks as follows:
sp_executesql N'SET ROWCOUNT 9; SELECT [SID] FROM [S] WHERE EXISTS
(SELECT * FROM [A] INNER JOIN [Q] ON [A].[AID] = [Q].[AID] INNER JOIN
[P] ON [Q].[QID] = [P].[QID] WHERE ([A].[X] <= 0 OR @.P1 = ''false'') AND
([A].[SID] = [S].[SID]))', N'@.P1 nvarchar(4000)', N'true'
This is actually a heavily reduced version of a real life
statement, but the necessary parts to display the problem
have been preserved - I'm aware that the query can be
optimized. The use of sp_executesql is mandated by the
application server framework.
Executing the statement above makes the database enter what
looks like a livelock state with the CPU fully loaded. So
far I have killed the process when it has executed for more
than an hour on a reasonably powerful machine (3 GHz, 4
GB ram).
If I execute the query from Query Analyzer (without
sp_executesql) with the value of @.P1 substituted in it
returns immediately (after less than one second), which I
would guess eliminates obvious problems like missing
indexes. The tables in use are reasonably small (a million
records in P, in the order of 1000 records in the other
tables), so in any case an execution time of an hour is way
over the top.
If I execute the statement on a structurally identical
database with fewer records in the tables it completes
immediately. There are no apparent consistency problems in
the first database; DBCC CHECKDB comes up with nothing.
If I reduce the row count to 8, remove the joins on the P
and Q tables or eliminate some of the constraints in the
WHERE clause the statement completes immediately. Replacing
the value of @.P1 with 'false' makes the query complete in
about a minute.
I realize that coming up with constructive input is
extremely difficult without access to the actual database in
question, but I'm open to any suggestions that you might
have about how I could look further into this. Any hints
about how I might eliminate the livelock would be very much
appreciated.
Best regards,
Mikkel Lauritsen
*** Sent via Developersdex http://www.examnotes.net ***Mike
Have you seen that an optimizer is able to use indexes defined on the
table/s?
"Mikkel Lauritsen" <renard@.nospam.dk> wrote in message
news:ucDEqJ1lFHA.1044@.tk2msftngp13.phx.gbl...
> Hi all,
> I am facing a performance problem with a piece of dynamic
> SQL (on SQL Server 2000 SP4), and it really has me stumped.
> The execution times for the same query executed using
> sp_executesql and directly from Query Analyzer differ by
> many orders of magnitude, and I have so far been unable to
> find out why.
> The statement in question looks as follows:
> sp_executesql N'SET ROWCOUNT 9; SELECT [SID] FROM [S] WHERE EXISTS
> (SELECT * FROM [A] INNER JOIN [Q] ON [A].[AID] = [Q].[AID] INNER JOIN
> [P] ON [Q].[QID] = [P].[QID] WHERE ([A].[X] <= 0 OR @.P1 = ''false'') AND
> ([A].[SID] = [S].[SID]))', N'@.P1 nvarchar(4000)', N'true'
> This is actually a heavily reduced version of a real life
> statement, but the necessary parts to display the problem
> have been preserved - I'm aware that the query can be
> optimized. The use of sp_executesql is mandated by the
> application server framework.
> Executing the statement above makes the database enter what
> looks like a livelock state with the CPU fully loaded. So
> far I have killed the process when it has executed for more
> than an hour on a reasonably powerful machine (3 GHz, 4
> GB ram).
> If I execute the query from Query Analyzer (without
> sp_executesql) with the value of @.P1 substituted in it
> returns immediately (after less than one second), which I
> would guess eliminates obvious problems like missing
> indexes. The tables in use are reasonably small (a million
> records in P, in the order of 1000 records in the other
> tables), so in any case an execution time of an hour is way
> over the top.
> If I execute the statement on a structurally identical
> database with fewer records in the tables it completes
> immediately. There are no apparent consistency problems in
> the first database; DBCC CHECKDB comes up with nothing.
> If I reduce the row count to 8, remove the joins on the P
> and Q tables or eliminate some of the constraints in the
> WHERE clause the statement completes immediately. Replacing
> the value of @.P1 with 'false' makes the query complete in
> about a minute.
> I realize that coming up with constructive input is
> extremely difficult without access to the actual database in
> question, but I'm open to any suggestions that you might
> have about how I could look further into this. Any hints
> about how I might eliminate the livelock would be very much
> appreciated.
> Best regards,
> Mikkel Lauritsen
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Mikkel,
A couple of sugestions:
- Qualify the tables with the owner
dbo.[A], dbo.[Q], dbo.[S]
- Why to declare @.P1 as nvarchar(4000), is not it enough nvarchar(5)?
- If @.P1 is nvarchar then use an nvarchar constant in the comparison:

> @.P1 = ''false''
@.P1 = N''false''
AMB
"Mikkel Lauritsen" wrote:

> Hi all,
> I am facing a performance problem with a piece of dynamic
> SQL (on SQL Server 2000 SP4), and it really has me stumped.
> The execution times for the same query executed using
> sp_executesql and directly from Query Analyzer differ by
> many orders of magnitude, and I have so far been unable to
> find out why.
> The statement in question looks as follows:
> sp_executesql N'SET ROWCOUNT 9; SELECT [SID] FROM [S] WHERE EXISTS
> (SELECT * FROM [A] INNER JOIN [Q] ON [A].[AID] = [Q].[AID] INNER JOIN
> [P] ON [Q].[QID] = [P].[QID] WHERE ([A].[X] <= 0 OR @.P1 = ''false'') AND
> ([A].[SID] = [S].[SID]))', N'@.P1 nvarchar(4000)', N'true'
> This is actually a heavily reduced version of a real life
> statement, but the necessary parts to display the problem
> have been preserved - I'm aware that the query can be
> optimized. The use of sp_executesql is mandated by the
> application server framework.
> Executing the statement above makes the database enter what
> looks like a livelock state with the CPU fully loaded. So
> far I have killed the process when it has executed for more
> than an hour on a reasonably powerful machine (3 GHz, 4
> GB ram).
> If I execute the query from Query Analyzer (without
> sp_executesql) with the value of @.P1 substituted in it
> returns immediately (after less than one second), which I
> would guess eliminates obvious problems like missing
> indexes. The tables in use are reasonably small (a million
> records in P, in the order of 1000 records in the other
> tables), so in any case an execution time of an hour is way
> over the top.
> If I execute the statement on a structurally identical
> database with fewer records in the tables it completes
> immediately. There are no apparent consistency problems in
> the first database; DBCC CHECKDB comes up with nothing.
> If I reduce the row count to 8, remove the joins on the P
> and Q tables or eliminate some of the constraints in the
> WHERE clause the statement completes immediately. Replacing
> the value of @.P1 with 'false' makes the query complete in
> about a minute.
> I realize that coming up with constructive input is
> extremely difficult without access to the actual database in
> question, but I'm open to any suggestions that you might
> have about how I could look further into this. Any hints
> about how I might eliminate the livelock would be very much
> appreciated.
> Best regards,
> Mikkel Lauritsen
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Hi Alejandro,
thanks a lot for the suggestions - I really appreciate your
prompt feedback on this.
I have tried making the suggested changes, but unfortunately
they have no effect on the livelock that I'm seeing.
The size of the parameter is beyond my immediate control,
because the entire statement is generated by the application
server framework - I have only defined a prepared statement,
and the SQL is then generated for me at runtime.
And I have to admit that I missed out on the constant not
being nvarchar (good catch!), but as mentioned above
changing that doesn't make any difference.
So far I have to say that I'm leaning more and more towards
thinking that this is caused by a bug in the execution
planner, or perhaps an inconsistency in one of the tables
which isn't detected by DBCC CHECKDB.
Best regards,
Mikkel
*** Sent via Developersdex http://www.examnotes.net ***|||Mikkel -
We are running into the same issue did you ever find a solution or workaroun
d?
Thanks
Mike

Performance and Execution Plan

Performance !!!

Hi!

I am trying to execute an stored procedured that have already been used in another server (the test server ) but it takes 25 hours and in the another server takes just 40 minutes. !!

I have reviewed the indexes, statistics an so on, I have exactly the same quantity of data but cannot explain why does it takes so long in the server that is supposed to be the better one.

When I show de Execution Plan in the Query Analyzer in one server I get:

- Select (0%)

- Compute scalar (0%)

- Remote Query (100 %)

When I executed in the another one I get:

- Select

- Compute Scalar

- Nested Loops Left Outer Join (80%)

- Merge Join / Right

- Remote Query

- Sort

- Hash Match Right …

Even if I make a simple select by a certain period, in one server takes so long and in the another one just a few minutes.

Coul anybody please help me?

Sure... How much memory is on the test server versus the "other" server? How much CPU? 32 bit for both of them? Same operating systems? More data?|||By looking at the execution plan, they don't look like the same query.|||Can you please post some sample queries? If it is distributed queries then it is possible that you are getting a plan that is retrieving large number of rows from remote data source. But this depends on the statisitcs on any local tables that you are joining with and the query. There are ways to force joins to happen on the remote side. But anyway, you need to give more details before I can suggest any solution.|||

There is a chance that on server A you are querying it's local tables

where on server B you are querying remote tables (on server A).

If this is the case, try not to commit huge transactions over the network.

Also the source code for the query and the exact location of the tables would help define the problem

|||

Thank you for all your answers, let me give a little more of detail to clarify.

In my real environment I have this:

I have the problematic stored procedure in one server (let me say "Server A") and this procedure gets
data from Itself and server B (the linked server).

Basically the procedure makes a select from remote tables into a temporary table with
just a "where" of dates and another inserts in local tables.

To simplify the case I extract just the main select I use and it is exactly the same:
takes hours and hours to get the results of the "select".
But this "select" when is executed in my Test environment takes just a few minutes. (I am talking of 25 Hours vs. 40 Minutes aprox.)

What I have already checked is to have the same indexes, same statistics created
in both servers of both environments (server A, B, X, Y)

I also may say, the databases that I have in my Real Environment are just a Restore of the Databases that I have in my Test Environment.

The Hardware...
Real Environment:
Server A: 4 GB RAM,
Server B: 4 GM RAM (The linked Server)

Test Environment:
Server X: 4 GM RAM
Server Y: 1 GB RAM (The linked Server)

The Query...

For this query I checked the next points after executed in both environments:

Results of Execution Plan:
The results between Test an Real are very different, because while in the Real Environment there are a lot of
Logical and Physical Operators that describe the steps followed.

In terms of Costs the most expensive is "Nested Loops/Left Outer Join" with
80% of total execution plan. (this step just does not appear when I use my test environment).

In the Test Environment the only steps that i can see in the execution plan are:

Select 0%,

Compute Scalar 0% and

Remote Query 100%|||

For starters, I want to suggest that you create a view on the linked server with the query and use it instead remotely. This will provide the best performance and the plan will be local to that server in most cases. This provides better maintainability also and more tuning options. Also, please make sure that you are running the same version + service pack of SQL Server in your environment for comparison purposes.

Having said this, since all of the tables in the query are remote tables I don't see why we should even be trying to do any operations locally. It is possible that your test queries are little bit different or you are using different parameters. I am assuming that you are comparing your SP call with the same parameters between the two environments.

|||

Well, the thing here is that the stored procedured that I have to use have more statements, some of them need to access local tables. The queries that i used in both environments are exactly the same and over the same quantity of data (just a copy of one into the another).

As the main cost that i have is in the extract of the stored procedure that I show here, I have also tried to make the select step by step. I mean adding table by table in order to find any reason for the excessive time spend. I have noticed that after adding the table that have an “inner join”the process gets worst. So please, if you have any other idea let me know.

Performance - Slow load times for SSIS Packages

Is there any information around what the SSIS packages are doing in the first 5-10 seconds of execution, and ways to speed this process up?Validation then Pre-Execute are the first things to happen as I recall. You can see all the events being raised prior to the Execute event itself in the BIDS Progress or Output windows. You can also capture lots if information including this through logging. DTEXEC can also dump all this information to the console, so easily captured when not running interactively, just not through BIDS, thereby cutting out the debugging overhead.|||It would be nice to know how to get rid of the delay when loading packages, so that tasks would fire instantaneously. Every package, no matter how simple, seems to have a 5-10 second delay.|||

Make sure the SSIS service is running - it caches information about packages that have run, making subsequent package invocations go faster.

Cheers/Kristian

Performance -- Plan Cost and Scan

I am looking some query stats and have a query.
Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahead
Reads - 3,976
Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead reads
100
Query 2 obviously runs around 95% faster and it would seem difference
between plan cost is very little.
What is the downside of having the plan cost go up? Under heavier load
would this start to perform poorly because of that? At what point does the
plan cost become more important than the other items? Or are the logical
reads a better indiciator of which way to go.
Sorry for so many questions -- really starting to use the tools to tweak
queries and want to make sure I am going down right paths.
Thanks!!!Execution plan costs are worth looking at, but must be taken with a
bit of skepticism. The cost in the execution plan is just an
estimate. The actual results can be quite different. You can even
get completely different execution plans sometimes just by updating
statistics.
Roy Harvey
Beacon Falls, CT
On Tue, 28 Feb 2006 10:46:07 -0500, "Brian" <brian@.nospam.com> wrote:

>I am looking some query stats and have a query.
>Query 1 Plan Cost: 5.312 -- Execution 20.497 seconds -- total time 23.848
>seconds --Physical Reads 1,404, Logical Reads, 927,701,Scans 621, Read Ahea
d
>Reads - 3,976
>Query 2 Plan Cost 9.469 -- Exection 00.143 seconds -- total time 02.016
>seconds -- physical reads 0, logical reads 7146, sans 622 , read ahead read
s
>100
>
>Query 2 obviously runs around 95% faster and it would seem difference
>between plan cost is very little.
>What is the downside of having the plan cost go up? Under heavier load
>would this start to perform poorly because of that? At what point does the
>plan cost become more important than the other items? Or are the logical
>reads a better indiciator of which way to go.
>Sorry for so many questions -- really starting to use the tools to tweak
>queries and want to make sure I am going down right paths.
>Thanks!!!
>