Monitoring the status of Planning Server databases
Atualizado em: 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 Apêndice A: Monitorando a integridade do SQL Server. 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