SQL Server Performance

Express 2005 not using all cores?

Discussion in 'SQL Server 2005 General DBA Questions' started by jazzy639, Nov 1, 2009.

  1. jazzy639 New Member

    Hi everyoneI have a server for an ASP based application that runs on MSSQL Express 2005. All four cores are selected for MSSQL to use.Right now its only me using the server to test everything. There's a certain page that runs a lot of queries to the the DB in a loop. MSSQL uses 25% of the CPU while this page runs for about 10 seconds.How do I get it to use all available CPU? I've not tried this with many users so who knows - it might only use one core per connection to the server. That doesn't sound right though.Any help would be appriciated!Regards
  2. Adriaan New Member

    If you're asking SQL to run a pretty simple query, there is no point in using more than one CPU. (You may even run into performance issues in case SQL decides to use more than one CPU for a simple query, in which case you need to add a little clause to your query to restrict the query to a single CPU.)
    If you're putting that same query into a loop, again no point in using more than one CPU - especially since the data will probably be retrieved from cache (seeing that you're the single current user).
  3. jazzy639 New Member

    Although its the same query, its different data in the WHERE clause.MSSQL uses one core for about 10 seconds. Isn't it better for it to use 4 cores and run for 2.5 seconds?I know Express uses one CPU. I have one quad core CPU. MSSQL just seems to max out one core...
  4. Adriaan New Member

    Without knowing the table design and the query that you're running, we can only do blind guesses.
  5. Adriaan New Member

    Frank's comment of course hits the spot - it's a built-in limitation of Express Edition ...
  6. jazzy639 New Member

    I'm talking about cores though. Express can use one physical CPU, in my case a quad core.
  7. FrankKalis Moderator

    Check these links:
    http://support.microsoft.com/?scid=kb%3Ben-us%3B914278
    http://www.eraofdata.com/blog/tag/multicore/
  8. jazzy639 New Member

    That's exactly what i'm saying:"On a computer that has a multiple-core CPU, SQL Server 2005 Express Edition starts a separate scheduler for each core.For example, if you have a dual-core CPU, SQL Server 2005 Express Edition starts two separate schedulers. Both schedulers are available for SQL Server 2005 Express Edition to use. This behavior lets SQL Server 2005 Express Edition do some parallel processing within the limits of the one CPU that the product is using."Its set up to use all four cores, I can see that in the settings. However when a single ASP page is executing which has lots of calls to the database, it all seems to use the one scheduler rather than splitting the load between the four.I've verified this with "select * from sys.dm_os_sys_info". 4 for CPU Count and scheduler count.
  9. Adriaan New Member

    The other link tells you that per query, SQL Express will only employ one CPU. There are limits to what MS gives away for free ...
  10. jazzy639 New Member

    SQL Express can use more than one core per query. But I think the problem is i'm trying to run say 100 simple queries. I would expect it to:Run 1st in core12nd in core 23rd in core 34th in core 4then the next query in whatever core/scheduler is freeHowever they all seem to queue up behind the one scheduler. Are you saying a different version of MSSQL will do the above?
  11. Adriaan New Member

    SQL is a procedural language, as in: it will execute your commands one after the other. This keeps things predictable, and ensures that your data doesn't get messed up.
    If you want to fire concurrent queries, you'll need to do this from the client application.
  12. jazzy639 New Member

    Im not sending a list of queries to the DB like "SELECT id FROM test; SELECT name FROM table2;..."Im opening up recordsets one after the other. This is just like there being say 50 users on the website, and each opening a page that sends one query. This page just so happens to send say 50 queries.I would expect MSSQL to make use of all cores?When will it use additional cores?
  13. Adriaan New Member

    Your original description was "There's a certain page that runs a lot of queries to the the DB in a loop."
    If you're "opening up recordsets one after the other", that's the same really - unless you're opening recordsets asynchronously (client app does not wait for the results, but continues with next instruction).
  14. jazzy639 New Member

    Ok, thats fair enough.So if we had 4 different users running this page at the same time, we can expect all four cores to be used?
  15. satya Moderator

    Again a set of KBA refers that:
    SQL Server 2005 Express Edition recognizes the additional processors that are available on a computer that has a multiple-core CPU or an HT Technology-enabled CPU.
    • On a computer that has a multiple-core CPU, SQL Server 2005 Express Edition starts a separate scheduler for each core.
    • On a computer that has an HT Technology-enabled CPU, SQL Server 2005 Express Edition starts a separate scheduler for each logical processor.
    For example, if you have a dual-core CPU, SQL Server 2005 Express Edition starts two separate schedulers. Both schedulers are available for SQL Server 2005 Express Edition to use. This behavior lets SQL Server 2005 Express Edition do some parallel processing within the limits of the one CPU that the product is using.
  16. FrankKalis Moderator

    Have a look at this comparison: http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx
    Express can be installed on a multicore machine, however by definition it can only use one CPU at a time.

Share This Page