SQL Server Performance Forum – Threads Archive

Splitting CPU

For budget constraints (can’t buy new license for another 2 CPU), we are looking to split our SQL 2000 Reporting Server’s 2 processors (in Hyper-Thread, so it looked like there’s 4 CPU) and put the other half into the new SQL 2005 Reporting Server. I wanted to know how exactly to monitor the CPU performance on the current SQL 2000 Reporting Server with 2 processor so I can justify whether or not splitting the CPU will have a drastic effect on our current reporting server. I have gone to Performance Monitor on % Processor Time, % Priviliged Time, Processor Queue Length, Context Switches/sec. But there’s no clear indication if making the sql 2000 server run on 1 CPU (hyper-thread), can support all the processess. Any help is greatly appreciated. Tnx.

i am not sure what you are saying is clear
do you have 2 physical processor (sockets) or 1 whatever you do,
do not put transactions on one logical proc, and reporting on the other logical proc of the same physical proc

I probably didn’t make it clearer. Yes i have two Physical processor. My Transactional server is on a separate box with 4 physical processor with HT. The idea is to just put the current reporting sever run in 1 physical processor with HT, instead of the current 2 with HT. How do I analyze CPU Performance Monitoring, so I can justify that removing 1 physical CPU is still acceptable? thank you very much.

first, i am not entirely happy with HT and SQL Server 2000. Other applications, without contention issues can get significant benefit from HT, Quest LiteSpeed which compresses the SQL Server backup gets as much as 40% performance gain with HT. SQL Server however, does not, except for high call volume apps, which typical retrieve a single row with each network roundtrip, where 85% of the cost is for the round-trip, 15% for the actual query. SQL disk IO also appears to be disrupted when SQL can use all procs. My recommendation is that you leave HT enabled in the BIOS, but allow the tx SQL instance to use just the lower 4 procs (which should be the first logical proc of each physical) for good measure, collect the performance counters I list in: http://www.sql-server-performance.com/qdpma/inst_3_pmlogs.asp</a> adding SQL Server<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />atabases -> Transactions/sec in your situation, max CPU will be ~50% because SQL can only use 4 of the 8 logical procs you want to know if your tx can run on 3 procs, which will comprise 37.5% of 8 procs. for tx, cpu should be below 70%, or in this case, 70% of 37.5% which is 26.25% if your cpu is below 26%, you can probably drop to 3 procs testing is always helpful i would run a 24 hour profiler trace at 4 procs, then at 3 procs, comparing the CPU and duration of key procs

this really gave me an idea how to properly evaluate my processor. thanks a lot

]]>