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