Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    May 2009
    Posts
    56

    Unanswered: How to get SS2005 to use all cores

    Hi, I have SQL Server 2005 on a box with one physical processor but 2 core. However, I am running a CPU intensive query, and it is only using one of the cores, not both. How do I set it up to use both cores at once to speed up the query? I am the only person who accesses this box, so normally multiple queries aren't running at once. Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    First, run this and check the run_value is 0 or 2:
    Code:
    sp_configure 'max degree of parallelism'
    Then check that there is not an OPTION (MAXDOP 1) in the query.

    If both these checks come up clean then SQL Server is using a single core because there would be no advantage to parallel processing or because it cannot (many operations are serial only).

    It is very, very unlikely your query is CPU bound if you are the only user. Post the query and execution plan and we'll go on from there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Always remember this about parallelism. It takes one woman 9 months to make one baby. But no matter how hard you try, you can not get 9 women to make 1 baby in 1 month.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by MCrowley View Post
    Always remember this about parallelism. It takes one woman 9 months to make one baby. But no matter how hard you try, you can not get 9 women to make 1 baby in 1 month.
    Quoted for truth and funnies
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I picked it up from a professor at UMass who does a lot of computational/supercomputer work.

  6. #6
    Join Date
    May 2009
    Posts
    56
    Quote Originally Posted by MCrowley View Post
    Always remember this about parallelism. It takes one woman 9 months to make one baby. But no matter how hard you try, you can not get 9 women to make 1 baby in 1 month.
    Ha I like this.

    But back to the question. Ran this
    Code:
    sp_configure 'max degree of parallelism'.
    Min = 0 Max = 64 config_value = 0 run_value= 0

    The query is below:

    select directions, dbo.fDosagePerDay(directions) DosesPerDay ,sum (cnt) Cnt
    into dbo.adhocDir
    from dbo.adhocTopDir
    group by sig, dbo.fDosagePerDay(directions)

    The Directions are directions for prescriptions. It is free text, varchar(255) The function is used to determine how much of it they are taking.

    I can understand that maybe this query won't benefit if it used 2 processes, but I ran another, similar query while this was running and the other core was still idle. I read somewhere that I need to split my database file into 1 one file per core, but I didn't know if that is true or not.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I have heard about using as many files in tempdb as you have cores, but I have not heard that about user databases.

    How complex is dbo.fDosagePerDay? I am betting if you do not have the function in the query (perhaps group by some other column), the query will speed up considerably. SQL Server is not known for its text handling capabilities.

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Are there licensing issues here?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    May 2009
    Posts
    56
    Ok, I removed dbo.fDosagePerDay from the group by line. A little faster but still long.

    I just ran a query today and noticed it was using both cores. So maybe SQL Server, just doesn't think using two cores would speed up the query. But still surprised it wasn't using both cores when I ran 2 queries.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tomiboy59 View Post
    select directions, dbo.fDosagePerDay(directions) DosesPerDay ,sum (cnt) Cnt
    into dbo.adhocDir
    from dbo.adhocTopDir
    group by sig, dbo.fDosagePerDay(directions)
    Did you copy and paste that? Because that query would not run.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    May 2009
    Posts
    56
    I did not copy and paste. Where it says sig, it should say directions. I changed the column names to make them easier to understand for this post. So like below.

    select directions, dbo.fDosagePerDay(directions) DosesPerDay ,sum (cnt) Cnt
    into dbo.adhocDir
    from dbo.adhocTopDir
    group by directions, dbo.fDosagePerDay(directions)

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depending on the cardinality of directions, this might run more like the sort of time you observed in post 13
    Code:
    SELECT  directions
          , dbo.fDosagePerDay(directions)   AS DosesPerDay 
          , SUM (cnt)                       AS Cnt
    INTO dbo.adhocDir
    FROM 
            (
                SELECT  directions
                      , SUM (cnt)       AS Cnt
                FROM    dbo.adhocTopDir
                GROUP BY 
                        directions
            ) AS grouped_directions
    GROUP BY 
        directions
      , dbo.fDosagePerDay(directions)
    If we are looking now at the speed of the query rather than worrying about cores then we would need to know a few more things:
    DDL for table
    Number of rows in table
    Number of rows returned by the query
    Query Plan
    Output of STATISTICS IO
    Content of the UDF
    Memory and IO config would be interesting (I'm guessing low since this is just a dual core system)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    May 2009
    Posts
    56
    It takes about 47 minutes to run. But I can tell you now it runs so slow cause of the function. Cause it took 20minutes to run when the function was half the size. The function is just ineffecient, but there really is no way to optimize it, AFAIK. But I'll give you the information you want, cause maybe I can still learn something new. But I'd still like to know that the server it utilizing the cores correctly. Even for future reference if I ever upgrade to a more powerful server.

    Number of rows in table 8.7mil
    Number of rows returned by the query 6.7mil
    Query Plan --I'll post this in another post.
    Output of STATISTICS IO --Not sure how to get this.
    Content of the UDF
    --Basically it says, if directions like '%daily%' then dosage = 'Daily' but there are about 20 different if statements, each with an avg about 20 different conditions trying to match the dosage.

    Memory and IO config would be interesting (I'm guessing low since this is just a dual core system) 2 Gig memory. 80 GB HDD contains OS and log files. a 500GB HDD contains data files.

    DDL for table
    CREATE TABLE [dbo].[adhocTopDir](
    [NDC] [varchar](11) NULL,
    [Directions] [varchar](254) NULL,
    [cnt] [float] NULL
    ) ON [PRIMARY]

  14. #14
    Join Date
    May 2009
    Posts
    56

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Syntax for STATISTICS IO
    Code:
    SET STATISTICS IO ON
    immediately before your query.
    Code:
    SET STATISTICS IO OFF
    immediately after your query.
    Report the results.

    The image of the query plan is incomplete although I am very surprised to see what we can see. Try saving the plan as a .sqlplan file and upload it.

    47 minutes is incredibly slow for such a small table.

    I am 99% sure we can optimise your UDF - please post it in its entirety.
    Another option is a computed column to derive dosage - it depends on how data is loaded on whether or not this would be a good solution.

    A covering index would not help your query so we can forget that.

    Do you have defragmentation routines running?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •