SQL Server Performance

SQL Server 2000 vs 2005 load test

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by vanirame, Mar 19, 2007.

  1. vanirame New Member

    Friends,[?]
    We load tested one of our application on SQL server 2000 SP4 vs. SQL Server 2005 SP2.
    (both on same System with 2 CPU hyper threaded, 4 GB RAM PAE, /3GB, 32 bit, Win 2003 SP2, OLTP)

    CPU pegged at 100% on SQL server 2000 when 500 users logged in, but on
    2005 only at around 230 users.

    Same settings had been used for 2005 as 2000[?]. We did not parameterize SQL on both 2000 and 2005.
    We see major waits @ RESOURCE_SEMAPHORE_QUERY_COMPILE.

    Any suggestions really appreciated.
    Thank you,
    V

  2. MohammedU New Member

  3. vanirame New Member

    Hi MohammadU,
    Thanks for the information.
    We tested same application on both sql server 2000 and 2005 without making any changes but 100% CPU around 500 users on 2000 and 230 users on 2005.

    Does it mean there are higher compilations/re-compilations in 2005 than 2000 (Plans not shared very well as in 2000 ) OR
    compilation is more CPU expensive in 2005 than 2000 OR algoritham to flush old plans is not efficient ?


    As I mentioned earlier, we did NOT use paramtereized queries on both 2000 & 2005 (though planning in future release)
    We tested 2005 even setting option "parameterization = forced"
    Thank you,
    V
  4. joechang New Member

    see if your performance monitor counters for:<br /> SQL Server<img src='/community/emoticons/emotion-7.gif' alt=':S' />QL Statistics -&gt; Batch Requests/sec & Compilations/sec<br /> <br />are close or 1/2, or something<br />if every call is being compiled, then you have no plan reuse<br /><br />i would not think that the flush algorithm is less efficient<br />try <br />SQL Server:Cache Manager <br />-&gt; Cache Pages & Cache Object Counts<br />Instance: Adhoc SQL Plans<br />
  5. vanirame New Member

    Thanks Joechang,
    We had monitored those counter already.
    There are very few re-compilations.
    any time 20% of batch requests/sec ~= compilations /sec.

    follwoing presentation from microsoft might be the answer (slide 13)
    because we are using older hardware.

    http://download.microsoft.com/downl...e-8f89fcaac0bd/SQL_OLTP_BestPractices.ppt#414,25,Performance Issues How to Evaluate Blocking

    SQL Server 2005 OLTP
    Implementation findings:
    SQL Server 2005 can use more CPU
    In-place application upgrades from SS2K to SS2K5 (same hardware) often result in 20-30% more CPU utilization
    Especially noticeable with batch or serialized operations

    The problem is primarily attributed to:
    Higher query plan compilation costs
    More code/larger working set
    Resolution/Workaround
    In some cases enabling ‘Forced Parameterization#%92 helps
    True mostly with older Hardware with small L2/L3 cache

  6. joechang New Member

    i have extensive tests of SQL 2000 and 2005, oldest systems being a 2 x Xeon 3.2GHz, 1M L2
    from a couple of years ago, just before launch, on the last beta

    of course all my tests used stored procs, not SQL
    hence, no compiles, no recompiles

    my recollection is
    except for small hash joins, SQL 2005 is about equal to 2000 in most small queries
    SQL 2005 shines in large queries with parallel execution plans
  7. vanirame New Member

    My case is few stored procs more dynamic sql (Non sharable plans).

    I guess we may have to conclude that,
    Compilation is more expensive in 2005 so application scalability will be lower with 2005 if APP
    DO NOT USE STORED PROCS AND parameterized queries.
    Thank you,
    V
  8. MohammedU New Member

    Did you reindexed all tables OR update statistic with FULL SCAN after restore/migrate databases from 2000 to 2005?

    Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
    http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

    Forcing Query Plans
    http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx

    Understanding Plan Guides
    http://msdn2.microsoft.com/en-us/library/ms190417.aspx

    Forced Parameterization
    http://msdn2.microsoft.com/en-us/library/ms175037.aspx




    MohammedU.
    Moderator
    SQL-Server-Performance.com
  9. joechang New Member

    does any at MS actually recommend update statistic with FULL SCAN on SQL 2005?

    SQL 2000 had a well known deficiency on statistics sampling,
    apparently some bright developer did not appreciate the importance of true random sample vs being more efficient with a random selection of pages
    oops
  10. ndinakar Member

    When we did our load test for a 5 TB db, 4000 users (on a HP superdome - 64 GB, 32 way 64 bit processors) SQL 2005 crashed 5 minutes into the test. SQL 2000 worked fine. This was before 2005 SP1 came out. Looks like there have been some updates in the SP2. so we are going to try another series of load tests again. We are hitting the threshold of 2000 in lot of areas so getting to 2005 is becoming more critical. At the same time all tests we did with 2005, we were never comfortable with the way 2005 was handling. Hopefully the SP2 has some magic <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************
  11. joechang New Member

    5 min, even for a load test (heavier than normal)
    is pretty bad

    numa systems are always tricky, i have seen many people get worse performance one numa than 4 socket non-numa because they did not understand the characteristics of a numa system

    still, i think should do some investigation of the cause of the crash than scare people from 2005,
    of course, it is always to good to stress the importance of testing,
  12. MohammedU New Member

    quote:Originally posted by joechang

    does any at MS actually recommend update statistic with FULL SCAN on SQL 2005?

    SQL 2000 had a well known deficiency on statistics sampling,
    apparently some bright developer did not appreciate the importance of true random sample vs being more efficient with a random selection of pages
    oops

    I don't think any MS recommended update statistics with FULL SCAN but I think it is the best way complete distrution of the data without REINIDEXING the tables...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  13. ndinakar Member

    >>I don't think any MS recommended update statistics with FULL SCAN but I think it is the best way complete distrution of the data without REINIDEXING the tables...


    No, we had PSS engineers come on site and suggest using Update Stats with FULLSCAN. Earlier we allowed default sample size and it did screw us few times. There's no general rule as such. I guess it depends on how your data changes. Sometimes, on large tables, update stats takes longer than actual reindexing itself.

    And I dont mean to scare people with 2005. For most small systems I did not hear any complaints. But for large systems, it makes a big difference. CPU overhead is 10% higher just from the upgrade. MS told us its normal and expected. So there are lot of considerations before you pull the trigger..

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  14. joechang New Member

    dinakar:
    now you have me very curious
    who from PSS came for a problem on the big iron
    numa systems is not something 1st level guys can handle
    did MS send one of the 4 big guns?
    (the ones not allowed be on one plane together)

    also on the statistics,
    it is one thing to blind FULL SCAN a small db
    but not a big db,

    it turns out their is good statistical theory for the # of rows sampled in 2000.
    their problem is not taking a true random sample
    so it is columns where there is correlation between the value and the page it resides

    so for a very large db, it is very important to use intelligence in identifying the correlated columns of very big tables for FULL SCAN treatment
    did the MS rep explain this?
    or was he blindly following a laundry list

    anyways, even small systems have problems with 2005 upgrade
    what i like to see is root cause analysis
    i think is kind of sad that MS did not more clearly identify these issues
    it kind of suggests their test set is not sufficiently diverse
    it is understandable in the SQL 6-7 days,
    but SQL is now big time
    they should have sufficient staff and expertise to catch more of these issues
  15. vanirame New Member

    From MohammaeU
    Did you reindexed all tables OR update statistic with FULL SCAN after restore/migrate databases from 2000 to 2005?
    ---------------------
    I updated the statistics with FULL SCAN and rebuilt the indexes BUT did not notice much of difference.

    Interesting facts I noticed with 2005 and 2000 are:

    1. 2 CPU hyper (L2 : 512K), SS 2000 : after stating about 200 users, CPU utilization jumps from ~25% to 100% and stay there for half minute, comes back to around ~20 -30%. When 230 users loaded, system again jumps back to 100%. Load is very typical and nothing really changed.

    2. "parameterization = forced" on master database seems made difference though I do not have any logical argument behind that.
    I managed to run 300 users (which was about ~230 users with out master setting.).

    3. During our stress test (Test how many application transactions in 24 hours )
    a. 2 CPU hyper threaded (L2: 512K), Win 2003, SP2 + 4 GB RAM, SS Enterprise 2000 resulted: 42,000 /hour CPU is the bottleneck.
    b. Same hardware but SQL server 2005 + SP2 resulted: 23,000 /hour CPU is the bottleneck
    c. 4 CPU dual core, hyper threaded (L2: 4MB), 6 GB, 2005 SP1 + hot fix resulted : 200,000 / hour CPU is the bottleneck

    Looking in older lower level hardware (a and b) : 2000 performs better than 2005.
    Newer hardware (a and c): 200,000 on new hardware SS 2005 which is more than 4 times of 42,000 ( 2 CPU hyper) but CPUs are just 4 times (theoretically)

    That could be the reason some people find 2005 performs better than 2000 and vise versa.
  16. joechang New Member

    SQL 2005 does perform better on the same hardware for many large queries involving parallel execution plans

    SQL 2005 performs about equal in small queries if you are using stored proc, have very little compile & recompiles
    apparently not, per your test, if there are compiles & recompiles

    it would probably help if you provide the CPU info for old and new systems
    interpreting CPU usage with HT enabled is a very tricky matter,
    for testing purposes, it might be better to disable HT to establish the volume vs CPU curve

    now just guessing on your CPU
    going from 2 (Netburst) Xeon single cores
    to 4 dual core (Core 2), 4X the number of CPU cores

    on SQL 2005, from old to new, 42K to 200K, which is 4.76X gain

    going from 2 to 8 cores, (same core) you would expect approx 3X gain
    (very difficult to get linear scaling)
    so you are probably getting 1.6X gain on the faster cores
    this would be about right for NetBurst 3GHz/1M to Xeon 5160 3.0GHz/ 4M shared by 2 cores



  17. vanirame New Member

    Hi Joechang,
    Thanks for your analysis.
    Older hardware is: 2 CPU single cores hyper threaded, Intel Xeon 2.8G, 4GB RAM, L2: 512K
    Newer hardware is: 4 dual core, 6 GB RAM, L2: 8M (Note the correction on L2 and I do not have document right now but post more information soon)

    Our application is pure OLTP so we set parallelisasm to 1 (tried all possibilities but figured 1 is optimal).
    We did NOT notice re-complies but 20% of batches/sec ~= compiles/sec.
    We do not use lot of stored procs but dynamic sql (non sharable plans in this application version.) so we are using parameterization=force.
    (Interestingly forced parameterization/sec on perfmon is consistently 0, may be bug but forced has performance boost)

    I believe SQL server 2005 more optimized towards data warehouse type queries so we may NOT notice performance gain and as per Microsoft guys we may notice 20 to 30% more cpu utilization (comparing to 2000) due to more code and compilation is more costly in 2005.

    Any suggestions always appreciated.
    Thank you,
    V
  18. joechang New Member

    my goof, the current Intel 4 socket system is not Core 2, but still the old NetBurst

    hence 8-16M L3, shared by 2 cores
    each core with 1M(2?) L1

    in which case, you are getting a large boost from the cache

    the question to you is: are you satisfied with your current situation? 200K/hr on the big box

    are you ready for some math?
    200K per hour = 55.5 per sec
    since you have 8 cores,
    thats 6.94 per core per sec

    or 144 CPU-millisec per transaction (which may be made up of several SQL calls)

    given how power CPU's are today, 144 CPU-ms is alot of compute cycles (432 Million)
    I am willing to bet alot of it is in the compile, rather than the SQL execution

    anyways, a brief word on forced parameterization

    SQL 2000 would only autoparameterize the very basic statesments
    SELECT x FROM TABLE WHERE col = y
    UPDATE TABLE SET col1 = x WHERE col2 = y
    same for DELETE and INSERT(?)

    ie, no joins, single sarg

    in SQL 2005, force parameterization means it will parameterized queries more complicated than above, multiple SARG, joins

    which can help

    but still, stored procs are best
    with a few explicitly parameterized SQL
  19. vanirame New Member

    Hi joechang,
    You are right. Set statistics for several sql statements revealed that execution time 0 ms where compile time most of the time <non zero> ms .
    We are aware that parameterization boost the performance of our application .

    As a software vendor, we support several other databases (oracle, sql server etc..) and there is never ending debate inside weather business logic in middleware or in the database (but unfotunateley middleware wins many times)
    Thank you,
    V

  20. joechang New Member

    are you aware of Hotsos (www.hotsos.com) in Dallas TX
    they are the premier Oracle performance experts
    they host an annual conference (early March i think)

    the number 1 item to get decent performance:
    use stored procs!!!
    period

    Tom Kyte, the premier Oracle performance guru says the same thing

    it is only lazy worthless developers who try to argue sending SQL to the database
    i have had shot (got fired) a bunch of those in my days
  21. ndinakar Member

    quote:Originally posted by joechang

    dinakar:
    now you have me very curious
    who from PSS came for a problem on the big iron
    numa systems is not something 1st level guys can handle
    did MS send one of the 4 big guns?
    (the ones not allowed be on one plane together)

    also on the statistics,
    it is one thing to blind FULL SCAN a small db
    but not a big db,

    it turns out their is good statistical theory for the # of rows sampled in 2000.
    their problem is not taking a true random sample
    so it is columns where there is correlation between the value and the page it resides


    I dont remember the names but everytme its a different person although the account mgr remains the same. Even yesterday we had 3 people from redmond and they had no clue what
    STATS_NORECOMPUTE does in an index script. They are making us run around. one says it computes stats for the data at that time but does not do auto update for future inserts/updates. another says it never gets updated..another says he doesnt know and he will go back and check.
    one guy asks us to do update stats with full scan. we did that (during prod peak hrs and the MS guy was on the call )and our system crashed. we had 17883 errors. later they said the update stats caused it. another comes back and says our stats is not up to date so we should do update stats with full scan since default is not covering enough records in its sample..i am just tired of these guys..

    btw, i saw your home page and your rates..10k/week?? you are damn expensive!!! If you get around 10 contracts per year you make more than what I do working 52 weeks!!!

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
  22. vanirame New Member

    That is what carry Millsap's or Jonathan lewis fee. Just kidding. May be we get you some time for analysis.
    Regards,
    V
  23. MohammedU New Member

    As per the information I got from one of the MVP about statistics was "SQL server 2000 will not use the right sampling Algorithm when use default sampling which is 10 or 20% OR certain percentage..."

    I don't know any improvements in sql server 2005 about statistics Algorithm...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  24. joechang New Member

    Dinikar
    i can see that PSS will not send Bob D, Bob W or Keith E
    but you cannot send a rookie that says UPDATE STAT WITH FULLSCAN on a 5TB database without proper analysis
    If do not not have ethical problems, I suggest putting about 30TB of worthless data in your db (that does not actually get used)
    then ask MS to send someone from their SQL CAT group, instead of a rookie who should not be allowed near a multi TB NUMA system

    Since you are on a SuperDome, I can suggest a couple of HP people with proper NUMA/SQL skills, they will not be easy to get, but there is no point having a general purpose person work on NUMA

    On the matter of me being very expensive
    I learned long ago (especially at Intel)
    if you are not very expensive, your advise is not taken seriously
    the more expensive you are, the weight given to you
    I know some very good people, hired out of college,
    became very good, but were not given proper weight until their reputation was established

    Mohammed,
    your MVP statement sounds weak, see my paper on this subject,
    precise analysis is critical

    Vanirame:
    I thought Cary charges $275/hr. I am only $250, cheap!
    I am not afraid to say, I stole many of Hotsos methods from Oracle to SQL Server,
    with my own precise techniques as applicable to the specifics of Windows and SQL Server.
    but still, you would get Cary if the purpose is to convince upper management
    for the straight technical, get Jeff H, I don't care how much you have to pay

    You will find what hotsos does on Oracle, I do similar on SQL
  25. joechang New Member

    Mohammed
    now that i read your statement from the MVP more carefully

    let me say more precisely,
    actually I said it earlier
    there is nothing wrong with the default sample row count
    it is based on very good statistics theory of how many samples you got,
    and what the sampling error is

    the key to proper sampling is getting a true random sample
    which SQL 2000 does not do,
    it picks random pages, and samples all rows in those pages

    so if many rows in a certain page had the same value (because they were inserted together) you sample is worse than worthless,
    in effect it tells you not to use the index because the row count is too high

    anyways, the SQL 2000 default is not a fixed percentage
    but rather examines the distribution of data to determine the proper count

    I recall someone saying the MS was aware of the problem of not taking a true random sample in 2000, and corrected this in 2005

    this is why in SQL 2000,
    if your data in not page correlated,
    the default sample percentage is fine,
    but if it is page correlated,
    you are screwed at any percentage, short of FULLSCAN

    this particularly happens with parent child tables
    if you index the OrderID column in LineItems,
    the distribution is correlated because all the LineItems that belong to a particular order were inserted together



  26. joechang New Member

    Dinakar:

    on the matter of 10 weeks vs 52 weeks
    if you think about it

    I am guessing most of your time is probably spent doing routine matters that a less experienced DBA could handle
    and that only occasionally do matters call for an expert

    so is not better to off load the light stuff to someone who needs to learn
    and enjoy yourself for 42 weeks each year, instead of the 2-3 week vacation for most jobs
  27. ndinakar Member

    I am not sure how the logistics works when MS sends a guy over. Perhaps it depends on the agreement we have with MS. So sometimes we do get burned out with rookies. <br />I totally agree that you cannot run update stats on 5 TM db. especially not when production at its peak. <br /><br />And yes, if I have the luxury/choice to choose 10 vs 52 its hardly a choice anymore. I still need to learn some more (perhaps lot more) before I get to charge that much <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />But at least I am on the way there and on the right track.<br /><br /><br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************
  28. joechang New Member

    you know about the MS SQL CAT right?
    for the big presitige SQL installations
    they will send one of the big guns out, no charge to you

    http://blogs.msdn.com/sqlcat/


    32-way Superdome cuts the mustard, but 5TB is not as impressive these days
    thats why I said to inflate your db size.
    hopefully noone from CAT is monitoring this (heh heh)
  29. MohammedU New Member

    quote:Originally posted by joechang

    Mohammed
    now that i read your statement from the MVP more carefully

    let me say more precisely,
    actually I said it earlier
    there is nothing wrong with the default sample row count
    it is based on very good statistics theory of how many samples you got,
    and what the sampling error is

    The comments I was referring to was heard from Either from Kimberly T or Kalen D in one of their classes...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  30. joechang New Member

    i think they basically just repeated whay heard from their reps at MS
    without inquiring to the exact details

    this is why i insist on precise analysis
    not hearsay
  31. ndinakar Member

    The guy from CAT is the one who wants to go back and check <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************
  32. joechang New Member

    so atleast the CAT guy was honest,
    even Gert says the true documentation is the source code
    that's why he just finds the appropriate code to look up what it actually does
    instead of wasting time with the doc, or hearsay

    since Kim and Kalen are really big names,
    just to prove i am precise on statistics

    create a table with exactly 71 bytes wide, with a unique clustered index,
    the actual table (with 11-12 byte overhead) will be 79-80 bytes
    exactly 99 rows will fit in each page

    populated it to 9.9M rows, or exactly 100,000 8K pages
    populate 1 column as i/99, which increments every 99 rows, so all values in each column are exactly the same

    a second column should be i % 100000,
    in each case, each value occurs exactly 99 times
    while the second is not random, is random for the purposes of a random page sample

    for good meaure, populate another column as 100000*rand(checksum(newid()) + 1

    then generate statistics with varying sample, from default to 100%

    you will see 2 & 3 are fine with default
    1 will be completely wrong at all but 100%

    so it is not necessary to blindly sample at 100% for all
    but only the page correlate columns

    this is what i mean be precise
  33. joechang New Member

    this might be of interest:

    I did the following tests 3-4 years ago with the then current SQL Server &
    Windows Server 2003.
    for a 2 processor Xeon 2.4GHz, the CPU cost break down is as follows

    A network round trip, ie, a stored procedure call to SQL Server that
    does nothing, costs 0.10 CPU-milliseconds
    meaning 1 processor can drive 10,000 network round trips/sec
    2 can drive 20,000/sec
    (actually 1 proc can do 12K/s doing to scaling characteristics
    but lets not talk about that here)

    A stored proc that does a single row index seek with bookmark lookup
    costs approx 0.15 CPU-ms, meaning the 2 proc system can do 13K/sec

    Of this 0.15 CPU-ms, 0.10 is the network round trip
    of the remaining 0.05, about 0.01 is for a one time cost,
    the other 0.04 is the incremental cost of actually executing the SQL

    Now, if I put a WITH RECOMPILE on this stored proc that does a single row index seek,
    the cost is about 2 CPU-ms,
    meaning the cost of a very simple compile is 20 times higher than
    the network round trip
    and 40 times higher than the cost of the query

    for more complicated queries
    compile costs of 100 CPU-ms or even 1-10 CPU-ms are not unusual

    My tests indicate that the SQL execution costs in SQL 2005 are more or less comparable to SQL 2000, with a few exceptions (small hash joins are much higher)

    this is why you should use mostly stored procs with fixed SQL,
    and some parameterized SQL for high volume apps
    never send high SQL call to SQL Server

    if any developer says to put the business logic in the App server,
    and send SQL to SQL Server instead of stored
    Immediate firing squad is the appropriate remedy
    or you will pay for it later
  34. mmarovic Active Member

    Stored procedure question is not just about performance, it is matter of separation of component interface (in this case db component) from its implementation. From performance point of view you can have comparable performance (because the same exec plan caching mechanism is used) from procedures and parametrized dynamic queries on both Oracle and Microsoft SQL Server. The advantage of stored procedure is also in more managable security.

    *

    About business logic place: You can have both business logic on the middle tier and use stored procedures. Stored procedures can be used for atomic operations and you can keep complex logic on middle tier. You would have more round trips, but you would save db server from coping with complex business rules. That solution may be more scalable, because it is easier to load balance application servers then db servers. Monster.com I worked for used that approach.

    *

    I don't work with mssql server for almost 2 years now, so I can't really comment on differences between 2000 and 2005, however, comparing two editions without tunning to the strength of each, doesn't tell you the whole story.

Share This Page