- every weekend they have incremental indexing on tables, then how full indexing worked?
indexing on dm_sys objects are configured in OOTB indexing. Their DBA is responsible setting up indexes on custom tables. Why does incremental indexing doesn’t work for the slowness they were facing?
ANS:
We won’t be able to answer this question as this question is for Microsoft SQL. Your DBA needs to check with MS support for incremental indexing VS full indexing and why incremental didn’t work.
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-dbreindex-transact-sql?view=sql-server-ver16
2) Is full text indexing is recommended from OT and how frequently they have to do full indexing?
ANS:
DBA needs to monitor tables for fragmentation, by using SQL Server reports and tools, and reindex the fragmented tables whenever needed.
These are the main tables to monitor:
–dm_sysobject_s/r
–dm_document_s
–dmi_object_type
–dmr_content_s/r
–custom_type_s/r
–dmi_queue_item_s
–dm_audittrail_s
We don’t have any specific recommendation for how frequently you should run it. But whenever above tables get too fragmented, you need to run commands which we recommended or dm_updateState job.
As below commands are old and deprecated, our performance team gave some recommendation to follow:
○ EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”
○ EXEC sp_MSforeachtable ‘Update Statistics? WITH FULLSCAN’
Recommendation from Performance Team:
Our Performance team strongly recommends using the built-in SQL Server tools to monitor tables for fragmentation and reindex them whenever necessary, instead of using Documentum’s dm_UpdateStats Job, if possible, or, alternatively, you can use the following third-party tool, which will only defragment and reindex when needed, which can be configured based on a threshold:
Here’s a command that can be used with that tool:
sqlcmd -E -S DEFRACMPDMSQ02 -d Statistics_DB -Q “EXECUTE [dbo].[IndexOptimize] @Databases = ‘USER_DATABASES’, @FragmentationLevel1 = 3, @PageCountLevel = 500, @LogToTable = ‘Y'” -b
And here’s an example of where to find those tools in SQL Server Management Studio:
SQL Server Index and Statistics Maintenance (hallengren.com)
3) dm_updateState , this job was not running when they face p1 issue. so is this necessary to run it. do we need to run it?
ANS:
It’s the DBA’s responsibility to monitor tables for fragmentation, by using SQL Server reports and tools, and reindex the fragmented tables whenever needed.
This can be done either by the DBA from SQL Server side or by a Documentum Job called dm_UpdateStats, which can find fragmented tables and reindex them, if you pass argument “-dbreindex FIX”, as mentioned in KB0721725(https://support.opentext.com/kb?id=kb_article_view&sysparm_article=KB0721725)
We recommend running that Job at least once a week, but you may need to run it more frequently, depending on your data volume and transactional activity.
During the last ticket, support checked DA and confirmed that this Job is currently Inactive, so we activated it, configured “-dbreindex FIX” and scheduled it to run tonight, once a week.