Browse FAQs

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

Published date: 18 March 2020

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 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

Was this helpful?

What can we do to improve the information ?

Can't find what you are looking for?

Reach out to our customer care team to receive information on technical support, assistance for complaints and more.