locked
Create Deployment Package with data times out RRS feed

  • Question

  • All,

    After a recent server hickup - the ability to perform a package export with data seems to have evaporated. what used to take all of a couple of minutes now times out after 15. - only 150k records in 5 tables

    Export without data works just fine

    I suspect something is amiss in the data or something didn't "cleanup" after the hickup because the w3p helper process is taking off and consuming tons of CPU while the databaase server sits Idle.

    Diagnostics is not very helpful -  See below for the error associated with the setting of "All" to the trace file:

    MDS Error: 0 : System.Threading.ThreadAbortException: Thread was being aborted.
       at System.String.EqualsHelper(String strA, String strB)
       at Microsoft.MasterDataServices.Core.BusinessLogic.Member.<>c__DisplayClass17.<CreateEntityMembersFromDataTable>b__14(Member e)
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
       at Microsoft.MasterDataServices.Core.BusinessLogic.Member.CreateEntityMembersFromDataTable(DataTable entityMembers, Identifier entityId, RequestContext context)
       at Microsoft.MasterDataServices.Core.BusinessLogic.Member.GetMembers(EntityMembers entityMembers, RequestContext context)
       at Microsoft.MasterDataServices.Core.BusinessLogic.Model.GetMembers(ModelMembers ModelMembers, Boolean IncludeLeafMembers, Boolean IncludeConsolidatedMembers, Boolean IncludeCollectionMembers, RequestContext context, OperationResult& results)
       at Microsoft.MasterDataServices.Services.Service.ModelMembersGet(ModelMembersGetRequest request)
        DateTime=2011-08-23T21:07:56.1715435Z
    MDS Error: 0 : Request timed out.
        DateTime=2011-08-23T21:07:56.1803329Z

    SO - How does one pull apart the problem to solve the issue of the suddenly lengthy Deployment Package build? Yes = we've got all the settings turned up for 1 GB files and uploads, timeout to 15 minutes and tracing everything - this is a case of how to take apart the problem to discover and correct the error. Executing the stored procedures that this process executes seems like a great first step - if the process can be defined to allow stepwise execution.

    First Question is : What is "ModelMemberGet" Request (Stored Proc, function, ?) and what parameters need be set to execute that function in a debug mode in SSMS?

    Possible secondary issues: Question is Probablility of occurance(?) and hints as to how to identify and (bonus points awarded) solutions.

    1) Cache or working set not cleared out

    2) data that is creating a loop or deadlock situation

    3) Permissions error

    4) Locked transaction that is blocking

    Ideas?

    Tony


    Richard A. "Tony" Eckel Rochester, NY
    Wednesday, August 24, 2011 3:10 AM

Answers

  • Hi Tony

    1) modelmembersget

     

    please refer to http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.services.service.modelmembersget.aspx for modelmembersget API call

     

     

    //Sample C# Code 
    private void ModelMembersGet()
    {
        // Create the request and response objects 
        ModelMembersGetRequest request = new ModelMembersGetRequest();
        ModelMembersGetResponse response = new ModelMembersGetResponse();
    
        request.ModelsGetCriteria.Models.Add(GetId(null, "Product"));
        request.ModelsGetCriteria.Versions.Add(GetId(null, "Version_1"));
        request.ModelsGetCriteria.Entities.Add(GetId(null, "Product"));
        request.ModelsResultCriteria.IncludeLeafMembers = true;
        request.ModelsResultCriteria.IncludeConsolidatedMembers = true;
        request.ModelsResultCriteria.IncludeCollectionMembers = true;
    
        // Get results for Get
        response = serviceClient.ModelMembersGet(request);
        if (response.ModelMembers.Count == 0)
            throw new Exception(string.Format("This Model does not have any members. '{0}'.", "ModelMembersGet"));
    } 

     

    and i think it is calling the following stored procedure (not checked yet)

    you can call it from SSMS with

     

    declare @p2 xml
    set @p2=convert(xml,N'<MetadataSearchCriteria xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Models><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></Models><Versions><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></Versions><VersionFlags/><DerivedHierarchies><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></DerivedHierarchies><DerivedHierarchyLevels/><ExplicitHierarchies/><Entities><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></Entities><MemberTypes/><Attributes/><AttributeGroups><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></AttributeGroups><SearchOption>BothUserDefinedAndSystemObjects</SearchOption></MetadataSearchCriteria>')
    declare @p3 xml
    set @p3=convert(xml,N'<MetadataResultOptions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Models>Identifiers</Models><Versions>None</Versions><VersionFlags>None</VersionFlags><DerivedHierarchies>None</DerivedHierarchies><DerivedHierarchyLevels>None</DerivedHierarchyLevels><ExplicitHierarchies>None</ExplicitHierarchies><Entities>None</Entities><MemberTypes>None</MemberTypes><Attributes>None</Attributes><AttributeGroups>None</AttributeGroups></MetadataResultOptions>')
    exec mdm.udpMetadataModelGetXML @User_ID=1,@SearchCriteria=@p2,@ResultCriteria=@p3

     

    (to get that , I simply used SQL server profiler (part of SSMS) and I've run my "MDSManager" tool because it is loading model list with API at the application start)

    result from this stored proc will be xml data, which is used by API call

    Regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

    Tuesday, September 13, 2011 3:35 PM
  • Hi Tony

    yes it seems to be a data issue

    maybe a member code /name with reserved keywords ?

    http://sqlblog.com/blogs/mds_team/archive/2010/11/03/reserved-words.aspx

    regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Wednesday, September 21, 2011 9:52 AM
  • All,

    Just an update:

    This issue is confirmed to be a limitation of the MDM loading process:

    1) Total load consists of 180k member records in 6 entities. The smallest with 3 members, the largest with 178K

    2) Total leaf records consist of 1709k attributes, 70% which were domain attributes, 30% were leaf attributes

    3) of the domain attributes, 2/7 are associated with a derived hierarchy

    This NEVER finished with the standard process: always crashed>

    Did the alternative and loaded the empty model, then dumped the members and attributes to the target system staging tables.

     

    The batch finished after Nine Hours and fifty-two minutes (9:52) with 1400 errors (0.08%)

    There were no special hierarchies, no special validation rules to run, nothing - it still took 10 hours!

    As a comparison, the sql to load the staging tables from the source server as a linked database took 47 seconds.

    So it was NOT a Data issue -

     

    Tony


    Richard A. "Tony" Eckel Rochester, NY
    Tuesday, October 18, 2011 7:15 PM

All replies

  • Given the level of interest - let me pose a more technical and focused question:

    What is the actual strored procedure that is executed where this failure occurs?

    My interest is to execute in SSMS to trap the error.

    Tony


    Richard A. "Tony" Eckel Rochester, NY
    Thursday, August 25, 2011 6:08 PM
  • Hi Tony

    1) modelmembersget

     

    please refer to http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.services.service.modelmembersget.aspx for modelmembersget API call

     

     

    //Sample C# Code 
    private void ModelMembersGet()
    {
        // Create the request and response objects 
        ModelMembersGetRequest request = new ModelMembersGetRequest();
        ModelMembersGetResponse response = new ModelMembersGetResponse();
    
        request.ModelsGetCriteria.Models.Add(GetId(null, "Product"));
        request.ModelsGetCriteria.Versions.Add(GetId(null, "Version_1"));
        request.ModelsGetCriteria.Entities.Add(GetId(null, "Product"));
        request.ModelsResultCriteria.IncludeLeafMembers = true;
        request.ModelsResultCriteria.IncludeConsolidatedMembers = true;
        request.ModelsResultCriteria.IncludeCollectionMembers = true;
    
        // Get results for Get
        response = serviceClient.ModelMembersGet(request);
        if (response.ModelMembers.Count == 0)
            throw new Exception(string.Format("This Model does not have any members. '{0}'.", "ModelMembersGet"));
    } 

     

    and i think it is calling the following stored procedure (not checked yet)

    you can call it from SSMS with

     

    declare @p2 xml
    set @p2=convert(xml,N'<MetadataSearchCriteria xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Models><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></Models><Versions><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></Versions><VersionFlags/><DerivedHierarchies><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></DerivedHierarchies><DerivedHierarchyLevels/><ExplicitHierarchies/><Entities><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></Entities><MemberTypes/><Attributes/><AttributeGroups><Identifier><Id>0</Id><Muid>00000000-0000-0000-0000-000000000000</Muid><Type>Unknown</Type><Subtype xsi:nil="true"/><SecurityPermission>NotSpecified</SecurityPermission></Identifier></AttributeGroups><SearchOption>BothUserDefinedAndSystemObjects</SearchOption></MetadataSearchCriteria>')
    declare @p3 xml
    set @p3=convert(xml,N'<MetadataResultOptions xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Models>Identifiers</Models><Versions>None</Versions><VersionFlags>None</VersionFlags><DerivedHierarchies>None</DerivedHierarchies><DerivedHierarchyLevels>None</DerivedHierarchyLevels><ExplicitHierarchies>None</ExplicitHierarchies><Entities>None</Entities><MemberTypes>None</MemberTypes><Attributes>None</Attributes><AttributeGroups>None</AttributeGroups></MetadataResultOptions>')
    exec mdm.udpMetadataModelGetXML @User_ID=1,@SearchCriteria=@p2,@ResultCriteria=@p3

     

    (to get that , I simply used SQL server profiler (part of SSMS) and I've run my "MDSManager" tool because it is loading model list with API at the application start)

    result from this stored proc will be xml data, which is used by API call

    Regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".

    Tuesday, September 13, 2011 3:35 PM
  • Xavier - and anyone else who's listening.

    Well, that didn't help much, but it got me looking deeper:

    1) I know that it is running to completion because for five minutes, the web service w3wp.exe is chewing more than 95% of one CPU on the Web server; database server is pretty calm

    2) there are tell tale skips in the CPU demand correlated with short bursts of DB activity that would, I assume, coorespond with the shift from one entity to the next.

    3) I extracted on of the queries that feed the XML using profiler and ran it in a query window in SSMS and it only took a1.5 minutes to run to completetion with the bulk of the records.

    Thus - I assume the data actually gets shipped to the Web server for package creation, but somehow never signals completion to the launching page "Admin/Wizards/WizardDeployment/Pages/CreateParametersPage.aspx". That page just sits and spins for ever.

    After the job completes on the WEB, one can press the Finish button and get a dialog about downloading, but will always get a zero length file. If one presses the Cancel button, an error shows up in the log 1309 with a time out.

    If one closes the parameter page int eh middle of the run, the run continues. If one then re-opens the wizard, the wizard will stay blank until the run complese, then the Model Deployment Wizard proceeds normally.

    If no data is requested, a perfectly good Model file is produced is seconds, but the create parameter page stays in the "Package Created Successfully" state with the Finish and Cancel buttons on; an endless loop of Finish...

    This happens on Two servers and two different instances of MDS -

    Elevating the MDM-User to system admin status did not affect the results.

    Looking for some help here from the Microsoft MDS team on how this is supposed to operate, what could break this feature, Where I have to look for answers.

    Systems Notes: these are CU9 installations on SQL 2008 R2, Win 2008 R2. The web - log - data - temp directories are all distributed. Hardware is a 4.5? ESX Vmware installation that sings....

    Little help her?

    Tony

     

     


    Richard A. "Tony" Eckel Rochester, NY
    • Edited by Tony Eckel Tuesday, September 20, 2011 11:31 PM
    Tuesday, September 20, 2011 11:29 PM
  • Okay - Now the kicker:

    Downloaded and installed the Employee sample pkg, and was able to export it just fine.

    It's not the installation or the configuration: It is the data or the model -

    What in a model or data could do this?

     

    Tony

     


    Richard A. "Tony" Eckel Rochester, NY
    Wednesday, September 21, 2011 12:12 AM
  • Hi Tony

    yes it seems to be a data issue

    maybe a member code /name with reserved keywords ?

    http://sqlblog.com/blogs/mds_team/archive/2010/11/03/reserved-words.aspx

    regards,



    Xavier Averbouch
    Microsoft Community Contributor
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Wednesday, September 21, 2011 9:52 AM
  • All,

    Just an update:

    This issue is confirmed to be a limitation of the MDM loading process:

    1) Total load consists of 180k member records in 6 entities. The smallest with 3 members, the largest with 178K

    2) Total leaf records consist of 1709k attributes, 70% which were domain attributes, 30% were leaf attributes

    3) of the domain attributes, 2/7 are associated with a derived hierarchy

    This NEVER finished with the standard process: always crashed>

    Did the alternative and loaded the empty model, then dumped the members and attributes to the target system staging tables.

     

    The batch finished after Nine Hours and fifty-two minutes (9:52) with 1400 errors (0.08%)

    There were no special hierarchies, no special validation rules to run, nothing - it still took 10 hours!

    As a comparison, the sql to load the staging tables from the source server as a linked database took 47 seconds.

    So it was NOT a Data issue -

     

    Tony


    Richard A. "Tony" Eckel Rochester, NY
    Tuesday, October 18, 2011 7:15 PM