SMS 2003 SP3 Add/Remove Programs reports may cause a CPU Spike and/or SQL Timeout.

9/17/2007

This fix is now ready and can be obtained by contacting CSS and requesting this fix.

For details please review KB 939872

Updated on 7/18/2007:

A fix is being developed for this issue and is expected to be available in the first half of August. A more precise date is not available as final development and testing need to complete.

Once this is available this issue will be documented and tracked by the KB article number 939872.

  < Previous Post >

Under current investigation is an issue where SMS 2003 SP3 reports related to the Add/Remove Programs data (ARP) may timeout.  This has been associated with SQL spiking the CPU for an extended time when these reports are executed.

At this time investigation points to a limitation in WMI whereby a 32 bit client’s data is being duplicated into both the 32 and 64 bit ARP classes. 

Symptoms:

When executing a report which leverages the Add/Remove Programs data (ARP) in SMS 2003 SP3 you may experience:

  • ASP errors similar to           Active Server Pages error 'ASP 0113’
  • The report execution may timeout and SQL may spike consuming the CPU.

This has been identified in the following two SP3 reports but may not be limited to these two reports:

  • Computers with specific software registered in Add Remove Programs
  • Software registered in Add Remove Programs on a specific computer

Details:

In SP3 a new view has been introduced, v_ Add_Remove_Programs, for the purpose of creating a union between the 32 bit ARP and 64 bit ARP data.  The noted reports were then modified to leverage this new view.  Prior to SP3 the v_gs_Add_Remove_Programs view was called and this view remains available for use – limited to only the  32 bit ARP data.

The problem is introduced on the clients due to a limitation in WMI where the clients reported data is duplicated in both the 32 and 64 bit ARP classes.  This issue can affect both 32 and 64 bit clients however for Windows 2003 Sp1 64 bit systems there is a hotfix available to correct the WMI limitation.  That fix is detailed in KB 923850.  This fix is not applicable for 32bit platforms.

When the view is called during report execution the duplicated data pulled from the union of 32 and 64 bit ARP can result in a larger than anticipated data set to be evaluated which results in the timeout.

Workarounds:

To work around this issue until a full resolution can be provided it is recommended to leverage the SMS 2003 SP2 report versions for the affected reports.    To that end a copy of the exported SP2 Reports are provided below which can be copied into a .MOF file and imported to your SP3 Site.   The two reports in this MOF have been renamed to preface the reports with TEMP_SP2 for easy identification and will not replace the existing reports from SP3. 

__________________________________

// *********************************************************************************

//

//                             Created by SMS Export object wizard

//

//                             Friday, June 29, 2007 created

//

//                             File Name: arp_reports.mof

//

// Comments :

//

// Export of 2 Add_Remove Programs reports from an SP2 site. 

// For temporary use until such time as post-SP3 issues with these same reports are

//  resolved.  Will not contain x64 Add_Remove Programs data.

//

// *********************************************************************************

// ***** Class : SMS_Report *****

[SecurityVerbs(140551)]

instance of SMS_Report

{

                Category = "Software - Companies and Products";

                Comment = "Displays a summary of all computers with specific software registered in Add Remove Programs";

                DrillThroughColumns = {};

                GraphXCol = 1;

                GraphYCol = 2;

                MachineDetail = FALSE;

                MachineSource = FALSE;

                Name = "TEMP_SP2 - Computers with specific software registered in Add Remove Programs";

                NumPrompts = 2;

                RefreshInterval = 0;

                ReportParams = {

instance of SMS_ReportParameter

{

                AllowEmpty = FALSE;

                DefaultValue = "";

                PromptText = "Software Title";

                SampleValueSQL = "begin

\n if (@__filterwildcard = '')

\n  Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS order by DisplayName0

\n else

\n  Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS

\n  WHERE DisplayName0 like @__filterwildcard

\n  order by DisplayName0

\nend";

                VariableName = "displayname";

},

instance of SMS_ReportParameter

{

                AllowEmpty = FALSE;

                DefaultValue = "";

                PromptText = "Collection ID";

                SampleValueSQL = "begin

\n if (@__filterwildcard = '')

\n  select CollectionID, Name from v_Collection order by Name

\n else

\n  select CollectionID, Name from v_Collection

\n  WHERE CollectionID like @__filterwildcard

\n  order by Name

\nend";

                VariableName = "CollID";

}};

                SecurityKey = "";

                SQLQuery = "Select sys.Netbios_Name0, fcm.SiteCode,  sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0

\nFROM v_R_System sys

\nJOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID

\nJOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

\nWHERE DisplayName0 = @displayname and fcm.CollectionID=@CollID";

                StatusMessageDetailSource = FALSE;

};

// ***** End *****

// ***** Class : SMS_Report *****

[SecurityVerbs(140551)]

instance of SMS_Report

{

                Category = "Software - Companies and Products";

                Comment = "Displays a summary of the software installed on a specific computer that is registered in Add Remove Programs";

                GraphCaption = "";

                GraphXCol = 1;

                GraphYCol = 2;

                MachineDetail = TRUE;

                MachineSource = FALSE;

                Name = "TEMP_SP2 - Software registered in Add Remove Programs on a specific computer";

                NumPrompts = 1;

                RefreshInterval = 0;

                ReportParams = {

instance of SMS_ReportParameter

{

                AllowEmpty = FALSE;

                DefaultValue = "";

                PromptText = "Computer Name";

                SampleValueSQL = "begin

\n if (@__filterwildcard = '')

\n  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0

\n else

\n  SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1

\n  and SYS.Netbios_Name0 like @__filterwildcard

\n  ORDER By SYS.Netbios_Name0

\nend";

                VariableName = "computername";

}};

                SecurityKey = "";

                SQLQuery = "Select v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS.Publisher0, v_GS_ADD_REMOVE_PROGRAMS.Version0

\nFROM v_GS_ADD_REMOVE_PROGRAMS

\nJOIN  v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID

\nWHERE v_R_System.Netbios_Name0 = @computername";

                StatusMessageDetailSource = FALSE;

};

// ***** End *****

__________________________________

As more details become available and when a resolution has been identified this blog will be updated with that information.

Comments

  • Anonymous
    January 01, 2003
    Back in June we blogged about an issue where the SMS 2003 SP3 Add/Remove Programs reports may cause a

  • Anonymous
    January 01, 2003
    Paul was nice enough to post a link to this blog workaround for this problem. I thought I would post

  • Anonymous
    January 01, 2003
    thank you

  • Anonymous
    January 01, 2003
    Now am I glad I found this - since we did Sp3 we have been having lots of unexplained CPU spiking - it is so bad you get 'sql server unavailable' in dataldr.log.... only last night we 'reworked' the add remove programs report code a tiny bit - and this HAS fixed the issue - the report runs faster than ever before - even before Sp3. And no view changes. I'll share below if it is of use.... declare @ResourceId int select @ResourceId=ResourceID from  v_R_System where netbios_name0 = @computername Select v_Add_Remove_Programs.DisplayName0, v_Add_Remove_Programs.Publisher0, v_Add_Remove_Programs.Version0 from  v_Add_Remove_Programs where ResourceID = @ResourceID I have to stress I am no SQL code expert - however I am married to one - we ran the profile tool and the report was busy table scanning - our add_remove_programs table has 2.6million rows (only 16k clients so not huge) It does make me wonder how many other reports in the app are 'inefficient' - we have already had similar issues with MOM 2005 tasks that could easily be made to run 100 times faster.

  • Anonymous
    January 01, 2003
    We recently ran into a problem with SMS Report number 97 that seems to be cropping up from time to time

  • Anonymous
    January 01, 2003
    Are there any updates on the proposed fix for this issue?

  • Anonymous
    January 01, 2003
    We found a problem with SP3 last week and was planning on contacting Premier support about it once the

  • Anonymous
    January 01, 2003
    Are there any updates on the proposed fix for this issue?