Mat Stephen's SQL Server WebLog

All things SQL Server. Complied by Matthew Stephen - SQL Server Product Specialist, Microsoft UK

Blogs

Microsoft SQL Server Performance Top Tip: Multi Processor (SMP) Box Doesn’t Use All It’s Processors

  • Comments 9
  • Likes

Seen this?  You’ve got a nice new sparkling multi processor box, packed with ‘go-faster’ chips but the performance isn’t much better than your old one or two processor box.  You take a look at the processor usage, either through task manager or Performance (System) Monitor, and notice only one processor seems to be hard at work, the others are just idling.

 

Steam comes out of your ears; you’ve just spent all this money and what for?  Well before you drop a wobbly have a look at how many files you have for your database out on disk.  Is it one .mdf and one .ldf – the default?  Yes?  Well that’s most likely your problem – you can only write to one file with one thread.  Your database has become IO bound as only one processor can write to the database at any one time.

 

Solution – create a new database with as many data files (all  the same size) as you have processors and transfer the data from the old database into this new one – this is to ensure you get an even proportional fill across the files.

 

PS If this isn't a new box and you used to get good performance see SQL Server Performance Top Tip: Multi Processor (SMP) sudden death syndrome.

 

 

Comments
  • Would this also apply (to a lesser degree) to MSDE and P4 HyperThreading?

    (MSDE should be able to use up to 2 processors - or would overhead kill performance?)

  • Yes - I guess it would apply to MSDE on a two proc P4 with HyperThreading.
    INF: SQL 2005 express wil only use one phyiscal processor

  • But remember/be carefull that a hyper threading core is competing for the CPU Bus and LEVEL 1 Cache and the current Thread shedulers in Win2K3 and Sql2000 are not aware of Hyperthreading so they might schedule the wrong thread as they expect 2 real cpu's.

    Good Post!

  • SQL Server didn't always support Hyper-Threading, but support has been introduced steadily through service packs. Service pack three introduced full support for Hyper-Threading. Full details can be found @ http://www.microsoft.com/sql/howtobuy/SQLonHTT.doc



  • During my TechNet presentation on Tuesday evening (14/6/05 in Reading), I got a little side tracked and,...

  • PingBack from http://www.hilpers-esp.com/712073-uso-de-varios-procesadores