EV 11 and Fragmentation warning

[Sarah_Seftel1]

Hi,

ALL of the EV 11 installation I have done in the last months results in the same after a month:

Fragmentation warning for indexes

When we approach the DBA team they run check and say all is ok.

even after running re-index, after a week or two the events are returning.

Anything special regarding this check?

Should we ignore it? should I change the threshold?

Sarah

[GertjanA]

I have the same. DBA's have an automated script, defragging indexes once a week. Performanc of EV is fine.

I've decided to disable the monitors on the EV-sites where this error occurred too frequent without causing issues.

See: http://www.symantec.com/docs/HOWTO101982
Configuring the SQL fragmentation System Status hidden monitors

I use this SQL to disable the 2 monitors all together:

Update Monitoring
Set Enabled = '0'
Where MonitoringItemType = 34 or MonitoringItemType = 33

[S-L]

We see the same behaviour. I read somehwere that this check is new in EV11 SP1.

According to Symantec Technote https://support.symantec.com/en_US/article.TECH168905.html tables with more than 1000 pages scanned can be ignored. Those tables are fine in our environment. There are a handful indices with higher fragmentation, but they only have less then 10 scanned pages.

So the test in the EV Admin Conole doesn't use Symantecs Best Practise vaulues at all. Also, when I run the 'ReadFragmentationResults' stored procedure (https://support.symantec.com/en_US/article.TECH224136.html) I get an average of 99%, which I can't verify with the DBCC SHOWCONTIG WITH ALL_INDEXES,tableresults.

I don't see either the option to modify the thresholds in the EV monitoring.

[S-L]

Thanks for your suggestion. I disabled those checks as well.

The idea behind those checks is not too bad, but if it triggers a false positive it's implementation sucks.

[AndrewB]

surely the check be adjusted instead of disabled. what about modifying the threshold parameter?

[S-L]

I would agree with you. But when I execute the script from page 40 of the SQL Best Practice Guide from EV11 "to identify all tables and indexes where the external fragmentation exceeds 5% and the table consists of at least 1,000 pages" it doesn't find anything.

SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS TableIndexName, phystat.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') phystat
JOIN sys.indexes i ON i.object_id = phystat.object_id AND i.index_id = phystat.index_id
WHERE phystat.avg_fragmentation_in_percent > 5 AND phystat.page_count > 1000

The query from https://support.symantec.com/en_US/article.TECH224136.htmlon the other hand does find something. And now it's going to be odd: If I execute both queries I can get two different results on the same DB for the same table for the same tableindex...

Procedure	Index	avg_fragmentation_in_percent
query from SQL BP for EV11	PK_JournalArchive	57,55
query from TECH224136	PK_JournalArchive	99,27

So how should I handle this inaccuracy?

[xgtdec]

Just to add to this that we have the same issue since going to 11.0.1. Again our DBA tells us all is fine but we're constantly reminded in the GUI that we are over 30%.

The strange thing from the EV point of view is that the Status checks will change from hour to hour, as an example at midday today i reran all checks and it reported no issues anywhere, then at 1pm i reran again and it tells me 4 databases have a frah level of over 30%, its terribly in consistent in its reporting considering our schedule is alway out of hours so EV is essentially sitting doing nothing.

Ive bookedmarked this in the hope somebody has some news on the issue!

[GertjanA]

xgtdec, you have to take in account that any activity might cause this.

We for instance, run collections during the day = database activity

If you have journal archiving, you also have database activity.

I am not sure about inconsistency, but as long as my DBA's state - SQL = fine, then I am fine.