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.