Sometimes, you will notice this sql is taking over 34 sec instead of 0 sec.
select all max(dm_document.r_modify_date) “maxdate” from dm_document_sp dm_document, dm_sysobject_r dm_sysobject_r2, dm_folder_rp dm_folder_r1 where (dm_document.r_object_id = dm_sysobject_r2.r_object_id and dm_sysobject_r2.i_folder_id = dm_folder_r1.r_object_id and dm_folder_r1.i_ancestor_id = ‘0b027183800008b3’) and (dm_document.i_has_folder = 1 and dm_document.i_is_deleted = 0)
The original dql for this sql is:
select max(r_modify_date) as maxdate FROM dm_document where FOLDER(‘/System/Operations/1/Config’,DESCEND)
This dql is triggered when you are loading xCP viewer.
To solve this performance issue, you must keep SQL Server configurations, as shown below:
- Auto Create Statistics (AUTO_CREATE_STATISTICS) = False (OFF)
- Auto Update Statistics (AUTO_UPDATE_STATISTICS) = False (OFF)
Never ever set them to True. Run UpdateStat manually.
