Hi all

My team developed an application that uses tables from SCCM to gather information to deploy applications into a target. It was developed using SCCM 2008 and lately we had to change to SCCM 2012.

We are experiencing a very poor performance on 2012. A query that used to take 1 second now takes 45.

Analyzing one of the queries that was taking the longest, we found that the use of the v_AdvertisementInfo view is the one slowing us down. We drilled down on the nested view calls and reached vSMS_AdvertisementInfo after a few levels.

Inside vSMS_AdvertisementInfo, the code utilizes what in 2008 used to be the tables 

• ProgramOffers
• SMSPackages
• Collections
• PkgPrograms

Which are views now in 2012, that consolidate two tables for each of them, one with a suffix _L and another with the suffix _G.

To experiment, we changed the calls by adding _G (e.g. ProgramOffers_G instead of ProgramOffers) to use the actual tables instead of the views, with results returning in 1 second.

My theory is that since the view contents are gathered dynamically, and since the views do not have indexes, after consolidating _G and _L with a view, the rest of the calls result in "table scans".

However, the SCCM GUI does not seem affected by this table split, I'm guessing they use the database differently.

Has anybody had a similar experience? Does anybody have a suggestion on how to fix this slowdown?

Thanks a lot in advance

Javier

• Edited by Javier Carvajal 12 hours 58 minutes ago

Have you implemented a database maintenance plan that includes both reindexing the db as well as rebuilding statistics?

it has an IT-issued maintenance plan that does that and some other stuff.

Correct me if I'm wrong, but in my mind, if this were a problem of indexing, it would not have seen an improvement when using the tables directly, right?