Post reply

New faster server much slower than old slow server?

  • November 26, 2011 at 3:45 am

    #252559

    We are migrating from a SQL2005 32-bit 3GB ram server to a 2008r2 64-bit 16GB ram server but have found running a select * from table query takes a lot longer on the new server than the old one.

    The plans are the same, the reads are the same but the CPU is higher.

    This is re-runnable so we know the new server with 13GB data cache can answer the query from RAM

    On the new server the disk read queue length stays at 0 and page life expectancy is > 1000

    On the old (currently live) server the disk read queue length is constantly higher than 2, often in the teens or higher.

    The page life expectanct is at 1

    So to me this says that the old server is stressed and has to go to disk to get the data where the new server has all the data in RAM.

    So why would the new server be so much slower? The table is > 1GB to make sure that the old server cannot have the entire table in RAM as other processes exist and that the new server has the entire table in RAM, so the new server should be much faster, no need for disk access at all.

    I am creating test DB's with sample data in different modes and will update statistics for the table to test that, but as this is a simple clustered index query for all fields for the entire table, I'm not sure why statistics are that involved as the plans on both servers are indentical, 1 operator saying clustered index scan with same values.

    Any ideas?

  • November 26, 2011 at 6:09 am

    #1413104

    when upgrading to a higher version, you'll want to rebuild the indexes and update statistics.

    There are a lot of similar posts, where the upgraded server performs poorly until those two steps are performed;

    I don't have the exact reason, I think it's due to the newer sql engine calculates statistics differently, so "old style" statistics get used to build sub-optimal plans.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 26, 2011 at 9:30 am

    #1413117

    Could you check and compare the speed of old server core processor and the new one?

    I had the same problem in the past and the root cause was the old 2 core CPU core by core was speerder that new CPU with six cores

    Francisco Racionero
    twitter: @fracionero

  • November 26, 2011 at 12:23 pm

    #1413130

    Any chance your new server has power saving CPUs installed?

    http://www.brentozar.com/archive/2010/10/sql-server-on-powersaving-cpus-not-so-fast/

  • November 27, 2011 at 9:14 am

    #1413185

    Gosh, there are a thousand reasons why this might be so... anywhere from someone changing the FILLFACTOR on the Clustered Index to having an improperly configured or less capable disk drive system including the IO controller to having a bit or two of bad memory somewhere in the system. The latter of those was recently a problem where I worked. Once we found and replaced the bad memory, the system absolutely flew!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 28, 2011 at 2:29 am

    #1413285

    I've got the local guy looking at it now.

    running the cpu-z tool i can see that the server is as AMD Opteron 6172 and the core speed is 800 Mhz

    The DB is a straight backup/restore and the options such as fillfactor are the same.

    I'll also run this CPU-Z tool on the old 'slower' but faster server.

  • November 28, 2011 at 4:53 am

    #1413350

    Have you looked to see what that (and other) query(s) is/are waiting on? Check the WAIT type or most common wait types, may give you an idea where the issue is?

    Does your new server have more processors / cores? Wondering if it could be suffering from parallelism (you'll see some CXPACKET wait types holding things up).

    When you restored the databases from the old server, did you set up your usual ReBuild/Organise Index "type" maintenance jobs?

  • November 28, 2011 at 4:54 am

    #1413351

    I think your processor has other speed

    http://products.amd.com/pages/opteroncpudetail.aspx?id=644&AspxAutoDetectCookieSupport=1

    Francisco Racionero
    twitter: @fracionero

  • November 28, 2011 at 5:01 am

    #1413357

    sotn (11/28/2011)

    running the cpu-z tool i can see that the server is as AMD Opteron 6172 and the core speed is 800 Mhz

    What does CPU-Z show the speed should be, on the "Specification" line? Is the Core speed that it's showing significantly different from the Specification speed?

  • November 28, 2011 at 5:14 am

    #1413360

    The specification line shows AMD Opteron(tm) Processor 6172

    The Core Speed line shows 800.0MHz

    Looking at the page

    http://products.amd.com/(X(1)S(s4bgc3qbiulmcg45dgtdbjyu))/pages/opteroncpudetail.aspx?id=644&AspxAutoDetectCookieSupport=1

    If I understand it correctly, we should get 2,100MHz

  • November 28, 2011 at 5:29 am

    #1413367

    sotn (11/28/2011)

    The specification line shows AMD Opteron(tm) Processor 6172

    The Core Speed line shows 800.0MHz

    There should be text at the end of the Specification line that shows the spec speed; something like "AMD Opteron(tm) Processor 6172 @ 2100MHz"

  • November 28, 2011 at 5:33 am

    #1413369

    I agree there should be, but there isn't 🙁

    I have attached the screen shot of it, in case that helps.

  • November 28, 2011 at 5:58 am

    #1413379

    sotn (11/28/2011)

    I agree there should be, but there isn't 🙁

    I have attached the screen shot of it, in case that helps.

    That's odd. 800MHz sounds awfully fishy though, you probably want to go ahead and check your power management options. Go to Control Panel->Hardware->Power Options, or type powercfg.cpl at a run prompt, and make sure the "High Performance" option is selected. You also might need to change a setting in the BIOS to allow the OS to manage the chip's power settings...I think that depends on the chip, don't know if it applies to this hardware configuration or not. Most likely your power plan is set to "Balanced", which will allow the OS to slow the CPU way down when it's not under extended periods of high load (and depending on the CPU, it may never get up to full speed, even under load, with anything other than the High Performance setting).

    Also, out of curiosity...is this server physical, or virtual?

  • November 28, 2011 at 10:32 am

    #1413551

    Thanks for all the pointers.

    We found in the BIOS the power saving setting, so now CPU-Z is set to 2100 Mhz instead of 800

    However the query was still marginally faster than the older server, then I remembered another point.

    A SQL server with just 1 query may not get the CPU to run flat out.

    So we ran 10 large select * queries at the same time, and this performed 10x faster than the old server.

    Thanks again.

  • November 28, 2011 at 10:41 am

    #1413552

    Lowell (11/26/2011)

    when upgrading to a higher version, you'll want to rebuild the indexes and update statistics.

    There are a lot of similar posts, where the upgraded server performs poorly until those two steps are performed;

    I don't have the exact reason, I think it's due to the newer sql engine calculates statistics differently, so "old style" statistics get used to build sub-optimal plans.

    SOTN, you never answered this question. 😀

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply