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 Friday, July 17, 2015 6:50 PM

Hi Garth, thanks for your response.

Just to clarify, we are not querying the tables directly. We are querying the view v_AdvertisementInfo. We found that this view used to take about a second to return a SELECT * in SCCM 2008. When we moved to SCCM 2012, this same view took ~30-45 seconds to return a SELECT *. This view uses vSMS_AdvertisementInfo, which uses the views that used to be tables I've listed above.

I realize we may not find an answer here, but I wanted to see if anybody faced a similar problem when moving to SCCM 2012.

I also realize you are not supposed to query the tables directly because the DB can change, and I suppose that is the reason for the views to exist. The thing is that we are querying a view and a simple SELECT * has a serious performance degradation.

So, is the call to vAdvertisementInfo supported? If so, did anybody notice a serious degradation of performance?

Thanks a lot in advance

So, is the call to vAdvertisementInfo supported? If so, did anybody notice a serious degradation of performance?

Yes it supported to query the SQL views.

Again, it might be an RBA issue, the rba feature add some overhead to the queries. Without your queries there is little anyone can do to help you. I'm on a train so it will be a while until I get access to my labs to look at things.

The query would be as simple as SELECT * FROM vAdvertisementInfo, but I guess there are many variables that come with the type of information stored...

Now, you mention a RBA issue. Could you give me a pointer on what is in your mind about this? I realize it may not be the solution, but it would give me a pointer on what to check.

Thanks for the response!