SQL Server database performance improvements

Summary

This article provides standardized SQL Server maintenance and tuning guidance for SKF @ptitude Analyst databases. Following these recommendations helps ensure consistent system performance, controlled database growth, and long-term stability.

These procedures apply to Microsoft SQL Server databases used with SKF @ptitude Analyst. They are intended for maintenance and performance optimization purposes and should be executed during off-peak hours whenever possible

Overview and expected results

The following tasks can be performed to improve database performance. This guidance applies to Microsoft SQL Server.

• Improve query response times 
• Reduce fragmentation 
• Control database and log file growth 
• Increase overall system stability

Best practices

• Always perform a full database backup before maintenance.
• Avoid routine shrinking of databases.
• Rebuild indexes after any shrink operation.
• Use archiving instead of deletion when possible.

System maintenance and procedures

Rebuild indexes

Rebuilding indexes on a regular basis, typically once or twice per week, helps improve database performance. This task can also be scheduled for standard databases.

Steps

1. Log in to SQL Server Management Studio as skfuser1 (Analyst local user).
2. Click New Query.

3. Execute the following command: 

   EXECUTE Rebuild_Index_All

4. Click Execute

The procedure will run and display a confirmation message when complete, including the total execution time. Depending on the size of the database, this process may take several minutes.

Shrink database and files

Shrinking a database reduces unused space and fragmentation but should be performed only when necessary.

Important notes:

• A database cannot be shrunk to a smaller than its original size.
• Shrink operations cannot run while backups are in progress.
• Shrinking a database typically increases index fragmentation.
• Always rebuild indexes after completing a shrink operation.

Check current disk usage

1. In SQL Server Management Studio, right click on the skfuser database.
2. Select Reports > Standard Reports > Disk Usage.

Steps to shrink the database

1. Back up the database.
2. Close all active database connections.
3. Log in as sa.
4. Right-click the skfuser database.
5. Select Tasks > Shrink > Database.
6. Click OK to start the shrink operations.
7. After the database shrink completes, shrink individual files if needed: 

• Right-click the database again.
• Select Task > Shrink > Files.

Note: Shrinking large databases may take several hours. 
For more information, refer to the relevant  Microsoft documentation.
 

Check transaction log file size

Excessive growth of the transaction log file can significantly increase disk usage and affect system performance.

Example issue

• MDF file: 25 GB
• LDF file: 261 GB

This can occur when transactions are written infrequently but collected often.

Resolution steps

1. Ensure all users exit the application.
2. Stop all related services, including: IMx Service, Transaction Server, Microlog Service.
3. In SQL Server Management Studio (SSMS), right‑click the database and select: Tasks > Detach.
4. Using Windows Explorer, move the skfuser.mdf to desired location.
5. When prompted after detaching, remove the missing log file entry.

SQL Server will automatically create a new log file. 

Clean up DeletedMeas table

Excessive transaction log growth can significantly impact disk usage.

Steps

1. Log in to SQL Server Management Studio as skfuser1.
2. Open a New Query window.
3. Paste the following SQL statement:
    

   TRUNCATE TABLE skfuser.skfuser1.DeletedMeas;

4. Click Execute to run the command.

 

Remove Marked-for-Delete records

Use this procedure to remove records that have been flagged for deletion by the system.

 

Steps

1. Log in to SQL Server Management Studio as skfuser1.
2. Open a New Query window.
3. Paste the following SQL statement:
    

   EXECUTE dbTools_RemoveMarkedForDelete;

4. Click Execute to run the command.

Check for temporary tables

Temporary tables should not normally be created. However, they may appear if @ptitude Analyst crashes while generating Workspaces.

These tables typically follow the naming pattern shown in Figure 1, for example:

 

Figure 1 Temp tables example

Removing Temporary Tables

These temporary tables can be deleted using Studio Manager.
Right‑click the table name and select Delete.

Clean up EventLog table

Keeping the EventLog table small improves system performance. Event log maintenance can be performed automatically or manually.

Automated cleanup

• Event log purging is managed through the SKF Monitor application. 
• The Archive Event Log feature is recommended for ongoing maintenance. 
• As shown in Figure 2, the SKF Monitor application handles routine deletion and archiving of old event log entries.
  
  

Figure 2 Preferences dialog

 

Manual cleanup

To manually delete old event log data: 

1. Log in to SQL Server Management Studio using the local Analyst user. 
2. Open a New Query window. 
3. Run one of the following commands depending on the data you want to remove.

 

Delete Events from a Specific Year (Example: 2025)

 

DELETE FROM skfuser.skfuser1.EVENTLOG
WHERE EVENTDTG LIKE '2025%';

 

Delete Events from a Specific Month (Example: January 2025)

 

DELETE FROM skfuser.skfuser1.EVENTLOG
WHERE EVENTDTG LIKE '202501%';

Recommended maintenance schedule

• Index rebuild: 1–2 times per year. Run more frequently for high-write systems.
• EventLog Cleanup / Archive: Perform monthly or in accordance with your data‑retention policy.
• Marked-for-Delete Cleanup: Perform daily or weekly.
• Temporary Table Review: Perform as needed, typically after system crashes or abnormal shutdowns.
• Log file size review: Perform monthly.
• Database shrink: Only after large data deletions; avoid routine shrinking.

Expected Results

• Improved query response times and overall application performance.
• Reduced index and file fragmentation.
• Controlled database and log file growth.
• More predictable disk usage
• Improved stability for @ptitude Analyst operations.

 

Contacting SKF Technical Support Group

For further assistance please open a support case using the Technical Support group's self-help portal at www.skf.com/cm/tsg. Once your support case is submitted, a technician will contact you to begin working on your issue. For urgent issues we are available at these times by phone:

• Monday through Friday, 5:00 a.m. to 4 p.m. Pacific Time -
  Phone: [phone] within the US or [phone] outside the US.
• Monday through Friday, 8:00 a.m. to 4:00 p.m. Central European Time -
  Phone: [phone].
• Monday through Friday, 7:30 a.m. to 4:30 p.m. India Standard Time -
  Phone: [phone].