SQL Server Performance Forum – Threads Archive

extra processor performance

How much extra performance does another processor add to SQL Server 2000? Our client has just ordered a new PowerEdge 2850 with 2GB ram, lots of disk and two processors. Now they want to know if they need to buy one or two processor licenses for SQL Server 2000… I asume adding a processor does not double performance as ram, bus and disk access play a large part in the hardware performance of the server. So how much performance does it add generally? 25% 50% 75%? The db will serve up to 40 simultaneous users running mostly SELECT statements… Marius

The four most important aspects of performance monitoring are: – CPU Usage
– Memory Usage
– Network Traffic
– Disk I/O PERFMON provide information about these four aspects as they relate to SQL Server: server’s CPU usage; memory usage; I/O subsystem; network traffic; and disk access. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most system. Paying extra attention to disk I/O is worth while. Teratrax Performance Monitor has two counters to monitor Disk I/O: the I/O counter and the Disk Access counter (see monitoring disk I/O). When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time can be used. If this causes the performance of other applications to suffer, try changing the workload (for example, by dedicating the computer to running the instance of SQL Server). 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.

Thanks satya, but you’re not really answering the question…

Marius, It really depends on the type of database you have. If a lot of analysis or aggregation of data is being done, then more processor will be used. Having more processors can also force even smaller, simpler queries to be pushed through by utilizing parallelism across multiple processors. So, depending on the type of database, you could see anywhere from a 5% – 175% increase in performance. It’s extremely variable. You are missing a little detail on the licensing though. You can either use CAL licenses, or you can use processor licensing. IF you use processor licenses, you MUST license all physical processors on that server. The only way around this is to actually physically disable or pull one of the other processors. Setting SQL Server to use only one of them will not work. It’s the physical processors in the box. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

I think he did.
If you run Performance Monitor and CPU is over 80% all the time and/or Processor Queue Length is over 2 per processor all the time, then you may be need more processors. I said may be, because you have to confirm that after you finish with all performance and tuning task, like find long queries, etc. But how much in % will performance improve with 2 more processor is dificult to predict. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

My reply is intended to perform more analysis by using PERFMON and other available tools, based on the research you will know the facts. As a whole adding more memory will definetly have a major performance gain, but due to the poorly written queries there will be no use if the server have 4 or more processors to use.

quote:Originally posted by mariusan Thanks satya, but you’re not really answering the question…

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.

MeanOldDBA, Thanks for the info on the licensing. I had a look at the license and you are right. It needs to be disabled for this to be a viable solution… Thanks you all for your input <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Marius

]]>