- Recommendations on database operations ↪
- Defragment Indexes and Update Database ↪ Statistics
- Recommendations on index ↪ maintenance
- What are the most trafficked tables in ADM, where deleting records is not recommended? ↪
- What are the most trafficked tables in ADM, where records can be deleted? ↪
Recommendations on database operations
If you are running a query or direct operation against the Aranda Device Management (ADM) tables, you should consider the following recommendations that ensure that database performance is not compromised in the operation:
- You should not manually modify the information stored in the databases, avoiding unexpected behavior in the application.
- Use replicas of the data for historical reporting.
- When building reports, avoid querying all fields in a table using the wildcard character (*).
- When building reports, avoid running SQL commands that query tables in their entirety, instead define WHERE clauses that allow you to get a tight number of rows.
- Don’t delete data from tables unless you’re clear about the implications. Deleting data may affect the operation of the software. This task may require approval from the audit area and/or the company’s management.
- Use the TRUNCATE statement when you need to delete all records from a large table, making the process more efficient since it does not generate entries in the transaction log.
Defragment Indexes and Update Database Statistics
Index defragmentation and statistics update tasks help improve database performance by improving access to data on disk. It is advisable to perform these tasks on a regular basis to ensure that the database is functioning smoothly by ensuring that the execution plans are compiled with the latest statistics from the database.
Perform the following procedure:
- Confirm the edition of SQL Server that you are running. If your edition is enterprise or you’re using Azure SQL Database, skip straight to step 4. For more information on online index trading, please see: Perform Index Operations Online
- Make sure that specialists are not editing, querying or entering data into the tools of the Aranda suite. To do this, you can stop the Internet Information Services (IIS) where the Aranda applications are hosted.
- Stop the Aranda Suite services on the application server.
- Connect to the SQL Server using SQL Server Management Studio and open a new .sql file
- Change the context to the Aranda database, e.g.
USE Aranda;
GO
- Rebuild indexes with fragmentation greater than 30% and page usage less than 60% by running the script:
SET NOCOUNT ON;
DECLARE
@v_command NVARCHAR(MAX),
@v_init DATETIME,
@v_fragmentation FLOAT,
@v_page_space_used FLOAT,
@v_table_name SYSNAME,
@v_index_name SYSNAME;
DECLARE cur_index CURSOR LOCAL STATIC FOR
SELECT
CONCAT (
'ALTER INDEX [',
i.name,
'] ON [',
OBJECT_NAME(s.object_id),
IIF(s.avg_fragmentation_in_percent BETWEEN 10 AND 30, '] REORGANIZE', '] REBUILD')
) statement,
s.avg_fragmentation_in_percent,
s.avg_page_space_used_in_percent,
OBJECT_NAME(s.object_id) table_name,
i.name index_name
FROM sys.dm_db_index_physical_stats (
DB_ID(),
DEFAULT,
DEFAULT,
DEFAULT,
'LIMITED'
) s
JOIN sys.indexes i ON i.index_id = s.index_id
AND i.object_id = s.object_id
WHERE s.page_count > 50
AND (
s.avg_fragmentation_in_percent > 10
OR s.avg_page_space_used_in_percent < 60
) AND s.index_type_desc <> 'HEAP'
AND s.page_count < 262144;
OPEN cur_index;
FETCH NEXT FROM cur_index INTO
@v_command,
@v_fragmentation,
@v_page_space_used,
@v_table_name,
@v_index_name;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @v_init = CURRENT_TIMESTAMP;
EXEC sp_executesql @v_command;
PRINT CONCAT(
'Ending: ',
IIF(@v_fragmentation BETWEEN 10 AND 30, 'reorganize ', 'rebuild '),
@v_table_name,
'.',
@v_index_name,
' execution time ',
CONVERT(VARCHAR(10), DATEDIFF(MILLISECOND, @v_init, GETDATE())),
'ms ',
'fragmentation ',
@v_fragmentation,
'%'
);
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH;
FETCH NEXT FROM cur_index INTO
@v_command,
@v_fragmentation,
@v_page_space_used,
@v_table_name,
@v_index_name;
END;
GO
- Restart all previously stopped services from the Aranda Suite and Internet Information Services (IIS).
Recommendations on index maintenance
- Operations on indexes in SQL Server editions other than Enterprise or Azure SQL Database can cause table crashes causing unexpected behavior in Aranda applications, schedule these tasks to run automatically at least once every week at times of low transactional load.
- Track the physical statistics of your indexes by using SQL Server management views to determine whether you should adjust the periodicity of index defragmentation operations. If you determine that the fragmentation remains high > 50%, reduce the periodicity, if you do not want to do so, you can maintain the recommended one or increase it.
What are the most trafficked tables in ADM, where deleting records is not recommended?
- AFW_ADDITIONAL_FIELD_VALUE: This table contains the values of the additional fields of all the concepts in Aranda Device Management.
- AFW_USERS: This is the main table of users, this table concentrates customers, field specialists and web specialists. This table can be fed new users through the web console and synchronization from the LDAP active directory. This table should not be purged and its records should not be deleted.
What are the most trafficked tables in ADM, where records can be deleted?
PRC_ASM_CLEAN_SOFT_USE: Cleaning up the history of software usage can be scheduled from the ADM application, in the Configuration - Measurement option. The frequency to clear the history must be configured by inventory type (Detail, Daily, and Monthly), this task is automatically scheduled to run daily from the time the configuration is made.
-
We recommend that you run this cleanup from the database before performing the configuration from the ADM application in the following cases:
- That the database contains a large history of software usage data
- Failure to run the scheduled task from the ADM console
- Procedure PRC_ASM_CLEAN_SOFT_USE removes software usage information from tables [ASM_SOFTWARE_USAGE], [ASM_SOFTWARE_USAGE_DAY] and [ASM_SOFTWARE_USAGE_MONTH]
Parameters:
-
@i_detail parameter of type DATE, is used by the procedure to delete the detailed software usage history of the date specified in the backward parameter. Delete information from the table [ASM_SOFTWARE_USAGE]
Example: @i_detail =’2022-01-01 00:00:00’
-
@i_day parameter of type DATE, is used by the procedure to remove the daily rollback from the software usage of the date specified in the backward parameter. Delete information from the table [ASM_SOFTWARE_USAGE_DAY]
Example: @i_day =’2022-01-01’
-
@i_month parameter of type DATE, is used by the procedure to delete the monthly software usage rollup from the date specified in the backward parameter. Delete information from the table [ASM_SOFTWARE_USAGE_MONTH]
Example: @i_month =’2022-01-01’
Implementation of the Procedure
EXECUTE [dbo].[PRC_ASM_CLEAN_SOFT_USE]
@i_detail ='2020-01-01 00:00:00',
@i_day ='2020-01-01',
@i_month ='2020-01-01'
- AAM_AGENT_TASK - AAM_AGENT_TASK_DETAIL: These tables save the actions that are scheduled from the console to the device. For example: Request an inventory, Request remote control, Send a message, Restart device, etc…; these actions have statuses (Registered = 1, Reported = 1, Running = 2, Executed = 7, Executed with Errors = 8).
Depending on the management of the tool, it can generate several actions in turn several records. You can make a decision to execute a physical deletion script for the actions that have been performed correctly and with an effective date.
DELETE TASKDETAIL
FROM dbo.AAM_AGENT_TASK_DETAIL TASKDETAIL
INNER JOIN dbo.AAM_AGENT_TASK TASK ON TASKDETAIL.task_id=TASK.id
WHERE TASKDETAIL.status in (7,8) and TASK.creation_date < '2020-01-01 00:00:00.000'
DElETE TASK
FROM dbo.AAM_AGENT_TASK TASK
LEFT JOIN dbo.AAM_AGENT_TASK_DETAIL TASKDETAIL ON TASK.id = TASKDETAIL.task_id
WHERE TASKDETAIL.task_id IS NULL
AFW_ALERT: This table saves the alerts that the system generates. For example: discovered devices, devices with specific software/hardware, devices with a disk occupancy percentage; these alerts have 3 statuses (New, Reviewed, and Closed). Depending on the management of the tool, it can generate several alerts and in turn several records.
A decision can be made to execute a physical deletion script for alerts that have been closed by dispatchers with an effective date since it has an index with the following keys (creation_date, status_id, category_id):
DELETE
FROM [BD_NAME].[dbo].[AFW_ALERT]
WHERE status_id = 3 and creation_date < '2020-01-01 00:00:00'
AFW_MAIL_HISTORY: The ADM System sends emails for different reasons. These emails are sent by a scheduled task and once they are sent satisfactorily they go to the AFW_MAIL_HISTORY table; This board can grow quite quickly; This history remains intact and a decision can be made under which criteria to delete the information.
For example, if you want to delete information, you can use the following script:
DELETE
FROM [BD_NAME].[dbo].[AFW_MAIL_HISTORY]
WHERE mahi_created < '2020-01-01 00:00:00'
AFW_WORKER_LOG: This table has the task execution log that the Aranda worker service executes. This log is used to identify that the tasks have been executed in the expected time and to validate their status by means of the work_success field (0: Failed, 1: Successful, 2: Pending execution and 3: In process).
You can make a decision to delete with a certain periodicity the tasks that were successfully executed and leave those that present some type of error in case any support or adjustment is required.
DELETE
FROM [BD_NAME].[dbo].[AFW_WORKER_LOG]
WHERE work_execution_date < '2020-01-01 00:00:00' AND work_success = 1