power Monitoring Expert - System Log Database is missing the index for Diagnostic table

Search FAQs

You are currently viewing the content available in Vietnam. If you are looking for information for another region, please select the correct country from the top-left dropdown in the page and 'Navigate to Browse FAQs' in the Support menu.

Power Monitoring Expert - System Log Database is missing the index for Diagnostic table

Issue
The database analytics shows the message below regarding the missing index

Missing Index Details from ExecutionPlan1.sqlplan

The Query Processor estimates that implementing the following index could improve the query cost by 31.587%.

USE [ION_SystemLog]

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [dbo].[Diagnostic] ([Application],[Computer])

INCLUDE ([Timestamp])

Product Line
Power Monitoring Expert 7.2.2
Power Monitoring Expert 8.2
Power Monitoring Expert 9.0

Environment
SQL Server Management Studio

Cause
In SQL Server, a clustered index determines the physical order of data in a table. The purpose of indexing is to speed up the performance of queries which improves the retrieval of diagnostics information in Management Console > SystemLog

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

Timestamp index (CIX_Diagnostic_Timestamp) has been added to PME 2020. To add to older versions of PME, run the following query in SQL Server Management Studio:

USE [ION_SystemLog]
GO

/****** Object: Index [CIX_Diagnostic_Timestamp] Script Date: 3/18/2020 10:27:56 AM ******/
CREATE CLUSTERED INDEX [CIX_Diagnostic_Timestamp] ON [dbo].[Diagnostic]
(
[Timestamp] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO