Post reply

Only one Processor used!!!

  • November 23, 2005 at 5:22 am

    #188606

    We have a production database which is running in a Windows 2000 machine with SQL Server 2000 Standard edition and SP3 installed. There are 2 physical CPU's and hyperthreading is enabled (4 logical CPU's). Only SQL Server is running in the server. Using Performance monitor, I realized that only one CPU is being used when batch runs are performed. Is there a way to fix this? The solution has to do with SQL Server configuration (Standard edition, affinity mask) or Windows configuration?

     

    Thanks in advance

     

    Petros 

  • November 23, 2005 at 2:44 pm

    #605572

    I haven't got experience with dual cpu's.

    Did you have a look at "parallel queries" in the books online?

    Configuration Settings that Influence Parallel Queries

    The following server configurations can affect parallel execution of the queries:

    • Cost threshold for parallelism
    • Maximum degree of parallelism
    • Maximum worker threads
    • Query governor cost limit

    Is the sql server licensed for 2 cpu's?

     

  • November 23, 2005 at 7:28 pm

    #605606

    interesting question... thank you. the answer seems to be Windows System Resource Manager

    sorry, it doesn't edit very well after cut and pasting...

    the following cut and pasted from Microsoft at:

    http://www.microsoft.com/technet/downloads/winsrvr/wsrmfaq.mspx

    Q. What is Windows System Resource Manager (WSRM)?
    A.

    WSRM allows you to manage CPU and memory utilization on a per process basis. This means that an administrator can run multiple applications on a server with greater safety. WSRM prevents applications from consuming more than their allocated CPU and memory limits, thereby preventing one application from starving other applications of CPU and memory. As a result, you will have a more consistent and predictable user experience.

    WSRM policies can be applied according to a time/date schedule. This allows administrators to free up CPU and memory for maintenance applications during non-peak hours, and for mission critical applications during peak hours.

    The WSRM accounting feature allows administrators to generate, store, view, and export resource utilization reports for systems management, and service level agreement (SLA) tracking and billing purposes.

     

    <script language=Javascript> if(typeof(IsPrinterFriendly) == "undefined") { var q = document.getElementById('question41'); if(q != null) q.style.display = "none"; document.getElementById('answer41').style.display = "none"; } </script>

    <script language=Javascript> if(typeof(IsPrinterFriendly) == "undefined") { var q = document.getElementById('question42'); if(q != null) q.style.display = "none"; document.getElementById('answer42').style.display = "none"; } </script>

     

    WSRM is included in the Enterprise and Datacenter Editions of Windows Server 2003. It is not included in the Windows Server 2003 Evaluation Kit, but can be downloaded separately.

     
    Q.

    Can WSRM be purchased separately for use with other Windows Server operating systems?
    A.

    No. Customers wishing to use WSRM will have to upgrade to either Windows Server 2003, Enterprise Edition, or Windows Server 2003, Datacenter Edition.
    Q. Will WSRM be available for Windows 2000 Advanced and Datacenter Server?
    A.

    No.
    Cheers,

    david russell

  • November 23, 2005 at 11:32 pm

    #605620

    Concerning Jo 's answer, SQL Server is lisenced for 4 CPU 's and from SQL Server configuration it is obvious that all of them are recognized. David, I know WSRM feature of Windows, but I need a solution for the existing machine and software. The configuration of SQL Server has the default values.

     

    Thanks,

     

    Petros

  • November 25, 2005 at 9:37 am

    #605875

    With respect you are all missing the most obvious that despite the number of cpu's a query will always only use one thread unless the query/batch is complex enough to warrent using more. If the sql is simple hitting the server and there's not much of it you'll probably not see much sign of multi proc activity.

    Quite often invoking multiple procs with a query is a sign of a poor query which needs optimisationm and, quite often the parallelism is actually counterproductive.

    If you load up your server you should see more cpu activity.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 28, 2005 at 3:40 pm

    #606247

    I agree with Colin. To see usage of all CPUs you might want to start dbcc checkdb in a loop for a database in one QA window (master), then repeat the process for 3-4 other databases (model, msdb, pubs, northwind)  and start them i separate QA windows as well. Then I'm almost 100% sure you'll see more than 1 processor being hit.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • November 30, 2005 at 1:01 pm

    #606849

    I had a simiar issue about a year ago where a query was running poorly on a new box.  I thought that surely my fire breathing new system would be faster than the old systen.  I noted that only one processor was in use.  In an effort to "optimize", I started foooling aroung with proccesor affinty_mask setting, number of threads etc.  Still no luck.

    I asked an experienced DBA, and he said that proccesor affinty_mask setting, number of threads,etc are pretty esoteric and that the defaults should work fine in most cases. He said that these should only be changed after careful observation and history with the system.  He also suggested that I update DB statistics.

    I changed everything back to the defaults and ran sp_autostats.  After that, my fire breathing box spat out the query results in  1 second.

  • December 6, 2005 at 1:30 am

    #607876

    Thanks for your suggestions.

     

    Regards

     

    Petros Andrikopoulos

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply