Need help SQL query performance issue

Hi,

Here are a few aspects you can investigate:

1. SQL Server Instance Settings
   Verify if both instances have identical settings. Pay close attention to critical configurations like MAXDOP (Maximum Degree of Parallelism) and buffer pool size.
   
   
2. I/O and Time Statistics
   Enable I/O and TIME statistics to compare the query performance between the queries. Focus on differences in logical and physical reads, CPU time, and elapsed time:
   SET STATISTICS IO ON;
   SET STATISTICS TIME ON;
   
   -- Your query here
   
   SET STATISTICS IO OFF;
   SET STATISTICS TIME OFF;
   
   
3. Wait Statistics for the Slow Query
   Check what the query is waiting on by reviewing the wait statistics. Use the following query to analyze the session’s wait types
   SELECT
       session_id,
       wait_type,
       wait_time_ms,
       blocking_session_id,
       resource_description
   FROM sys.dm_os_waiting_tasks
   WHERE session_id = <your session_id>;
   
   
4. Recompile the Query
   Use the OPTION (RECOMPILE) hint to see if a stale execution plan is causing the performance issue. This forces SQL Server to generate a new plan for the query.

I hope this provides some helpful insights.

Peter