Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: SQL Server Strange behaviour

    Hello all,

    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.

    Thank you!!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are DB1 and DB2 of similar sizes?

    What does the execution plan for both queries look like? Does the slow query have more table scans?

    Are the index statistics on DB2 out of date?
    Code:
    select object_name(object_id), name, stats_date (object_id, index_id)
    from sys.indexes
    where index_id < 255
      and index_id > 0

  3. #3
    Join Date
    Aug 2012
    Posts
    2
    It was the execution plan, we hace cleaned the statistics and the procedure cache and now is working fine.

    Thank you very much!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •