Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Solarwinds DB Table Custom Indexes - Failing Maintenance

Hi All,

I've recently started a new roll and inherited looking after a Solarwinds Orion instance (2023.4.2). I began investigating slow performance in our dashboard using the database performance analyser (DPA).

DPA recommended adding new indexes to our CustomPollerStatistics tables. After trying this out we went from widgets failing to load history data to loading within seconds (a massive improvement). However now our db maintenance is failing on the same table.

------------------------ Maintenance Log ---------------------------

dbm_ExecuteMaintenance: dbm_Move2Partitioned: ALTER TABLE SWITCH statement failed. There is no identical index in source table 'SolarWindsOrion.dbo.CustomPollerStatistics_CS_stg' for the index 'DPA_RECIDX_4' in target table 'SolarWindsOrion.dbo.CustomPollerStatistics_CS_Detail_hist' . ALTER TABLE CustomPollerStatistics_CS_stg
SWITCH
TO CustomPollerStatistics_CS_Detail_hist PARTITION 8
WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS))

----------------------------------------------------------------------------

The SQL that DPA recommended is as per below.

CREATE NONCLUSTERED INDEX [DPA_RECIDX_4] ON [SolarWindsOrion].[dbo].[CustomPollerStatistics_CS_Detail_hist] ([CustomPollerAssignmentID],[RowID],[Timestamp]) INCLUDE ([RawStatus])

My SQL server knowledge is a bit rough and gets me by most of the time, but this one has me stumped. Has anyone here Post with more DBA experience added their own custom indexes to speed things up?

Thanks

Find more posts tagged with

Accepted answers

All comments

KMSigma.SWI

You DBA's logic looks sound (but I only "play a DBA on TV").

I should warn you that any custom indexes may be removed upon upgrade, so you should keep track of any that help performance.

mesverrum

As best I can tell the db maintenance is complaining because you added an index to one particular historical table, CustomPollerStatistics_CS_Detail_hist, but there is also table called CustomPollerStatistics_CS_stg and the maintenance job assumes they should both have the same kind of indexes. I don't think anything is going to be broken related to that, its just something the DB maintenance doesnt expect so it throws an error and I would assume it just moves on. You could probably stop it from happening by creating an equivalent index on the stg table., and maybe any other similar tables like the daily and hourly versions of that table.

helios504

Thanks! I was unaware of this. I will make a note of it.

helios504

I couldn't find any tables similar to CustomPollerStatistics_CS_stg.

I'm guessing when maintenance starts it creates a staging table to handle the aggregating of historical data, then it deletes it afterwards? If that's the case I might be snookered.

KMSigma.SWI

That very much looks like a table that's created and then destroyed. I'd recommend dropping the index for a night and see if you still encounter errors. Or better yet, open a support case. There's a chance there's a new DB Maintenance job definition for your version.