Number2 Blog - myITforum

BACKGROUND

We've recently migrated from SMS 2003 to CM 2007 SP2 with R2, and something we noticed is that DDR processing for certain things seems to be slower in CM than it was in SMS 2003, 30-40/min instead of 200-300.  Also, we noticed that for certain full inventory MIFs,  processing was horribly slow (4-5/min instead of 800-900/min)  Well, that seems counterintuitive, so we took some time to try to figure out why.  Was it a configuration issue on our part?  A sizing issue? Was it a hardware problem?  Was there some hiccup along the way that is making things go slower or did the product team have to design in some extra checks that slow things down?

Well, it wasn't the CM team designing extra bulk into it.  And we did not have a hardware issue.  Our servers were configured just fine too.  We have more than enough hardware too, 3.2GHz dual-quad core Xeons, 32 GB of RAM, 2 RAID controllers with 1GB of RAM and 30 spindles in RAID 10.  Aside from going i7 with no front-side bus and DDR3, you don't get much better than this without going massively parallel and breaking the bank.

THE SOLUTION (SHORT VERSION)

As it turns out, there were a couple of issues at play. 

1. There's a KB article our CSS guy gave us which, when followed, speeds up DDR processing for system group discovery (see KB949957).  
2. The other issue, I tracked down to a SQL slowdown which could be fixed by adding an index.
   
   CREATE NONCLUSTERED INDEX _My_INSTALLED_SOFTWARE_DATA_CodeHashAndID
   ON [dbo].[INSTALLED_SOFTWARE_DATA] ([SoftwareCode00],[SoftwarePropertiesHash00],[MachineID])

WARNING:  Creating an index on your own without direction from Microsoft CSS or without an associated KB article is neither supported nor recommended by Microsoft.  So, just think about that before you start whipping out indexes onto production tables.  In my case, I've been doing this for years, I know the ramifications, I have a back-out plan, and this was done in a scalability lab under load.  If you're going to attempt this, I'd recommend getting guidance from Microsoft first and testing it out in a lab.

THE SOLUTION (LONG VERSION)

I noticed that the server was processing very little (just a few DDRs per minute and a few MIFs per minute) and the disks were almost completely idle, memory usage was minimal (20GB free out of 32GB) but the CPU usage was still pretty high (60-70%) and most of it was SQL.  For as little as it was doing, that's too high.  Clearly something isn't being very efficient.  So I opened up SQL Profiler for a couple minutes to figure out what SQL was doing. I noticed that the things taking the most time were all the same.  2 seconds for one simple statement just checking for the existence of an INSTALLED_SOFTWARE_DATA record, that should take 2 milliseconds, not 2000.

So, I took the SQL code and put it into SQL Management Studio to check the execution plan.  Management Studio recommended adding an index that would improve efficiency by 99%.

CREATE NONCLUSTERED INDEX _My_INSTALLED_SOFTWARE_DATA_CodeHashAndID
ON [dbo].[INSTALLED_SOFTWARE_DATA] ([SoftwareCode00],[SoftwarePropertiesHash00],[MachineID])

Once I added that index, HINV went up over 600 MIFs/min like I suspected it might and it flew through those MIFs no problem.  Now the server is sitting there idle again (well, 10% CPU anyway).  MIF processing was at 5/min for hours and then went ape once I added the index.  Those statements that took 2000ms before now take 0-2 ms afterwards.  I don't know what made those MIFs especially slow, because we've seen lots of other MIFs just fly so it must be that some MIFs have INSTALLED_SOFTWARE_DATA things in them and some don't.  Those that do are slow.

SUMMARY

1. There's a KB article our CSS guy pointed us to which, when followed, speeds up DDR processing for system group discovery (see KB949957).  
2. The other issue I tracked down to a SQL slowdown which could be fixed by adding an index.
   
   CREATE NONCLUSTERED INDEX _My_INSTALLED_SOFTWARE_DATA_CodeHashAndID
   ON [dbo].[INSTALLED_SOFTWARE_DATA] ([SoftwareCode00],[SoftwarePropertiesHash00],[MachineID])

WARNING:  Creating an index on your own without direction from Microsoft CSS or without an associated KB article is neither supported nor recommended by Microsoft.  So, just think about that before you start whipping out indexes onto production tables.  In my case, I've been doing this for years, I know the ramifications, I have a back-out plan, and this was all done in a scalability lab under load.  If you're going to attempt this, I'd recommend getting guidance from Microsoft first and testing it out in a lab.

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