SQL Server Performance

Query runs slower from another database

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by dave_organ, May 25, 2007.

  1. dave_organ New Member

    Here's a trivial query:

    Select ROWID From HomeDB.dbo.CatalogItems where rowid = 11111
    (RowID is the primary key)

    When I am in database HomeDB, the response is immediate as you would expect.
    When I execute from another database (but still same server), the response takes 2 secs.

    The issue seems to be due to some latency referencing the other database.

    We're using SQL Server Express SP2

    Comments anyone?
  2. Luis Martin Moderator

    Did you run Update Statistics?
    Did you see if the execution plan are different?

    BTW:Welcome!!

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. dave_organ New Member

    Thanks for the welcome, Luis!

    Execution plans are the same.

    Haven't run stats yet, will try.

    I noticed that if I batch several similar queries it takes about the same time, that is, the delay seems to be on the first query executed against the other database whereas the remaining ones in the same batch run almost instantaneously (as you would expect, given the nature of the query)


  4. satya Moderator

  5. dave_organ New Member

    I am now sure that the problem is not data- or table-oriented.

    My latest test is this:
    USE ProblemDatabase
    SELECT 'Hello'

    The above takes 2 seconds, whereas
    USE OtherDatabase
    SELECT 'Hello'
    is more or less immediate.

    Even in Query Analyzer I can see a delay of 1-2 seconds when using the database name dropdown to switch database context.

    My user and schema context is 'dbo'

    We are now going to create a new database, populate it with the same contents as the problem one, and see what effect that has.


  6. dave_organ New Member

    Problem now solved, but without knowing why:

    After recreating and repopulating the suspect database, the problem has gone away.
    Unfortunately, we'll never know what caused the problem!

    Thanks everyone for your comments.

  7. Luis Martin Moderator

    Good news to you, bad news to us I would like to know what fix the problem.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  8. satya Moderator

    You say suspect database, that means is that database not identical to the previous one?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. dave_organ New Member

    It's different only in the sense that it contains different tables.

    But it's supposedly the same otherwise: same server, same file location, same database options.

    Nevertheless there was something about it that we couldn't spot.
  10. satya Moderator

    If it has something to do on the difference between the tables then you wouldn't be able to find what went wrong, if not having a review of error logs including event viewer may give any clue outside of the SQL process.

    That is why to troubleshoot the performance problem PERFMON & SQL Trace are useful.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  11. Anonymous New Member

  12. FrankKalis Moderator

Share This Page