Keeping Historical EPM data

Apr 16, 2012 at 2:47 PM

I hope this is the right place to ask this question.

I have been running EPM since May 2011. My database is currently massive, with PolicyHistoryDetail taking up most of the space. It is 130GB in size. The PolicyDashboard report errors out, saying that the user cancelled the request. In actuality, it just times out.

The first SQL it runs is:

declare @ServerGroupName nvarchar(13)

select @ServerGroupName=N'SQL 2005 PROD'

SELECT CASE  WHEN EXISTS  (SELECT *  FROM policy.v_PolicyHistory_LastEvaluation iple  WHERE iple.EvaluatedServer = ple.EvaluatedServer  AND iple.PolicyResult = 'fail')  THEN 'Out of Compliance' ELSE 'In Compliance'  END AS InstanceStatus , EvaluatedServer

FROM policy.v_PolicyHistory ple

WHERE (EvaluatedServer IN (SELECT server_name FROM policy.pfn_ServerGroupInstances(@ServerGroupName))OR @ServerGroupName = '')

GROUP BY EvaluatedServer

When I run this SQL manually, it takes 55 minutes to return, hence the time out on the report.

Is it necessary for any reason to keep all the info in the PolicyHistoryDetail table, or would keeping the last 30 days be sufficient. NOTE: There is no business pressure to run EPM. I am doing it to keep ensure that our SQL Server instances are built uniformly and no security guidelines are violated. I also use it for monitoring, to a very lesser degree.

I guess what I am trying to ask is whether there are any information in these older rows that is needed should I run a report today?

Thanks for the help so far with other issues

Coordinator
Apr 16, 2012 at 5:29 PM
Edited Apr 17, 2012 at 11:54 AM

Hi Dawid,

By chance is your MDW database on 2008+? If so, have you tried compressing the table and seeing if that helps you (space-wise) at all? Also, since this project is a framework, you are free to roll your own cleanup solution as you see fit. I'll see if I can't develop some sort of cleanup solution and get it added to the project code for future version. Hopefully that will help out any future implementers of EPMF.

In the meantime, in regards to your history retention, it's really up to you how long you feel you need to keep that data. If you're using Enterprise Edition, you may want to partition the history table and break off the older data to its own filegroup/disk. Or maybe we just need to modify the report query inside EPMF to only pull X amount of months back of historical data rather than try to load the whole deal.

Developer
Apr 27, 2012 at 4:37 PM

 

David,

To add on to the recommendation of compressing the table (either row or page), if you decide to create a job to run on a recurring basis cleaning the old entries in PolicyHisytoryDetail table please ensure that you also remove the corresponding entries in the PolicyHistory table. These two tables are connected by the PolicyHistoryID and by deleting entries in both the tables you ensure you are not orphaning entries in PolicyHistory after removing their PolicyHistoryDetail. To find out if you already have orphans you can run the script below:

SELECT  * 

FROM policy.PolicyHistory ph

WHERE ph.PolicyHistoryID NOT  IN

(SELECT phd.PolicyHistoryID FROM policy.PolicyHistoryDetail phd)

Hope this helps.