SQL Server Performance

server 1 (old) beat server 2 (new) in test sql

Discussion in 'Performance Tuning for DBAs' started by Brent, Aug 24, 2004.

  1. Brent New Member

    hi all, <br />i'm in the process of changeing sql servers from one box to a new box. server A (sql 7 dual 870Mhz with 2 gb ram and running with a active large db on it.) server B (sql 2000 Quad 2 GHz xeons 2 gb ram and idle). <br /><br />i ran <br /><br />declare @start datetime<br />declare @i int<br />declare @x float<br /><br />set @i = 0<br />set @start = getdate()<br /><br />while (@i &lt; 1000000)<br /> begin<br /> set @x = sin(cos(sin(cos(sin(cos(sin(cos(sin(cos(@i))))))))))<br /> set @x = sin(cos(sin(cos(sin(cos(sin(cos(sin(cos(@i))))))))))<br /> set @x = sin(cos(sin(cos(sin(cos(sin(cos(sin(cos(@i))))))))))<br /><br /> set @i = @i + 1<br /> end<br /><br />print datediff(ms, @start, getdate())<br /><br />server A returened 21360ms 21 seconds (both processors showed activity)<br />server B returened 31530ms 31 seconds (only 2 of the processors showed much activity the server stayed a 23% on average I liked this.)<br />Now i'm a little bit wared about moving the db to the new box <br />Does Any body have any idea[?][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><i><b>KENNY</b></i>
  2. satya Moderator

    Have you checked execution plans on both the servers?
    Collect PERFMON counters stats for more assessment on these 2 machines.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. derrickleggett New Member

    Also, you really should up the RAM to 4 or 5gb. What version of windows/SQL do you have on this box? I recommend always having at least 1gb of RAM per processor.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  4. Brent New Member

    i would like to up the ram in the box, i'm running win 2000 server and sql enterprise can i up the ram??

    The exection plans on both servers are the same.

    thanks

    KENNY
  5. derrickleggett New Member

    Brent, I'm just amazed they'll spend the money for Enterprise Edition and not buy anything better than Win 2000 Standard. That's loco. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> Hopefully, you can talk them into it. It's a total waste of money to not do so.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  6. Brent New Member

    I was just hired because of discussion like this. With the money they all ready have put in they should have the fasted system out there.
    Thank for the help.

    KENNY
  7. Twan New Member

    Having said that though, the test that you are running is not really a good test of SQL performance... it is single threaded so won't ever use more that 1 CPU (although it may switch between CPUs) it is also not using any real memory, caching, disk or anything really... The problem you are running into is likely to be excessive context switching because of hyperthreading...<br /><br />A better comparison would be to put some load through both machines froma clean start. Are you able to restore a copy of the database on machine 2, and then set up a trace on machine 1 for replaying. Replay it on machine 2 whilst running another trace and compare the results...?<br /><br />buying Enterprise edition is unlikely to help with this, and as Derrick mentions given the cost it is better spent on RAM <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  8. Luis Martin Moderator

    I agree with Twan about test. If you do that, don't forget to Update Statistics before restore on second box.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  9. derrickleggett New Member

    buying Enterprise edition is unlikely to help with this, and as Derrick mentions given the cost it is better spent on RAM <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />--He already has enterprise edition. RAM, RAM, RAM. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  10. brimba New Member

    Stop focus on your test is my suggestion. You have faster and more processors in your new server and when you start to give them something to do they will do more work than the other server.

    Also you told us that you are running enterprise version of sql-server. Then I guess that your database is bigger than 2GB? If its possible I recommend you to purchase RAM of the same size as the database plus one more gig for the system.
    What version of Windows 2000 are you running? Have you configured windows and sql-server for all four processors?

    If you dont afford to buy enough of ram to fit the database in there then I suggest that you check that you have fast harddrives and such since it probably will be swapping alot on the disk.
  11. satya Moderator

    With AWE, SQL Server can reserve memory that the OS and other applications aren't using. Each instance that uses this reserved memory must statically allocate the memory it needs.

    To learn about adding memory to SQL Server and the limits of AWE memory, read the Microsoft article "HOW TO: Configure Memory for More Than 2 GB in SQL Server" athttp://support.microsoft.com/default.aspx?scid=kb;en-us;274750 and few articles from this website
    http://www.sql-server-performance.com/awe_memory.asp
    http://www.sql-server-performance.com/sql_2000.asp

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. Twan New Member

    no offense to anyone but don't put too much emphasis on bulk RAM. Lots of RAM is good though I'd say that going over 4GB should be preceeded by working out if you actually need it.<br /><br />A 500GB database should run quite happily on a server with 3GB RAM, as long as the database is appropriately indexed and the application is tuned. Fitting the entire database into RAM is not only near impossible it is also totally unnecessary.<br /><br />I would never commit to turning on AWE without tuning the app and testing that you need &gt; 3GB for SQL to use. AWE is good for throughput but it is not good for straight out performance. Only use AWE if you actually need it, not because you have the spare few $K to buy the RAM...<br /><br />ok that's my spiel for the week <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  13. satya Moderator

    Did you get a chance to capture the PERFMON counters and PROFILER to investigate further for the slow running queries.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. derrickleggett New Member

    Twan, do you have any databases that are 500gb and run with only 3gb of RAM? If they have any transactions at all, they would need more than that. The increases we got from buying more RAM were incredible. Our main database environment is only 250gb. We run 300-400 t/sec with peaks over 1400 though. More RAM allows you to maximize the usage of the cache, store more data in memory, and minimize the swaps needed. It's easily the best bang for the buck on SQL Server.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  15. Twan New Member

    Hi Derrick,

    I don't in my current contract, but have in a previous contract had a .5 TB database with 4GB in the box, 3 for SQL 1 for OS. probably only a couple of 100 tps, but a well tuned app, at least when we were done with it... I got called in because of performance problems, and got some huge wins by just indexing the data properly and optimising the SQL. I agree that more RAM can give you a boost, especially if there is any paging going on, but I would still say that adding more RAM without actually seeing if you need can mean that app tuning is overlooked...

    When talking about large databases it depends a lot on how much of the data is actually active... in our case a transactional system a lot of the data was kept for reporting, but most of the work was done in adding new and amending recent data

    Cheers
    Twan
  16. Luis Martin Moderator

    I have one customer with 60Gby database, 1GBy RAM (2 Processor, RAID 10), 1500 t/s and I wrote a lot of reports recommendating to upgrade RAM, but there is no way. Of course same customer 1 week ago bought 1 printer for marketing (u$s 3000).



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  17. derrickleggett New Member

    lol Isn't that just like management.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  18. chopeen Member

    quote:Originally posted by Brent

    server A returened 21360ms 21 seconds (both processors showed activity)
    server B returened 31530ms 31 seconds (only 2 of the processors showed much activity the server stayed a 23% on average I liked this.)

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5262

    I still have no idea why this happens. As far as I know, the problem with this query is caused by HTT.

    Did you try to run the same query on a new machine with HTT disabled?

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland

Share This Page