thanks for the reply friends..there is an another view which i got is 'virtual memory ballooning ' an option in VMware..

Hi Koen Verbeeck,

both the execution plans are not the same..

one of the clustered index scan having cost as 56% in PROD but in DEV its 14%

clustered index key...

GilaMonster,

the restoration happen one month back.

this both the server are new and the DB is related to People soft.

Recently we upgraded the DB from 2005 to 2008R2(in new server).

after the...

Thanks Koen Verbeeck for ur reply.

I will check the execution plan.

Thanks GilaMonster for ur reply:

the data volume is same also the index(recently we restored the PROD DB into...

Perry Whittle,

currently I am restoring the DB from the backup(backup of step 3).once the restore is done should I take a log-backup backup(DB is in full recovery) to fix this...

Ya,the DB was working fine..currently I am restoring the DB..

thanks for the reply..

1)added secondary log file to the DB.

2)deleted the secondary log file via DB properties.

3)took a full backup.

4)restored the backup(from point 3 backup)

5)server gets restarted.

6)the DB doesn’t come...

check with your SMTP server name and port number..had the same problem last week and found out SMTP server name was wrong..

Ya we rebuild all the indexes, updated the statistics. still we are getting performance issue in this select query..

Sure..but dont know when this query will finish..

FYI this is a upgraded new DEV server from 2005 to 2008R2.

few more details:

Server properties:

Processor:2.67 Ghz( X two)..

ram 36 (usable : 32)

64 bit...

Its estimated execution plan.

created temp table for both tables (row count:453637 for process_instance=339706) and all the indexes as per main table.still the query is keep on running 🙁

total count in...

HI,

The nolock we are not using..It was used for testing..I added the missing index in the table and I ran the query..also I have attached the updated Execution plan.

Thanks!

thank you very much for your reply!

Yes I tried to add the missing index and the performance is same..

I am attaching the sqlplan file for your references...

Thanks in advance!

Second table:

[PS_OPEN_ITEM_TAO](

[PROCESS_INSTANCE] [decimal](10, 0) NOT NULL,

[BUSINESS_UNIT] [varchar](5) NOT NULL,

[JOURNAL_ID] [varchar](10) NOT NULL,

...

First table:

[PS_OPEN_ITEM_GL](

[BUSINESS_UNIT] [varchar](5) NOT NULL,

[JOURNAL_ID] [varchar](10) NOT NULL,

[JOURNAL_DATE] [dbo].[PSDATE] NOT NULL,

...