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]

GO

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

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

INCLUDE ([Timestamp])

GO

*/

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

Diterbitkan untuk: Schneider Electric Indonesia