Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    26

    Unanswered: New "bigger, better" SQL Server...slow

    Hi,

    We just put on our main accounting (50 GB total, 8 GB largest table - GLTRAN) database on a new Windows Advanced 2003 server with 8 GBs of memory. Everything is essentially the same as the old box, aside from the fact that it's on Windows Advanced 2003 Server and it's using LUNS as the E: drive where the SQL database is kept. It runs fine for the most part, excpet this one report takes literally 20 times longer to run than on the pld box.

    It's SQL Enterprise 2000 SP4 (also the same). Are there new config options for SQL when running on a 2003 server? Or is it how the OS is handling the SQL service? I'm perplexed. It's not indexes. I still have the old box and load the current dbase to it for testing purposes and the report runs like lightning on it.

    Thanks,
    Dave

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How did you load the database? Maybe running update statistics would help. Also, compare the execution plan on your new server to the execution plan used by the old server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd bet that you've been bitten by KB#899761. My guess is that you've applied the fix to the "old" machine, but forgotten about it on the "new" machine. That tends to cripple queries that benefit from hash joins (and anything else that make a server work very hard).

    -PatP

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Pat Phelan
    I'd bet that you've been bitten by KB#899761. My guess is that you've applied the fix to the "old" machine, but forgotten about it on the "new" machine. That tends to cripple queries that benefit from hash joins (and anything else that make a server work very hard).

    -PatP
    Nice bet; I was also about to ask if AWE was configured properly. For an 8 GB machine, I believe you use the /PAE /3GB parameters in boot.ini and then configure the AWE options in SQL.

    Also, I was not clear on the disk storage solution. You mention an E:\ drive for data; what about logs? A separate LUN for Temp? And what about network IO? What does sp_who2 say? What about sp_lock? What is the wait resource?

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is very rare to use /pae and /3gb at the same time... They tend to fight much more often than they cooperate. /3Gb is a good choice for a 4 Gb server, because it gives you a 3Gb bpool for your application. For more than 4 Gb, you probably want /pae so that the box can swap more memory into a 32 bit address space. There are a few occaisions when you want both, but those are very unusual.

    -PatP

  6. #6
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    26
    Guys...awesome. One thing that I over-looked was the /PAE (that is enabled on the old one).
    I'll definitely change that at noon today when the bean-counters are at lunch, and reboot the server after.

    I only have SQL SP3 running on both of these servers. So that patch won't work until I install the SP4 (which I didn't even know existed!) and then load that patch. I will keep you all posted and many thanks to all.

    Dave

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by djay

    I only have SQL SP3 running on both of these servers. So that patch won't work until I install the SP4 (which I didn't even know existed!) and then load that patch. I will keep you all posted and many thanks to all.

    Dave
    I remember Paul Randal was investigating the sp4 problem with queries running longer. Does anyone know if a resolution or M$oft acknowledgement was offically posted?

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by Pat Phelan
    It is very rare to use /pae and /3gb at the same time... They tend to fight much more often than they cooperate. /3Gb is a good choice for a 4 Gb server, because it gives you a 3Gb bpool for your application. For more than 4 Gb, you probably want /pae so that the box can swap more memory into a 32 bit address space. There are a few occaisions when you want both, but those are very unusual.

    -PatP
    Pat,

    I have seen several articles saying that /3GB and /PAE are okay together. Chris Kempster is one; I'm looking for the MS reference (but as usual my google-shui is weakest when I need it most.

    Chris Kempster summarizes /3GB and /PAE like this:
    4GB RAM => /3GB (AWE support is not used)
    8GB RAM => /3GB /PAE
    16GB RAM => /3GB /PAE
    16GB + RAM => /PAE

    There's also a brief discussion about it here: link.

    I regularly use /3GB and /PAE on my 8GB RAM servers (on IBMs and Dells with Windows 2000 Advanced and Windows 2003 Enterprise. I have never noticed any adverse behavior.

    I am always prepared to be further enlightened about best practices though. It would not be the first time I was wrong.

    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    26
    No go. Still a dog. What about the Windows 2003 performance options in the Advanced tab of system props?

    Processor Scheduling: Programs or Background? I have Background.
    Memory Usage: System Cache or Programs? I have Programs.

    I have the data on E, the logs on F and the tempdb on a RAID 1 partitioned G drive with 100's of GB free, maybe thousands!

    Thanks as always...

    Dave

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you enabled AWE in SQL Server 2000?

    -PatP

  11. #11
    Join Date
    Dec 2002
    Posts
    1,245
    Can you post the results of sp_configure? Also, can you tell us what the wait resource is (from the EM / Current Activity / Process Info screen)?

    Regards,

    hmscott
    Have you hugged your backup today?

  12. #12
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    26
    AWE is enables, and always was

    SP_CONFIG RESULTS:

    name minimum maximum config_value run_value
    ----------------------------------- ----------- ----------- ------------ -----------
    affinity mask -2147483648 2147483647 0 0
    allow updates 0 1 0 0
    awe enabled 0 1 1 1
    c2 audit mode 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    Cross DB Ownership Chaining 0 1 0 0
    cursor threshold -1 2147483647 -1 -1
    default full-text language 0 2147483647 1033 1033
    default language 0 9999 0 0
    fill factor (%) 0 100 0 0
    index create memory (KB) 704 2147483647 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2147483647 0 0
    max degree of parallelism 0 32 0 0
    max server memory (MB) 4 2147483647 6144 6144
    max text repl size (B) 0 2147483647 65536 65536
    max worker threads 32 32767 255 255
    media retention 0 365 0 0
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 0 0
    nested triggers 0 1 0 0
    network packet size (B) 512 65536 4096 4096
    open objects 0 2147483647 0 0
    priority boost 0 1 0 0
    query governor cost limit 0 2147483647 0 0
    query wait (s) -1 2147483647 -1 -1
    recovery interval (min) 0 32767 0 0
    remote access 0 1 0 0
    remote login timeout (s) 0 2147483647 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2147483647 0 0
    scan for startup procs 0 1 0 0
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    two digit year cutoff 1753 9999 2049 2049
    user connections 0 32767 0 0
    user options 0 32767 0 0

    hmscott, how do i do that?

  13. #13
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    26
    There's actually nothing under the wait resource column...?

  14. #14
    Join Date
    Dec 2002
    Posts
    1,245
    Djay,

    Sorry to run out on you on Friday; sometimes that happens on the forums -- you don't always get an immediate response. I have been very lucky to date and have gotten really good results and excellent timeliness here (well, at least from the SQL guys -- Oracle's been a different story ).

    What's the status now?

    I see a couple of things in your sp_configure:

    1. Max server memory (6144). This looks okay, but you may want to check your alert log for a message indicating the SQL Server was unable to allocate that amount of memory. things like RAID cards can soak up just enough off the top of your memory to keep you from getting all 8 GB (less the 1 GB allocated to the OS). The message (if it exists) will appear in the first 50 lines or so after starting the server.

    2. The min server memory (0) indicates to me that you are allowing SQL to dynmically manage memory. In all the servers that I have managed with > 4 GB memory (ie, AWE enabled), I have always used a fixed amount of memory (not dynamic). From the server property page, click the Memory tab and then select the "Used a fixed memory size" radio button. Dial in the amount of memory you wish to allocate to SQL and click okay. You will have to restart the server.

    As for the "wait resource", it may not apply in this case. But try to catch the server when it is running this long report and update (F5 to refresh) the Current Activity node and then look at the Process Info tab. You may have to cycle through these steps a couple of times to get anything (your focus has to be on the "Current Activity" node when you press the F5 key or nothing will happen).

    If nothing ever appears under the wait resource, then it's just being a pig and taking its sweet time.

    Finally, are you sure that all of your indexes got copied over from the old server to the new one?

    Regards,

    hmscott
    Have you hugged your backup today?

  15. #15
    Join Date
    Oct 2003
    Location
    Toronto
    Posts
    26
    I'm thinking about using that /3GB switch in boot.ini even though the old box doesn't have one and maybe setting the min mem as well. Can 2003 Server be that different from 2000 Server? All signs point to YES right now...

Posting Permissions

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