Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Exclamation Unanswered: SQL Server running Slow on a high end system

    I have about a 447 MB SQL server 2000 database on a desktop PC acting as a QA server. The hardware specs of the QA box are as follows:

    CPU: P4 2.4 GHz
    Memory: 1GB
    Drives: 80 GB IDE

    I recently purchased a Dell PowerEdge 2650 server to act as the staging box. The staging box has

    CPU: P4 2.4 GHz
    Memory: 2GB
    Drives: 40GB SCSI, mirrored

    I made a backup of the database on the QA box, and restored it on the staging box. Yet when I run something as simple as a select query (select * from <table>), the less powerful QA box is faster.

    I figured maybe the statistics are different on the staging box. I ran dbcc showcontig to make sure the statistics were identical. Also ran RedGate's SQL compare and data compare to make sure everything was identical.

    I figured maybe the query optimizer needs to be tweaked. I recreated the indexes and updated statistics on the staging box. The queries actually got slower as a result.

    I thought maybe SCSI drives are slower. Tried breaking the mirror on the staging box. No luck. Put the mirror back in place, ran a test where I copied a large folder from one directory to another on the staging box. Repeated the same test with the same data on the QA box. The staging box was more than twice as fast than the QA box.

    It doesnt appear to be a problem with the query, adjusting memory in SQL server has not effect, both boxes are using SQL server 2000 SP3, why is the bigger machine running queries hundreds of milliseconds slower than the smaller machine? Any help will be appreciated!

  2. #2
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Showcontig does not show stats but does show fragmentation. I recommend you start by updating the statistics first and foremost, you say you backed up and restored right? So all your indexes should be there. Are you sure there are no additional config differences between the boxes?
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  3. #3
    Join Date
    Mar 2004
    Posts
    10
    Thanks for your response! When I updated statistics, it seems to have worsened the problem on the staging box. Here is what I did. I created a new maintenance plan that ONLY updated statistics. I ran the job it created which lasted roughly a minute. I ran the same select statements on both QA and Staging boxes. The Staging box averaged about 10 millisecond faster after about 20 repeated trials.

    Encouraged by the results, I tried a much more complex query. Again, same database, same query, different boxes. QA box runs the query in 3 seconds. Staging box takes 41 seconds!!

    I then tried the same simple select statement.....Staging box had slowed by about 30 ms, and QA box is now beating staging box in almost every trial.

    You asked if there is anything configured differently on the two boxes. Except for the hardware, i cant think of anything else. Both boxes are dedictaed SQL boxes. I ran perfmon on all counters. The ones that spike when I run the complex query include:

    Scan Point Revalidations per sec
    Range Scan / sec
    Index Searches / sec
    Page lookups / sec

    The very same counters spike on the QA box as well. I am quite baffled at this point.

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    From your one line "QA box is now beating staging box in almost every trial. "

    Does that mean all is well now? If not, how much memory is SQL USING on both boxes? Sometimes a new box takes a bit to grow it's memory use.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Mar 2004
    Posts
    10
    Nope all is not well. I am sorry I wasnt clear. The low end QA box, a PC, is still faster than the high end staging box (the Dell server). As far as memory usage goes, the high end box is using 347,000K of memory. The low end box is using 54,000K of memory. I have both configured to dynamically assign memory. Hope this helps.

    One more difference I came across is, the high end box has something called hyperthreading enabled which is supposed to make it faster as well. Disabling it has made no difference.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you capture PERFMON counters on both the boxes to compare as nothing additional is crunching the resources on main server.

    http://support.microsoft.com/default...b;EN-US;243589 to troubleshoot slow running queries.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Mar 2004
    Posts
    10
    I ran perfmon on all SQL counters. The ones that spike when I run the complex query include:

    Scan Point Revalidations per sec
    Range Scan / sec
    Index Searches / sec
    Page lookups / sec

    The very same counters spike on the QA box as well. I have looked at the other counters. Nothing spikes on the high end box that I cant see spiking on the low end box as well.

    I am also having trouble with a simple select query (not even using a 'WHERE' clause).

  8. #8
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I feel the physical disk is getting stressed due to index and table scan lookups, thereby indicating data volume and fragmentation (excessive fragmentation can impair performance).

    Monitoring the methods used to access database pages can help you to determine whether query performance can be improved by adding or modifying indexes or by rewriting queries.

    Review the above referred KBA and take help of PROFILER while running the query and submit to index tuning wizard for any recommendation.

    Also make sure to DBREINDEX on periodical basis to reduce the fragmentation.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  9. #9
    Join Date
    Mar 2004
    Posts
    10
    I ran DBCC showcontig. On the low end box, here is what they look like for the table I am running a simple select statement against:

    DBCC SHOWCONTIG scanning 'Unit' table...
    Table: 'Unit' (2050106344); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 40
    - Extents Scanned..............................: 6
    - Extent Switches..............................: 5
    - Avg. Pages per Extent........................: 6.7
    - Scan Density [Best Count:Actual Count].......: 83.33% [5:6]
    - Logical Scan Fragmentation ..................: 2.50%
    - Extent Scan Fragmentation ...................: 33.33%
    - Avg. Bytes Free per Page.....................: 727.8
    - Avg. Page Density (full).....................: 91.01%




    On the high end box (which is running slower than the low end box), here is the DBCC Showcontig:

    DBCC SHOWCONTIG scanning 'Unit' table...
    Table: 'Unit' (699253646); index ID: 1, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 44
    - Extents Scanned..............................: 6
    - Extent Switches..............................: 5
    - Avg. Pages per Extent........................: 7.3
    - Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 674.3
    - Avg. Page Density (full).....................: 91.67%

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Try capturing after a DBCC DBREINDEX on the involved tables.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  11. #11
    Join Date
    Mar 2004
    Posts
    10
    On the low end box, Here are the results of

    dbcc dbreindex ('Unit')
    go
    Update Statistics Unit
    go
    dbcc Showcontig


    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC SHOWCONTIG scanning 'Unit' table...
    Table: 'Unit' (2050106344); index ID: 1, database ID: 5
    TABLE level scan performed.
    - Pages Scanned................................: 40
    - Extents Scanned..............................: 6
    - Extent Switches..............................: 5
    - Avg. Pages per Extent........................: 6.7
    - Scan Density [Best Count:Actual Count].......: 83.33% [5:6]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 0.00%
    - Avg. Bytes Free per Page.....................: 727.8
    - Avg. Page Density (full).....................: 91.01%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    On the high end box, the same query gives the following result:

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    DBCC SHOWCONTIG scanning 'Unit' table...
    Table: 'Unit' (699253646); index ID: 1, database ID: 7
    TABLE level scan performed.
    - Pages Scanned................................: 44
    - Extents Scanned..............................: 6
    - Extent Switches..............................: 5
    - Avg. Pages per Extent........................: 7.3
    - Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
    - Logical Scan Fragmentation ..................: 0.00%
    - Extent Scan Fragmentation ...................: 33.33%
    - Avg. Bytes Free per Page.....................: 674.3
    - Avg. Page Density (full).....................: 91.67%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.


    The low end box runs the query "select * from Unit" in 63 ms (10 trial avg.). The high end box runs the same query in 93 ms (10 trial avg. ), so the problem still exists.
    Last edited by jkhan; 03-08-04 at 13:58.

  12. #12
    Join Date
    Mar 2004
    Posts
    7
    Have you verified the versions of both servers (SELECT @@VERSION)? I had a similar problem and the cause was that the old server was between service pack 3 and 4 and the new box had not been sp'd yet.

  13. #13
    Join Date
    Mar 2004
    Posts
    10
    Both boxes have SQL server MSDE Service pack 3. I used Select @@version to collect the information.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Can you check the query plans that are getting generated by both machines? In Query Analyzer go to the query menu, then show execution plan. Are the plans for this query the same on both QA and Staging?

    Also, what editions of SQL server are you running on both? This is also available from select @@version..

  15. #15
    Join Date
    Mar 2004
    Posts
    10
    Here are the results from the Select @@Version query on the two boxes:

    Staging (High end box):
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    QA (Low end box):
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    The query execution plan is identical on both boxes. Since its a simple select statement, there are only two steps in the plan. I looked at the details for each step. Every single line in the details for both step is identical on both boxes. Query still runs faster on QA (low end) box.

Posting Permissions

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