ACS queries speed up


In one previous post Permanent Link to Fighting against ACS queries I mentioned I found a way to speed up ACS queries, but I was not allowed to share my findings with the community until yesterday (thanks Joe). A word of caution what I’m about to blog about requires an ACS database schema change, this schema change is easily reversible and won’t touch your data nevertheless it won’t be supported by PSS.

This speed up applies if:

  1. you’re keeping many partitions (days of data) on line
  2. the average time window selection for your reports is relatively small compared to the number of days kept in the ACS database
  3. your reports are using the CreationTime field as one of the selection parameters

The hack consists in adding a constraint to every closed partition, the constraint mandates the time period contained in the partition. Without this mod the data you’re interested in is searched in every partition, with the mod just the partitions that contain data in the selected time window are searched. This can make a huge difference in query execution time.

The sample statement for a generic partition is similar to the following.

ALTER TABLE [dbo].[dtEvent_074837bc_7b93_4930_a799_7fcb6778819e]  WITH CHECK ADD  CONSTRAINT [CK_dtEvent_074837bc_7b93_4930_a799_7fcb6778819e] CHECK  (([CreationTime]>=’2009-10-30 01:00:25.000′ AND [CreationTime]<=’2009-10-31 01:02:17.000′))

To make it a permanent change you just need to iterate (for example one a day) the partition table for closed partitions and then add such a constraint based on the actual data contained in the partition. Due to the sensitivity of the mod I won’t post the TSQL needed, just drop me a line if you need it.

– Daniele

This posting is provided "AS IS" with no warranties, and confers no rights.

Advertisements
  1. #1 by Mark on October 17, 2012 - 11:43 am

    That drop box link isnt working, can you repost ? thanks

  2. #3 by jemeljanov on July 18, 2012 - 9:39 am

    Hi, I’m also interested in TSQL, please send it to “screamers aatt gmail.com

    Thanks.

    • #4 by Daniele Grandini on July 20, 2012 - 9:00 am

      Hi Sergei, first of all you shall know this fix has been included in System Center 2012 Operations Manager, so if you upgrade to the 2012 version you won’t need it anymore.
      If you’re still with 2007 R2 you can find the SQL statements here: https://www.dropbox.com/s/vtcg51xk8oxlujd/AddConstraints.sql

      Regards
      Daniele

      • #5 by Sergei on July 31, 2012 - 7:26 pm

        I’m still on 2007 R2 cu5, but I have upgrade plans for autumn, maybe sp1 will be also released

  3. #6 by Claus Friis on June 29, 2011 - 11:58 am

    I would like to have a look at the TSQL, could you send it to me, please ?

  1. Upgrading ACS from Operations Manager 2007 to 2012 « Quae Nocent Docent

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: