Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006
    Posts
    2

    Unanswered: SQL 2005 Query Performance

    Hi, I want to know if anyone have any clue about the reason why this happens.
    I have a table on SQL Server 7 with 320 thousand registers and when I execute a SELECT * on it, it takes about 6 seconds to give an answer. But the same table on SQL Server 2005 Ent takes about 16 seconds, Is it normal?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    So many variables, so little time.

    How much memory on the two servers? What's the CPU behind each? Disk configuration? Is the query plan cached? Are the indexes on the two tables identical? Have stats been updated (oops, sorry, that's more an Oracle thing).

    There are a LOT of variables that go into determining how much time a query takes.

    I will say that in general, the client-side tools for SQL 2005 seem quite a bit "porkier" than their 2000 counterparts. Hopefully my request to upgrade to 1 GB RAM on my laptop will get approved soon!

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Sep 2006
    Posts
    2
    I will describe the two servers

    Server 1 (The fast one, takes 6 seconds from Query Analizer):
    SQL Server 7
    768 MB RAM DDR
    1 HDD 70 GB RAID 0
    1 Pentium 4 HT 2.0 GHz

    Server 2 ( takes 16 seconds from Management Studio):
    SQL server 2005
    3 GB RAM DDR2
    4 HDD 146 GB RAID 0+1
    2 Xeon 3.0 GHz

    The indexes are identical on both tables
    The query is a SELECT * on that table
    the table has 320,000 records

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    ??

    Hmm, well on the surface, you do have a very valid question.

    1. Check the query execution plans. Make sure they are the same.

    2. What's your network connection to the server (or are you running the query local on the box)?

    3. Has the SQL 2005 box had a chance to burn-in to its normal level of activity?

    4. What's the spindle speed of the HDs?

    5. You might also check the scan density on the two tables (DBCC SHOWCONTIG). It might be a fragmentation issue.

    6. Are there other processes/services running on the SQL 2005 box that are not running on SQL 7.0 (such as reporting services, notification services, analysis services or non-SQL services).

    I dunno, it could be a LOT of different things.

    Sometimes you gotta just start tearing into it one bit at a time and start eliminating some of the variables.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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