Number2 Blog - myITforum

!!!! UPDATE !!!!  WHEN YOU'RE DONE, THIS ARTICLE HAS BEEN UPDATED --> HERE

PROBLEM 

I don't know if this is a function of the massive size of our SMS environment (100-200K clients) or if others are having this problem still, but after following the steps in KB 939872, we're still seeing very poor performance months later when querying the SQL view dbo.v_Add_Remove_Programs and the cost of querying it is just plain hideous (Cost > 38403!)

I've verified that the size of the table has reduced considerably (several GB) so I know the changes outlined in the article have taken affect.  But the performance is just plain aweful for us.  I would assume that if you don't have very many clients this might not be an issue for you, but for us I had to take action.

After looking at the dbo.v_Add_Remove_Programs view, I realized that is the culprit. Looking at the SQL behind that view, 2 things stick right out at me:
   SELECT machineid AS 'ResourceID',
          instancekey AS 'GroupID',
          revisionid,
          agentid,
          timekey AS 'TimeStamp',
          prodid00 AS 'ProdID0',
          displayname00 AS 'DisplayName0',
          installdate00 AS 'InstallDate0',
          publisher00 AS 'Publisher0',
          version00 AS 'Version0'
     FROM dbo.add_remove_programs_data
   UNION ALL
   SELECT arp64.machineid,
          arp64.instancekey,
          arp64.revisionid,
          arp64.agentid,
          arp64.timekey,
          arp64.prodid00,
          arp64.displayname00,
          arp64.installdate00,
          arp64.publisher00,
          arp64.version00
     FROM dbo.add_remove_programs_64_data AS arp64
          LEFT OUTER JOIN dbo.add_remove_programs_data AS arp32
            ON arp64.prodid00 = arp32.prodid00
    WHERE (arp32.prodid00 IS NULL)

The 2 main problems here are both in the second query:

1. Joining the ARP32 to the ARP64 on the non-indexed prodid00 column (slow/inefficient)
2. Filter criteria (in the WHERE clause) looking for IS NULL on the same non-indexed column, but my understanding of indexes tells me that even if it were indexed, the SQL engine wouldn't make use of it because it's a NULL field.  Indexes were made for looking up values, not finding NULLs
   

In english, this view is doing the following:

1. (first query - above the UNION ALL)
   Select all the rows from the ARP (32-bit) table
2. (second query - below the UNION ALL)
   Select all the rows from the ARP (64-bit) table,
3. Select all of the rows from the ARP 32 table where the prodid00 column matches the ARP 64 (prodid00 is a non-indexed column by default, so it will have to scan the ENTIRE table looking for matches),
4. Take all of the ARP64 records left over that didn't have a matching ARP32 (where prodid00 is NULL...again, prodid00 is non-indexed, but even if it were, looking for "IS NULL" wouldn't use it) and return those as the output from the second query
5. (results of the whole query)
   Add (union) the results from 1 and the results from 4 together and output that.

THE SOLUTION (THE CODE FOR THIS ARTICLE IS HERE - right click and SAVE AS - but please read the article first)

First, the usual disclaimer, this solution has not been verified by Microsoft, nor did they give it to me in the first place, and I offer it with no warranty for any particular purpose, the views expressed here are not the views of my employer, etc, blah blah blah.

The solution is made up of 2 things:

1. We need to add an index on that prodid00 column in both the ARP32 and ARP64 tables
   
2. We need to rewrite that second query to be leaner

1) CREATE AN INDEX ON PRODID00

This code will create 2 indexes -- one on the ARP32 table and one on the ARP64 table (replace the stuff in red with your own DB name or desired index names)

USE [<your SMS db>]
GO

CREATE NONCLUSTERED INDEX [_SDE_ARP_ProdID_IDX] ON [dbo].[Add_Remove_Programs_DATA]
(
   [ProdID00] ASC
)

CREATE NONCLUSTERED INDEX [_SDE_ARP_64_ProdID_IDX] ON [dbo].[Add_Remove_Programs_64_DATA]
(
   [ProdID00] ASC
)
GO

2) REWRITE THE SECOND QUERY IN dbo.V_ADD_REMOVE_PROGRAMS

Like I mentioned, having a WHERE clause that looks for IS NULL on a LEFT OUTER JOINed table is just not going to make use of indexes. Let's rewrite it to see if we can do any better:

SELECT machineid AS 'ResourceID',
       instancekey AS 'GroupID',
       revisionid,
       agentid,
       timekey AS 'TimeStamp',
       prodid00 AS 'ProdID0',
       displayname00 AS 'DisplayName0',
       installdate00 AS 'InstallDate0',
       publisher00 AS 'Publisher0',
       version00 AS 'Version0'
  FROM dbo.add_remove_programs_data
UNION ALL
SELECT arp64.machineid,
       arp64.instancekey,
       arp64.revisionid,
       arp64.agentid,
       arp64.timekey,
       arp64.prodid00,
       arp64.displayname00,
       arp64.installdate00,
       arp64.publisher00,
       arp64.version00
  FROM dbo.add_remove_programs_64_data AS arp64
 WHERE arp64.prodid00 NOT IN (select prodid00 from dbo.add_remove_programs_data)

The big improvements were to remove the join from ARP64 to ARP32 and use a subquery instead.  Normally I like to stay away from Field NOT IN (Subquery) but in this case the cost and performance gains were substantial.  It will still use the index to first find all of the ARP64 prodid00 columns that are also in ARP32, and then return what's left.

VERIFY THE GAINS 

We need to verify that the cost and performance gains really materialized, and that we didn't break something else.  It's an important step to test everything that you've written or is already written which uses the tables/views you're working on. 

Consider the following query for testing performance (shows all versions of the adobe flash activeX out there that's not like current version of 9.0.115.0)

DECLARE @DisplayName VARCHAR(128) SET @DisplayName = 'Adobe%Flash%ActiveX'
DECLARE @Version VARCHAR(64) SET @Version = '9.0.115.0'

SELECT DISTINCT DisplayName0 as [Product Name],
                version0 as [Version]
           FROM dbo.v_Add_Remove_Programs
          WHERE displayName0 LIKE @DisplayName
            AND version0 != @Version
       ORDER BY DisplayName0, Version0

         COST    EXECUTION TIME
before   439     81310 ms
after     21       430 ms 

SUMMARY (THE CODE FOR THIS ARTICLE IS HERE - right click and SAVE AS)

So it would appear that perhaps the view definition and default indexes for dbo.v_Add_Remove_Programs and the underlying tables aren't quite ideal, especially for very large shops.  Adding a couple of indexes and modifying the view caused significant cost savings and performance gains in our very large SMS 2003 shop.

If you're going to try something like this, please test, test, test and verify that it's going to do you good and not harm.  In our environment it dropped the cost to 2% of the original and the execution time went from 81 seconds to under 1 second for our test query.  Other queries were equally as improved.

!!!! UPDATE !!!!  WHEN YOU'RE DONE, THIS ARTICLE HAS BEEN UPDATED --> HERE

Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog