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

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

jarviand — Fri, 06 Jul 2007 10:33:30 GMT

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.

SMS 2003 - Troubleshooting - Post SP3 SQL Spike Workaround

Logs of an SMS Administrator at myITforum.com — Fri, 06 Jul 2007 21:26:42 GMT

Paul was nice enough to post a link to this blog workaround for this problem. I thought I would post

Problem with Report 97 after upgrading to SP3

Chris Stauffer at myITForum.com — Mon, 09 Jul 2007 18:17:51 GMT

We found a problem with SP3 last week and was planning on contacting Premier support about it once the

SMS SP3, Report 97, and Ron Crumbaker's Web tool...OH MY!

Sean McHugh at myITforum.com — Mon, 09 Jul 2007 22:56:26 GMT

We recently ran into a problem with SMS Report number 97 that seems to be cropping up from time to time

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

BCM4309 — Fri, 07 Sep 2007 21:01:26 GMT

Are there any updates on the proposed fix for this issue?

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

BCM4309 — Fri, 07 Sep 2007 21:02:43 GMT

Are there any updates on the proposed fix for this issue?

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

SMS & MOM — Tue, 18 Sep 2007 15:06:18 GMT

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