December 20, 2002 at 6:31 am
My question is simple - what are the tangible benefits of a multiprocessing system over an otherwise identical single processor system when using SQL Server 7.0?
Take, for example, a server class machine with 4 Gig of memory, running Win2k server and a 2.4Ghz processor. What benefits will be gained by adding another processor?
Is SQL 7.0/Win2k server intelligent enough to load the processors so that queries are processed at an optimal speed, or is the benefit merely in being able to process more than one query at a time without a loss of speed?
December 20, 2002 at 7:25 am
As far as I am aware SQL Server 7.0/2000 are both fully SMP aware.
Having additional processors increases the number of available processor threads which increases the number of concurrent tasks for the Application. This improves concurrency but can also aid the performance with very large queries.
When the SQL Query Optimiser decides that a queryplan is over the threshold to consider for running on multiple processors it will check the availability of the processors and split the SP to run across the available processors.
I think the general rule is more processors the better when it comes to SQL.
December 20, 2002 at 7:36 am
The answer is unfortunately not as clearcut as that. Many times I have seen performance drop significantly between writing something on my single processor development system, take it to a client with say a 4-way SMP server and things go slow. The changes to a query plan to stream then gather parallel sections of data for processing on multiple processors can often lead to all sorts of delays. The problem I believe is caused because the procedures that run in parallel have to synchronise with each other across CPUs, and that can cause delays as other queries are not executable whilst that is taking place. I usually tried to code out parallelism from my query plans by altering indexing and using other design patterns.
December 20, 2002 at 7:41 am
I fully understand what you are saying sjcsystems, I to have experienced queries which cause problems on SMP machines and have had to use then OPTION(MAXDOP n) option.
I think that any good DBA should be analysing how each SP works and know how to work around each of the issues. Query Optimister, clever though it is, is not always as smart as a good DBA.
December 20, 2002 at 4:51 pm
I agree with both paulhumphris posts. I've seen the drops in performance myself in particular situations, but with a little work, you can set the level for considering a query for parallelism to the proper level to achieve a "sweet spot" that works best for your system.
I also believe that though the query optimizer is nice, it will never perform as well as a "good" dba as it's like all microsoft products. They are designed with the multitudes in mind. They must be generic to work in every situation. Have you EVER seen ANYTHING that was both generic and optimized. The very terms are conflicting. A generic solution works for the average. An optimized solution works for a particular. Microsoft, or anyone else for that matter cannot design for every situation, so they design for the most likely situation. It's also an extention, in my mind, of removing the memory management capabilities from SQL. I could do things with 6.5, as far as memory is concerned, that flat can't be done with 2000. It's a shame, but the masses needed the ability to place less than efficient dba's in charge of the servers, and rather than teach them how to manage memory in SQL, we just dummied up Sql to do it generically. It also follows the age old adage of "Ease of use, means loss of functionality."
December 20, 2002 at 5:56 pm
The amount of on chip cache can make a huge difference too. From what I understand having a smaller cache results in more cpu flushing, decreasing performance.
Andy
December 20, 2002 at 6:20 pm
Faster processors do not always mean faster performance. The first bottleneck is from the Hard Drive to the CPU across the motherboard so make sure the motherboard itself has large bandwidth to the processor. Also, processors may have sideeffects when added as the OS does not always fully optimize if you add to an existing install I cannot remember where but there is a site that covers how you can correct this on the internet. More processors primary bennifit is the fact you can execute more threads at the same moment. Even thou processors are noted as multithreading they actually stop and start threads individually, one thread only processing at any given division of time, based on specific conditions (Intel has details somewhere on there site explaining in detail). Also, keep in mind adding another processor also mean changing your licensing if you are under perprocessor license so don't forget that, but a way you can get extra umph without a true extra processor is to look at the new Intel processors that use hyper-threading (more or less it is two processors on the same physical processor) and licensing by MS is confirm as per physical processor not logical. As Andy alos mentioned, get a CPU with the largest on processor cache you can as this means data is not pushed off as fast to the motherboard cache or ram and offers higher performances. The next thing is have a motherboard with as large a cache as possible and get fast RAM to further speed the pipeline of data (PC100, 133, DDR 2100, 2200 just don't cut it with a 2.4 Ghz monster, you really need RamBus or high end DDR). Then you also deal with HD speeds as bottlenecks and network processing bottlenecks. I personally would be carefull adding a processor to an existing box and try to pair them up in a new box with a clean install where the OS has detected the multiprocessors from the start if this is at all an option. After all that you may still have so SQL fine tuning to do.
December 23, 2002 at 2:34 am
Hmm, some food for thought there. Thank you for your replies, any more advice would also be useful.
I was fully intending to make sure I get the best out of the rest of our hardware (CPU and mobo cache, high speed memory etc) - it's just the processors that are the sticking point at the moment - as I add processors, the cost of a server escalates alarmingly.
To be honest, looking at the benchmarks I've done, the server could just about manage on one of the 2.4Ghz processors, and so I think that 2 processors would make it fly... and to think, I was originally looking at a 4 x 2.4Ghz system.
December 23, 2002 at 2:47 pm
SO if I am to understand the reply’s to the thread, there doesn’t seem to be very much that can be done here other then having an 2CPU (4CPU if you got the money). A lot of memory and a fast hard drive. Perhaps some tweaks to some of the DB OPTIONS as well. Has anyone here seen any performance stats on the SOLID STATE harddrives that they are marking now? The company that was making then said that they were getting about 5-10 times of a speed increase over a typical 15k RPM SCSI drive setup.
December 23, 2002 at 6:55 pm
No but from what I have seen on Geek.com (they apparently have some) they are supposed to be monster on speed and performance. But the cost is way out there, ($54000 for 3.2 Gb last I saw) which does not make a worthwhile solution except in a high end transaction situation such as stock market. Also, I am not totally sure here but what I have seen tells me there are 2 types the battery powered memory and the compact flash type. The problem on the later is I saw a statement that performance can degrade over time on CF cards and they may suffer other issues with repeated use. If that is the case then do the SS HDs suffer the same potential issue. And in regards to the battery memory what happens if the battery fails? Not sure i would be comfortable with my data in that potential risk environment. Of course this is summation on my own and have not had the chance to have a vendor try to let me test drive.
December 26, 2002 at 9:30 am
Solid-state memory cards are great, expensive, and not cost-effective for every application. However, they reduce the slowest bottleneck in the chain, the hard drives.
Get a good book on SQL performance and a book on server performance, then test your specific application as you make changes.
Caution: Avid Learner.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply