What to Do When Content Loads Slowly

Run the following SQL query, and check how long it will take. This might be the root cause. This query is being called from xCP viewer:

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) 140 Fetched 1 with batch hint 50 140 Commit

DQL: select max(r_modify_date) as maxdate FROM dm_document where FOLDER (‘/System/Operations/1/Config’,DESCEND)

Here is the original trace, and it shows this operation took 35 sec, instead of millisecond:

127420.054 35.505 [https-openssl-nio-443-exec-242] .RPC: applyForCollection(“EXEC”,null,DynamicallyTypedData@459c2a10[readOnly=false, autoFill=true, fetchTimestamp=0, values=[QUERY=select max(r_modify_date) as maxdate FROM dm_document where FOLDER (‘/System/Operations/1/Config’,DESCEND), FOR_UPDATE=F, BATCH_HINT=50, BOF_DQL=T, FLUSH_BATCH=-1]],50,true,true) ==> TypedDataIterator{collectionId=38, open=true, maybeMore=true}

Background information:

• Issue got fixed after running these two commands, which are recommended in KB0721725:
  • EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”
  • EXEC sp_MSforeachtable ‘Update Statistics? WITH FULLSCAN’

• since sp_MSforeachtable is deprecated, and Microsoft no longer recommends using it, please use following script:

DECLARE @TableName NVARCHAR(255);

DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + ‘.’ + QUOTENAME(name)
FROM sys.tables;

OPEN TableCursor;

FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ‘Reindexing table ‘ + @TableName;
    EXEC (‘DBCC DBREINDEX (‘ + @TableName + ‘)’);
    FETCH NEXT FROM TableCursor INTO @TableName;
END;

CLOSE TableCursor;
DEALLOCATE TableCursor;

Topics discussed during this meeting:

• Customer wanted to understand how we got to the conclusion that this would help and what those commands do.
• We explained that these commands are documented and are recommended after an upgrade, a migration, or any operation that could cause table fragmentation.
• We suspected that they were going to help because their first SQL trace was showing a lot of different queries that were taking several minutes to run, which indicates some overall problem with the database rather then a specific query slowness.
• We clarified that those commands do not create any new indexes, the first one only reorganizes all tables, to eliminate fragmentation, and rebuilds unbalanced indices, while the second one only updates database statistics for all tables.
• We explained that, over time, as users insert, update and delete rows, tables become fragmented, their indices become unbalanced and statistics become outdated, which causes higher I/O overhead when the database is searching for information, leading to slowness.
• 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.
• We informed that 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.
• As the customer believes that their DBA is not monitoring tables for fragmentation and not reindexing them from time to time, we recommended running that Job at least once a week, but they may need to run it more frequently, depending on their data volume and transactional activity.
• We 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.
• Customer asked how long it will take, given that the commands above took over 24 hours to complete.
• We clarified that those commands reindexed all tables, and it took longer because tables were probably very fragmented, but now that they have just been reindexed, the Job will run faster, because it only reindexes tables that are fragmented.
• Customer also asked about the slow queries found in their latest SQL trace and we pointed him, containing a list of the queries that took over 0.1 second, but we clarified that those queries shouldn’t be taking that long anymore, as the issue is fixed now.
• They want to collect a new SQL trace in a week and will raise a new case for us to identify slow queries in it.
• Finally, customer said that their DBA told him that the commands above are deprecated in SQL Server 2016, and their DBA wanted to know what are the new commands that should be used in that version.
• We told him that their DBA should refer to Microsoft documentation for that information, and clarified that those are the commands we have in our documentation, but we will check internally to see if someone knows something about that.

Conclusions:

• Slowness got resolved after reindexing all tables and updating all statistics on SQL Server side.

Next steps:

• Support will check internally if anyone knows about the new syntax of those commands for SQL Server 2016 and will close the case.
• Customer will collect a new SQL trace in a week and raise a new case to identify slow queries in it.

References:

• KB0721725: Documentum Server – SQL Server general performance best practices guidelines

Post-meeting notes:

• We have discussed internally about those two commands and found the following information on the internet:
  • In SQL Server 2016 and later versions, Microsoft has deprecated some system stored procedures and functions, including sp_MSforeachtable. These stored procedures are still available for backward compatibility but are not recommended for use in new development work. Instead, Microsoft recommends using alternative methods such as dynamic SQL or cursor-based operations.For the first command you provided:sqlCopy codeEXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"This command is used to reindex all tables in the database. Instead of using sp_MSforeachtable, you can achieve the same result using dynamic SQL or cursor-based operations to iterate over each table and execute the DBCC DBREINDEX command.Here’s an example of how you could rewrite the command:sqlCopy codeDECLARE @TableName NVARCHAR(128)DECLARE TableCursor CURSOR FORSELECT name FROM sys.tables
OPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0BEGINEXEC('DBCC DBREINDEX(''' + @TableName + ''')')
    FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursor
 For the second command:sqlCopy codeEXEC sp_MSforeachtable 'Update Statistics? WITH FULLSCAN'This command updates statistics for all tables in the database with a full scan. Similarly, you can achieve this using dynamic SQL or cursor-based operations.Here’s an example of how you could rewrite the command:sqlCopy codeDECLARE @TableName NVARCHAR(128)DECLARE TableCursor CURSOR FORSELECT name FROM sys.tables
OPEN TableCursorFETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0BEGINEXEC('UPDATE STATISTICS ' + @TableName + ' WITH FULLSCAN')
    FETCH NEXT FROM TableCursor INTO @TableNameENDCLOSE TableCursorDEALLOCATE TableCursor
 By using these methods, you can achieve the same functionality without relying on deprecated stored procedures. It’s important to note that using dynamic SQL or cursor-based operations might have performance implications depending on the size and complexity of your database, so it’s recommended to thoroughly test these solutions in a development environment before implementing them in a production environment.
• Additionally, 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:
  • https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
• 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:
  • 
  •