performance vary for the same sp in two different db

Discussion:

(too old to reply)

Taylor

2010-01-19 08:17:12 UTC

Hi,

I have a sp in database A. And the sp will access tables in database
A. Now somehow the sp will be moved to database B and the performance
goes worse since it moved. The cache config, tempdb, dboption are all
the same for the database A and B. Is there any other possible reasons
for the performance degradation?
I am out of mind. The query plans are different for the two sps in two
databases. What will affect the query plan?

unknown

2010-01-20 06:12:18 UTC

Permalink

Hello ,

How about the tables that the sp is accessing.
Do they have "same" schema /indexes and "rows" in both the
databases?

HTH,
Vivek

Post by Taylor
Hi,
I have a sp in database A. And the sp will access tables
in database A. Now somehow the sp will be moved to
database B and the performance goes worse since it moved.
The cache config, tempdb, dboption are all the same for
the database A and B. Is there any other possible reasons
for the performance degradation? I am out of mind. The
query plans are different for the two sps in two
databases. What will affect the query plan?

Taylor

2010-01-20 07:33:27 UTC

Permalink

Post by unknown
Hello ,
How about the tables that the sp is accessing.
Do they have "same" schema /indexes and "rows" in both the
databases?
HTH,
Vivek

- 显示引用的文字 -

The stored procedures are identical. They access the same tables.

Carl Kayser

2010-01-20 12:02:00 UTC

Permalink

Post by unknown
Hello ,
How about the tables that the sp is accessing.
Do they have "same" schema /indexes and "rows" in both the
databases?
HTH,
Vivek

- ??????? -

The stored procedures are identical. They access the same tables.

Vivek didn't ask about the stored procedures. He asked whether the tables
had the same lock schema (APL, DPL or DRL), indexes, and number of rows.

Mark A. Parsons

2010-01-20 18:41:07 UTC

Permalink

Not enough details to understand what your issue may be ...

------------------

What do you mean by 'same tables'?

1 - tables of the same name but in different databases, eg, db_A..table1 and db_B..table1

or

2 - one set of tables in one database, eg, regardless of where the stored proc resides (db_A or db_B), the stored proc
always references db_A..table1

------------------

Do the 2 databases (db_A, db_B) reside in the same dataserver?

------------------

What happens if you execute the proc 'with recompile' from both databases, eg:

exec db_A..proc_1 with recompile

and

exec db_B..proc_1 with recompile

------------------

Have you verified that both copies of the stored proc are in fact *exactly* the same?

You can use sp_helptext, defncopy or ddlgen to view the source code for the stored procs.

Post by Taylor

Post by unknown
Hello ,
How about the tables that the sp is accessing.
Do they have "same" schema /indexes and "rows" in both the
databases?
HTH,
Vivek

- 显示引用的文字 -

The stored procedures are identical. They access the same tables.

Alexey Kolosov

2010-01-20 16:49:56 UTC

Permalink

Post by Taylor
Hi,
I have a sp in database A. And the sp will access tables in database
A. Now somehow the sp will be moved to database B and the performance
goes worse since it moved. The cache config, tempdb, dboption are all
the same for the database A and B. Is there any other possible reasons
for the performance degradation?
I am out of mind. The query plans are different for the two sps in two
databases. What will affect the query plan?

maybe a statistic is different. try to do "update statistic" etc.