some clue here , i found that xtype value for these tables is "IT" not sure what that is?

They are not systables and those table names are some like "queue_messages_2009057645"

I am sysadmin on that box and could not see those tables from designeer mode or when i do other quries like "SELECT * FROM sys.Tables" but i could...

ok let me expalin this way..

I used the above query to bring up the list of tables and columns in a database but it shows tables that do not exist...

bhuvanesh, thgough you give exec permission to the proc it will not allow to do security admin stuff unless you havethat previlage.

sorry about the duplicate post, admins please delete this post.

OK i have created the stored proc by using " Execute as owner' but getting the following error message

Msg 916, Level 14, State 1, Line 1

The server principal "sa" is...

I restore db to production every month so that data addedd in the stage area will be updated but during this process i rebuild indexes,backup db and restore on to...

can some one flush out any idea as to why 2 identical databases behaving different.

I have tried statistics update, drop/re create index, created new db and imported all the data,restored the same database again,wrapped the view in another view which the query is accessing,creates...

Gila, actually its the same text files which i copied in the post but could not attach them, can you give an idea with weired behaviour of my databases.

both db's...

if you need sqlplan file of the execution plan , please check the atatchements.

I think the problem in the last part of the query is that it is using nested loop at the inner join, how do i avoid that? where as the...

i did generate scripts for statistics from better performing database and copied to the database that performs very poor hoping it would choose the execution plan path but...

I have tried table valued function instead of view but still the same performance, i am not getting paralellism yet.