Data Warehouse index fragementation

Dashboard Server
1

I was reading Kevin Holman’s script library on his blog page today and came across this query and was wondering if anyone had tried this yet. He gave the following SQL query to check and see how badly your Data Warehouse Database index is fragmented

 

–Show index fragmentation (to determine how badly you need a reindex – logical scan frag > 10% = bad. Scan density below 80 = bad): DBCC SHOWCONTIG DBCC SHOWCONTIG WITH FAST –(less data than above – in case you don’t have time)

 

I was thinking could we set up a rule to run this periodically and if it encounters an error run the following script he gave as a recovery?

 

–Reindex the database: USE OperationsManager go SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF EXEC SP_MSForEachTable “Print ‘Reindexing ‘+’?’ DBCC DBREINDEX (’?’)” –Table by table: DBCC DBREINDEX (‘TableName’)

 

Was just thinking it could help make SCOM self healing and keep performance at its max.

2

We run the ReIndex once a day to ensure the Console is responsive. I can’t recommend enough!

Just make sure it’s done outside of normal maintenance:

https://kevinholman.com/2017/08/03/what-sql-maintenance-should-i-perform-on-my-scom-2016-databases/

Don’t force SQL to do too much at once or you’ll have bigger problems!

Also, the check is good, but it will return a lot of information that you’ll need to comb through. Do it once to get a baseline, then re-index and compare.

3

Had that article book marked already to work with my SQL team to make sure all the recommendations in it are followed when we stand up our 2016 environment.

4

Great to hear! (Ignore my last - It’s one of those days!)