SolarWinds Databse : Cortex_Document Table

Hi kevin.tran​

Cortex is a new service (Solarwinds Cortex Service) that was introduced in 2017.3 core platform. I believe the objective of this service is to consolidate all polling and monitoring activities across various Solarwinds modules.

Maybe this documents should help

Success Center - Solarwinds Cortex Service will not start

Success Center - Solarwinds Cortex Port Requirements

Not sure how relevant this is, but we had an issue where we had 15 mil records in the table which was causing huge memory usage on the ServiceHost process on the MPE & APEs (Orion 2018.2 HF6) as well as smashing the DB on that table

This is what we got given from support

Please refer the following for the suggestion from the developer for Orion platform 2018.2:

• The previous SQL script that drops duplicated data from Cortex_Documents has not been executed probably because there is vSAN element found in the database and the historical data below will be lost:
  • Cluster Details page - storage tab (Resource Utilization)
  • Esx Host Datails page - Virtualization Summary tab (bottom part of Resource Utilization)
• After investigation, the developer found that the above mention need to be clear in order to fix the issue (even after upgrade).
• If you are ok to lost the historical data from the vSAN element, run following SQL script to drop the duplicates data (it will take couple of minutes):
  • DELETE FROM Cortex_Documents WHERE Data LIKE '%"Orion.Virtualization%'
  • Restart SolarWinds Cortex service on all pollers to release the duplicated data from the memory.

It may be worth doing a "SELECT COUNT(*) Cortex_Documents WHERE Data LIKE '%"Orion.Virtualization%' " to see if its the same issue

We didn't see any noticeable data loss after nuking that data and it did resolve our memory / SQL performance issues

Have you opened a support case for it?

Thanks Ravik. I did see those documents. Didn't say much what deleting the table would do or cause any harm.

Thanks tgeihlich!

Looks like we have similar issue. I did open a support case with SW support and they said to drop the Cortex_Document table as a while which has about 14.8M records. In your case, only drop the Orion.Virtualization data which makes more sense SW support troubleshot in detail to find the issue. This SW support agent asked us to delete all 14.8M records.

We did ask the BDA team to perform backup but I am afraid dropping = delete and we don't have any historical data. We do need historical data.

On your case did you loose any historical data after you deleted Orion.Virtualization data?

Best,

Kev

Hey Kev,

We didn't lose any historical data for Nodes or Applications

The devs mentioned that there would be data loss for vSAN components but we only had two that had been decommissioned so I'm not sure if we lost data there

Out of curiosity, what sort of memory usage are you getting? Before the fix we were doing 20-35gb (on 32GB APE's, was not a fun time) on the ServiceHost process and after we're doing about 7gb

Cheers,

Tyler

Hi Tyler,

We had memory usage ranged from 12GB - 18GB on 24GB APE on the ServiceHost process. After the upgrade to 2018.4 HF3, the issue went away but it took a long time for things to settle down. We did use the My Deployment to upgrade the APEs. Currently, we are dealing with stuck at Loading issue under All Groups but today when I logged on, no more stuck at Loading issue. We did NOT dropped the Cortex.Document Table from DB.

Best,
Kev

tgeihlich We seem to be experience similar issues of queries against Cortex_Documents that is running up our CPU and starting to affect the Web Console.  kevin.tran​ were you able to fix your issue?  Did you clear out the whole cortex table?

Hi bharris,

Yes! We followed SolarWinds support's instruction to clear the table (all records) after backed up Database of course. We also did the upgrade to the latest at that time which was 2018.4 HF3. It took a long time for the upgrade since we have many pollers that didn't have the correct .NET and Windows Updates (yup! all Pollers must be same updates and .NET version).

You can also open a case with SolarWinds because it might be something different from your environment. They will need to look at the Diagnostics Logs.

Good luck!

Best,

Kev

kevin.tran Do you happen to have the ticket number from support?  I'd like to provide that to support so they can look into it in regards to my ticket.

CASE # 00339826

Yes please work with Support First Prior to deleting anything. Remember to BACKUP!!!

    Instructions:

Run the following query on the SQL Studio Mangement Console to drop the current
Cortex_Document and re-create those needed files:

DROP TABLE [dbo].[Cortex_Documents]

DROP TABLE [dbo].[Cortex_DocumentTypes]

DROP TABLE [dbo].[Cortex_ExternalDocumentTypes]



CREATE TABLE [dbo].[Cortex_Documents](

[ElementId] [bigint] NOT NULL,

[DocTypeId] [int] NOT NULL,

[OwnerPartitionId] [int] NOT NULL,

[LastWriteTime][datetime2](7) NOT NULL,

[DeletedDate] [datetime2](7) NULL,

[Data] [nvarchar](max) NOT NULL,

CONSTRAINT [PK_Cortex_Documents] PRIMARY KEY CLUSTERED (

[ElementId] ASC,

[DocTypeId] ASC

) ON [PRIMARY]

) ON [PRIMARY]



CREATE TABLE [dbo].[Cortex_DocumentTypes](

[DocTypeId] [int] IDENTITY(1,1) NOT NULL,

[DocType] [nvarchar](250) NOT NULL,

CONSTRAINT [PK_Cortex_DocumentTypes] PRIMARY KEY CLUSTERED (

[DocTypeId] ASC

) ON [PRIMARY],

CONSTRAINT [IX_Cortex_DocumentTypes] UNIQUE NONCLUSTERED (

[DocType] ASC

) ON [PRIMARY]

) ON [PRIMARY]



CREATE TABLE [dbo].[Cortex_ExternalDocumentTypes](

[DocTypeId] [int] IDENTITY(1,1) NOT NULL,

[DocType] [nvarchar](250) NOT NULL,

CONSTRAINT [PK_Cortex_ExternalDocumentTypes] PRIMARY KEY CLUSTERED (

[DocTypeId] ASC

) ON [PRIMARY],

CONSTRAINT [IX_Cortex_ExternalDocumentTypes] UNIQUE NONCLUSTERED (

[DocType] ASC

) ON [PRIMARY]

) ON [PRIMARY]


Start the Cortex Service back up, wait for a few minute to see
if it still not running.