Epicor SQL Server Performance Tuning and Database Optimization

Overview

Epicor Kinetic depends heavily on the performance of its SQL Server database. Over time, as transaction histories grow, slow queries and indexing issues can degrade the user experience. For technical discussions and answers, visit the EpiUsers

Database tuning begins with identifying slow-running queries. Analyzing query statistics using SQL Server Management Studio highlights which BAQ layouts or reports require optimization. Often, poorly designed SSRS reports can put heavy loads on the server, requiring index adjustments.

Technical Implementation Details

To implement this solution in your Epicor environment, utilize the following code block. Ensure you replace the standard configurations with your company database names, server URLs, and keys.

-- T-SQL Query to identify the top 5 slow queries in the Epicor Database
SELECT TOP 5
    query_stats.query_hash AS [Query Hash],
    SUM(query_stats.total_worker_time) / 1000000 AS [Total CPU Time (Sec)],
    SUM(query_stats.execution_count) AS [Execution Count],
    MIN(query_stats.statement_text) AS [Statement Text]
FROM (
    SELECT 
        qs.*, 
        SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY [Total CPU Time (Sec)] DESC;

Process Verification and Flow

Additionally, schedule weekly index maintenance runs. Rebuilding indexes and updating database statistics reduces disk I/O bottlenecks and keeps transaction processing times under 200 milliseconds. When retrieving data through the Epicor REST API, optimized database indexes are crucial to maintaining fast response times.