Results 1 to 9 of 9

Thread: Query in memory

  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: Query in memory

    Hi guys,
    I have a couple of databases in 2 different servers, Test and Production. They are very similar, probably a few rows more in production, but not that many.
    We I run an store procedure in test it takes less than when I run it on the Production server it seems slower. The thing is that the second time that I run this query in the test server it is quick like if it keeps the information in memory. But in the production server the second time takes exactly the same.
    I'm new with SQL server and I don't know if there is something about cache memory that I didn't take into account.
    Please, any help?

    Thanks a lot

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It would help to see the procedure...BUT, it is probably 1 of 2 things

    1. The indexes in dev were not created in prod, or
    2. The query optimizer is using the wrong plan

    Have the dba do sp_recompile on the procedure in production if the indexes are there, the test the procedure and look at the query Plan [CTRL]+K
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    More likely, the required data is cached on the Production server, and the first run on the test server has to go all the way to the disk to get all the data.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    More likely, the required data is cached on the Production server, and the first run on the test server has to go all the way to the disk to get all the data.

    Dude,

    You have it backwards....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2005
    Posts
    4
    Thanks for your help. I will have a look to the query plan, but I think that the problem could be that we thought that we had SP3 in production and SP4 in Test.
    Thanks again :-)

    Luis

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm. I might. I was focusing in on this line:
    Quote Originally Posted by kazan
    The thing is that the second time that I run this query in the test server it is quick like if it keeps the information in memory.
    Is the production version of the query slow?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to compare the execution plans and IO statistics between the environments. Instead of sp_recompile do "dbcc flushprocindb(@dbid)" which will force the creation of a new execution plan. If the structure is IDENTICAL (!!!) then you should see identical execution plans and IO stats. If the latter is not the same, then there is gotta be something different between the environments. Other then that, - there are no mysteries.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by rdjabarov
    Instead of sp_recompile do "dbcc flushprocindb(@dbid)" which will force the creation of a new execution plan.
    Both will do the same thing

    If the structure is IDENTICAL (!!!) then you should see identical execution plans and IO stats. If the latter is not the same, then there is gotta be something different between the environments.
    True enough

    Other then that, - there are no mysteries.
    [/quote]

    There are no miracles!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, good morning to you too, Brett! Miracles however do happen, while there are no mysteries
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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