Issue
The database analytics shows the message below regarding the missing index
Product Line
Power Monitoring Expert 8.x
Power Monitoring Expert 9.x
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 bee 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
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 8.x
Power Monitoring Expert 9.x
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 bee 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
Released for:Schneider Electric UK
