SQL Server Performance

XEON Processor and Single CPU

Discussion in 'Performance Tuning for Hardware Configurations' started by mdouthitt, Apr 22, 2003.

  1. mdouthitt New Member

    Our new Dell SQL server has a single XEON processor. SQL configuration has picked up the system as multiprocessor and when I look at cp performance with Task Manager, it shows two distinct cpu's.

    Based on the number of issues with parallelism, queries and performance - should I set the processor configurations at single cpu?? Could my queries be taking extra time analyzing whether to use 1 or 2 cpu's??

    Any ideas why this happens with a XEON processor?

  2. Chappy New Member

    What operating system are you running?

    I could be completely off the mark here, but is this Xeon one of the new hyperthreading processors ? Hyperthreading is designed to boost platform performance due to enhanced thread concurrency, but even on single processor systems, one of the side effects (reportedly, noone seems to be able to confirm definitely), is that the cpu sometimes appears as more than one cpu to Windows.
    Do you know the exact cpu installed ?

  3. mdouthitt New Member

    quote:Originally posted by Chappy

    What operating system are you running?

    I could be completely off the mark here, but is this Xeon one of the new hyperthreading processors ? Hyperthreading is designed to boost platform performance due to enhanced thread concurrency, but even on single processor systems, one of the side effects (reportedly, noone seems to be able to confirm definitely), is that the cpu sometimes appears as more than one cpu to Windows.
    Do you know the exact cpu installed ?



    Chappy,

    The server is a Dell PE2650, 2.0GHZ/512K XEON. I don't know if it is classified as hyperthreading but it does appear to windows as a multiprocessor system. We are using Windows 2000 Server Standard Edition.

  4. Chappy New Member

    The PE2650 does come with XEON(s) which support hyperthreading..
    http://www.euro.dell.com/countries/uk/enu/pub/products/model_pedge_pedge_2650.htm

    And this Intel link confims hyperthreading does not require more than one processor, which I had wondered about..
    http://developer.intel.com/technology/hyperthread/

    You should have a BIOS setting allowing you to disable hyperthreading. You could try this if you wanted real proof this is the cause.

    If this is the case (and it sounds likely to me), then to answer your original question, I would not perform any processor specific changes to sql server. If windows thinks theres two cpu's, and you tell sql to only use one of them, you might end up only using half the cpu's true capacity or maybe under utilise the hyperthreading feature).
  5. Argyle New Member

    You have one of the new hyperthreading CPUs. P4 Xeon started with that at 1.8 GHz I think. And as you have noticed it will show up as two processors in windows.<br /><br />SQL 2000 with SP1 and above can utilize hyperthreading in some extent, SQL 7 can not. Hyperthreading does not mean that you get the performance of two processors for the price of one but it definitly can improve performance. You will have the same benefits (and issues) with parallellism with these two "logical" cpus as you would with two "real" cpus. Test your queries as you would with any dual machine.<br /><br />Also one good thing is that you only need to buy one processor license and not two [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />/Argyle<br /><br />
  6. bradmcgehee New Member

    We are now using hyperthreading Xeon's, and they help with performance. I have one server with 4 Xeons and the OS tells me that I have 8 CPUs. While not performing as well as 8 CPUs, the server is very fast.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  7. Jacco New Member

    Windows 2000 will see the hyperthreaded CPUs as 2 different CPUs. This should change in Windows 2003, as that is designed with hyper threading in mind. One issue with SQL Server and hyperthreading is that Standard Edition is only licensed for 4 processors on any Windows edition except Data Center. AFAIK pre SP3 Standard Edition would only use 4 processors in this case, but I found a post from the MS SQL Development team where they say it's fixed in SP3. I have a 4 Xeon machine at a customer site that runs SQL Standard SP3 on Windows 2000 Standard Server SP3, and I indeed see 8 processors mentioned in Enterprise Manager. I'll test today or tomorrow most likely if I can conjure up a query with a parallel execution plan that actually uses all 8 processors.
  8. ricardosada New Member

    Hi

    Has anyone come to a conclusion about which is better "hyperthreading" or "no hyperthreading" when using just ONE Xeon Processor??. I'm using SQL Server 2000 Standard on Windows 2000 Server and I've a performance issue here, SQL Server doesn't seem to be using the full processing power, just a fraction. I'm running a complex DTS (many tasks) to extract data to text files.

    any help is appreciated

    thanks
  9. ricardosada New Member

    I apparently solved my own issue. The problem was our RAID 1 array (not good for data warehousing as I read in this excelente site).



    quote:Originally posted by ricardosada

    Hi

    Has anyone come to a conclusion about which is better "hyperthreading" or "no hyperthreading" when using just ONE Xeon Processor??. I'm using SQL Server 2000 Standard on Windows 2000 Server and I've a performance issue here, SQL Server doesn't seem to be using the full processing power, just a fraction. I'm running a complex DTS (many tasks) to extract data to text files.

    any help is appreciated

    thanks
  10. stefanoale New Member

    I use dual Xeons (Dell P6600 1.8Ghz/1MB cache) with win2K and sql2000 Server SP3.
    I bought 2 per processsor licenses and even though Windows shows 4 processors (hyperthreading) everything is working smoothly.

    I contaced Dell about this and they recommend to keep the hyperthreading.

Share This Page