Search indexes in log database - VisualCron - Forum

Community forum

Jon Tofte-Hansen
2022-04-06T12:08:24Z
Hi

We like to keep execution logs resulting in millions of lines in the tables JobLogs and TaskLogs. It is very practical to have a complete log history when debugging performance problems or when nosy auditors ask for proof of old job executions.

Unfortunately the VisualCron client has never been very good at fetching log details in large data sets. We have through the years regularly experienced client crashes - even server crashes - because of long wait times on running queries in the log database. The posibility of having an external log database has been a lifesaver, because we could query the log details directly in the database with SSMS.

Well, the other day we sat down and analyzed the problem thoroughly , and we discovered that the two tables above was lacking proper indexing. After adding the below indexes the log history was suddenly usable in the client GUI.

Unless you have good reasons not to index the log tables, I would recommend to add something like this to the log database:

CREATE NONCLUSTERED INDEX [index_jobid]
  ON [dbo].[JobLogs] ( [JobId] ASC )

CREATE NONCLUSTERED INDEX [index_executionid]
  ON [dbo].[TaskLogs] ( [ExecutionId] ASC )

CREATE NONCLUSTERED INDEX [index_taskid]
  ON [dbo].[TaskLogs] ( [TaskId] ASC )



Thank you.
Support
2022-04-06T13:00:46Z
Perfect, thanks, we will create this for the next version!
Henrik
Support
http://www.visualcron.com 
Please like  VisualCron on facebook!
Jon Tofte-Hansen
2022-05-24T09:09:11Z
For general information.

This has greatly improved fetch performance (at least in our system):

CREATE NONCLUSTERED INDEX [index_taskid_serverid_startdate]
  ON [dbo].[TaskLogs]
    (
      [TaskId] ASC,
      [ServerId] ASC,
      [StartDate] ASC
    )
Michael Fjellström
2022-05-24T09:30:57Z
Originally Posted by: Jon Tofte-Hansen 

For general information.

This has greatly improved fetch performance (at least in our system):

CREATE NONCLUSTERED INDEX [index_taskid_serverid_startdate]
  ON [dbo].[TaskLogs]
    (
      [TaskId] ASC,
      [ServerId] ASC,
      [StartDate] ASC
    )



Thank you for this suggestion!
Scroll to Top