locked
Different results between SQL2005 and SQL2008 RRS feed

  • Question

  • Hello

    I have a query in a database in an SQL 2005 server database. This query extracts data from 2 tables. Each table has around 350.000 entries. When I run this query on this SQL2005 Server I have to wait around 4-5 second to view results.

    Now in the company where I am working we have migrated to SQL2008. I have made backup to this database and I have restored to the nes server where is installed SQL2008.

    When I run the same query I have to wait 20-30 minutes to see any result.

    Can somebody help me with this?

    I mention that the old server has only 1GB RAM memory and the new one has 18GB of memory. The new serever is on the top in this moment. So the problems remain with something else. Also in these two table from my database I do not have any index. I have indexed only the ID columns on each table.


    Thanks in advance, Ciprian LUPU
    Monday, January 24, 2011 12:20 PM

Answers

  • Hi Ciprian Lupu,

     

    Before further investigation, please provide us the following information:

    ·         The query script

    ·         The table structure
    please right-click on this table in SQL Server Management Studio (SSMS) and choose “Script Table as | CREATE To | New Query Editor Window”

    ·         The type and number of CPU in these two machines

    ·         The memory allocated to SQL Server in these two machines
    right-click on instance in Object Explorer pane in SSMS and choose Properties. In popup window, please switch to Memory tab and get the value from the textbox under “Maximum server memory (in MB)”

     

    Meanwhile I would like to recommend that you update the statistics after you restore it to SQL Server 2008.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    • Marked as answer by WeiLin Qiao Wednesday, February 9, 2011 5:00 AM
    Wednesday, January 26, 2011 7:11 AM