Processing Decision Tree Example for "Data Mining with SQL Server 2005" Hangs

Discussion in 'microsoft.public.sqlserver.datamining' started by spontello, Nov 14, 2005.

  1. spontello

    spontello Guest

    I purchased the "Data Mining with SQL Server 2005" book and I am attempting to follow along with the Movie Click database example. I have downloaded the MovieClick database and converted it to SQL Server 2005 RTM. I have successfully built the Movie Trees and Movie Bayes models, but I cannot train either model. I am able to process the structure of both models, but any attempt to train results in the the processing hanging without any result for hours. If I eliminate nested case tables to get down to one or two nested case tables everything processes correctly. I have 1 GB of memory available, but when I process msmdsrv is not doing anything. Any ideas?

    Thanks
    - Steve
     
    spontello, Nov 14, 2005
    #1
    1. Advertisements

  2. Hello,

    Could you please update the following configuration settings to the
    specified values?
    Use SQL Server management Studio, connect to Analysis Services 2005,,
    right-click on the server and select Properties, then change the following
    entries to the indicated values. You might need to switch the property
    viewer to Advanced mode (there is a check-box labeled 'Advanced properties'
    in the lower part of the box)

    Configuration entry Default Value Change
    To:
    Thread Pool \process \MaxThreads 64 128
    Thread Pool \process \ concurrency 2 -4
    (minus 4)

    Hope this helps,
    --
    --
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Please do not send email directly to this alias. It is for newsgroup
    purposes only.

    thanks,
    bogdan
     
    Bogdan Crivat [MSFT], Nov 14, 2005
    #2
    1. Advertisements

  3. spontello

    spontello Guest

    Bogdan,
    Thanks for your quick reply. I made the changes you suggested, restarted AS and then tried to train the models. Unfortunately the result was the same as before. The processing progress monitor shows "Reading cases: 0 cases read." and just hangs.

    - Steve
     
    spontello, Nov 14, 2005
    #3
  4. spontello

    jamiemac Guest

    A quick question, how many processors do you have? Also, are they hyperthreaded, etc.?

    Thanks
     
    jamiemac, Nov 14, 2005
    #4
  5. spontello

    spontello Guest

    I have one Pentium M processor running at 1.3 GHz with no hyperthreading and 1 GB RAM. In other words I am running this on a laptop. I have no problem processing the Adventure Works DM models and as I stated previously I can process the example models if I remove most of the nested case references.

    Thanks
    - Steve
     
    spontello, Nov 14, 2005
    #5
  6. spontello

    Jasper Smith Guest

    I have exactly the same issue. Initially the processing would hang at a very
    early stage (Build Processing Schedule - Integer Data = 1526) without
    hitting the SQL data at all. Having made the suggested changes to the Thread
    Pool (which I don't think make any difference - it's the restart of AS I did
    after changing the settings that helped it get further) it gets a bit
    further but now hangs at the Training mining model stage and hangs SSMS.
    After I kill SSMS, restart it and try to process again it hangs back at the
    original stage (but it at least lets me stop it). If I then restart Analysis
    Services it gets further again but hangs at the Training mining model stage.
    Repeat ad nauseum.....

    XP Pro SP2, SQL2005 DEV RTM, P4 3.2 HT enabled (same problem on my non HT
    laptop though)

    I can process the AW DB models with no issues at all and from the traces it
    seems the SQL side of things is fine but it's getting hung up in AS for some
    reason.
     
    Jasper Smith, Nov 15, 2005
    #6
  7. Hello, Steve, Jasper

    Please try doing the changes I mentioned before plus this new one regarding
    the Coordinator Execution Mode:

    CoordinatorExecutionMode -4 -> -16
    Thread Pool \process \MaxThreads 64 -> 128
    Thread Pool \process \ concurrency 2 -> -4

    As Jasper mentioned in his posting, these changes may require restarting the
    server before becoming effective.

    With the three changes above, I got the whole MovieSurvey database to
    process on a single CPU machine.

    Jasper, I hope this will also fix the problem you found. Please let us know
    if it still does not work.


    --
    --
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Please do not send email directly to this alias. It is for newsgroup
    purposes only.

    thanks,
    bogdan
     
    Bogdan Crivat [MSFT], Nov 16, 2005
    #7
  8. spontello

    Jasper Smith Guest

    Hi Bogdan,

    Thanks - that worked :)

    Although BOL has some limited details on some of the configuration options
    available in AS2005 do you know if there is or will be a whitepaper
    surrounding these? In AS2000 there was not a huge amount of "tweaking"
    available on the server side but in AS2005 it seems there's a lot more. I'd
    just like to understand a bit more about what the effect of these
    configuration changes was.
     
    Jasper Smith, Nov 16, 2005
    #8
  9. I don't know yet of any white paper for the configuration options, I'll post
    here as soon as I find out anything about this.

    I can try to explain the changes I suggested.

    The MovieSurvey mining models are having a rather large number of nested
    tables (7). Aggregating the nested tables together with the main table to
    produce cases for model training is a parallel operation, which involves a
    number of parallel tasks proportional with the number of tables. Changing
    the CoordinatorExecutionMode from -4 to -16 is actually allowing the server
    to run simultaneously 16 concurrent tasks per each available CPU (or 16
    overall, in the case of 1 CPU).

    On the 1-cpu machines, the default of 4 concurrent tasks (-4 == 4 per CPU =
    4 overall) was obviously too small.

    --
    --
    --
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Please do not send email directly to this alias. It is for newsgroup
    purposes only.

    thanks,
    bogdan
     
    Bogdan Crivat [MSFT], Nov 16, 2005
    #9
  10. Bogdan,
    Your solution worked for me as well. I share Jasper's desire for more information regarding AS 2005 configuration options. Thank you very much for your help. Now on to learning more about data mining in SQL Server 2005...

    - Steve
     
    anonymous_user, Nov 16, 2005
    #10
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.