Retrieving Logs getting slower over time

Retrieving Logs getting slower over time. #MC-KB21

Written by Giuseppe

Last published at: February 28th, 2024

The log tables rely on indexes to perform queries fast. Over time, these indexes get fragmented.

You need to ensure that you regularly defragment the log table indexes. If you are unsure about how to do this, here are our recommendations:

On-Prem hosted SQL Server

If you host your own SQL Server, you can use the stored procedure RebuildLogsIndexes while will check and perform maintenance as needed on all the tables in the database in one go:

  1. Open SQL Server Management Studio
  2. Open a new query window in your ThinScale database
  3. Copy and paste EXEC RebuildLogsIndexes
  4. Right-click and select Execute or press F5

 

Graphical user interface, text, application Description automatically generated

This can take several hours depending on the fragmentation size, or it can be very quick to execute, depending on your server hardware, the age/size of your database, and other factors. Such an operation is cancelable, and if you need to resume later, it will pick up where it left off.  We recommend running the maintenance regularly, and you can consult with your company's database admin for best practices depending on your needs.



If needed, for greater control, instead, you can run the index maintenance for individual tables one by one (this applies to Management Server 7.4.x and up):

  1. Open SQL Server Management Studio
  2. Open a new query window in your ThinScale database
  3. Open this attached .txt file containing 46 scripts (one to address each index, which is compatible with tables in versions 7.4.x and up) individual table index maintenance.txt
  4. Perform a Find and Replace action so that each instance of "YourDatabaseName" is replaced with the name of your company's specific Thinscale Database
  5. Copy the first script and paste it into your query window in SQL Server Management Studio
  6. Right-click and select Execute or press F5
  7. Once you get the success message "Commands completed successfully," please repeat steps 5-6 for the rest of the 45 scripts.
Delete



 

 

Azure SQL Server

Otherwise, if you host on Azure, we recommend this script:

https://github.com/yochananrachamim/AzureSQL/blob/master/AzureSQLMaintenance.txt

1698947529000-individual+table+index+maintenance.txt