SQL Server Performance Forum – Threads Archive

SQL Server 2000 vs 2005 load test

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

---

RESOURCE_SEMAPHORE_QUERY_COMPILE
Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans. http://blogs.msdn.com/sqlprogrammab…ormance-problems-and-suggested-solutions.aspx
http://blogs.msdn.com/sqlqueryproce…-applications-when-upgrading-to-sql-2005.aspx
MohammedU.
Moderator
SQL-Server-Performance.com

---

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

---

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 />

---

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

---

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

---

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

---

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

---

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

---

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 />***********************

---

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,

---

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

---

>>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
***********************

---

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

---

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.

---

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

---

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

---

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

---

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

---

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

---

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
***********************

---

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

---

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

---

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

---

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

---

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

---

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 />***********************

---

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)

---

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

---

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

---

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 />***********************

---

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

---

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

---

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.

---

]]>