We are having an strange problem on SQL Server 2008:
We have two servers and two databases (Nearly the same data)
DB1 is running on server 1
DB2 is running on server 2
DB1 executes an sql script in 7 seconds
DB2 executes the same sql script in 600 seconds.
DB2 is copied to Server 1 in order to discard server performance issues, but the sql script run slowly too on this server.
Once is clear that server performance is not the problem, we think that is caused by small data variations:
The Scripts call to a view where it seems to be the problem:
Case 1: We replaced the view invocation for a replica table (same view data) and it works very fast.
Script calls table (equivalent to the view)
Case 2: As a result of this we think that the problem could be inside the view (it calls to smaller views), so we replace the smaller views call for the equivalent tables, but it works slowly.
Script calls View -> View calls tables (equivalent tables to the subviews)
Resume: The problem does not seems to be inside the view, because the Case 2 works slow. It seems that the problem is because we call the view, does it make sense? remember that this view is running fast on the DB1
Transaction logs has been trunked, Index has been rebuild, a DB shrink has been executed.