다음을 통해 공유

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

  • 아티클

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

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

  • Anonymous
    January 01, 2003
    PingBack from http://www.hilpers-esp.com/712073-uso-de-varios-procesadores

  • Anonymous
    February 02, 2005
    The comment has been removed

  • Anonymous
    February 04, 2005
    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

  • Anonymous
    February 13, 2005
    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!

  • Anonymous
    February 14, 2005
    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