• Article

Monitoring the status of Planning Server databases

Updated: 2009-04-30

Database administrators should regularly monitor the health and performance of the Planning Server databases. Index fragmentation monitoring and defragmenting are important parts of Planning Server monitoring, and we have dedicated a separate section to them.

Monitoring the status of Planning databases is approached in two ways. The following section focuses on monitoring from a Planning Server application perspective. The other way involves general Microsoft SQL Server 2005 health monitoring. For information about general SQL Server monitoring, see Appendix A: Monitoring SQL Server health. For more information about monitoring SQL Server computers, see SQL Server Books Online.

Sample queries

The following queries allow you to check your system status and monitor the Planning Server system. Run each query on the PerformancePoint Planning application database.

Current queue items, including all items that are scheduled to run in the future

select * from asyncworkitems with (NOLOCK)
where methodname = 'AsyncSubmit' ---and itemcompletionstatus = 4
and (itemstartDateTime is null or itemenddatetime is null)

Current queue items, excluding all items that are scheduled to run

select * from asyncworkitems with (NOLOCK)
where methodname = 'AsyncSubmit' ---and itemcompletionstatus = 4
and (itemstartDateTime is null or itemenddatetime is null)
and scheduleddatetime > getutcdate()

Items processed last hour

select count(*) as [Count], avg(datediff(second, itemstartdatetime, itemenddatetime)) as [Avg],max(datediff(second, itemstartdatetime, itemenddatetime)) as [Max], min(datediff(second, itemstartdatetime, itemenddatetime)) as [Min] from asyncworkitems with (NOLOCK)
where methodname = 'AsyncSubmit'
and (itemstartdatetime is not null and itemenddatetime is not null)
and dateadd(minute, 60, itemenddatetime) > getutcdate()

Items submitted last hour

select count(*) as [Count]
from asyncworkitems with (NOLOCK)
where methodname = 'AsyncSubmit'
and dateadd(minute, 60, submissiondatetime) > getutcdate()

select top(10) datediff(second, itemstartdatetime, itemenddatetime), datediff(second, submissiondatetime, itemenddatetime) from asyncworkitems with (NOLOCK)
where methodname = 'AsyncSubmit'
order by itemenddatetime desc

How long until the next cube processing time

select datediff(second, getutcdate(), scheduledDatetime)  from asyncworkitems where methodname like 'asyncprocess%' and itemenddatetime is null and itemstartdatetime is null

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable content for PerformancePoint Planning Server.

See Also