+2
Requested

Add SQL index in Log table

Maja Pejcic 5 years ago in ADAM Core updated by Olivier Lasserre 4 years ago 0

Hi,

After investigating DB performance we've come to a conclusion that this index would be useful to be added in ADAM DB:

/*
USE [adamdb]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblLOG] ([Severity],[LoggedOn])
INCLUDE ([Text])
GO
*/

Adam support did allow us to add the index into our client's DB and suggested feature request to be made for this.

 

SQL

Hi,

I'm interested in all possibilities and ways to increase performance on Adam environment.

Can you add more details about the benefit on performance when this index is added in the ADAM DB?
Have you used a specific tool or benchmark software to compare the performance before and after adding this index on tblLOG?
Index is added on the tblLOG, so the improvement should be only be when you query on the log table, when a search is done for a specific log entry (in System / ConfigStudio).

Thanks

Hi Olivier,

Our DBA back in the day noticed and pointed out that execution of certain queries provokes CPU spikes and alerts, he made a suggestion that we introduce this index in our DB having said that the Query Processor estimated that implementing that index could improve the query cost by 84.9385%. And you're right that this is only for tblLOG, so if you use logging a lot and are frequently doing searches for logs, this can certainly help the performance. 

Hi Maja,

FYI, we performed tests and benchmarks on a ADAM DB before and after have add a new index on tblLOG.

Unfortunately for us, the result was not really good. For a same query / search in the tblLOG the total_elapsed_time was around 7 times as much longer with the new index.
Thanks anyway to share your experience and your idea!