Forum Discussion

Lakshmikiranreddy's avatar
Lakshmikiranreddy
Copper Contributor
Dec 11, 2024

Need help SQL query performance issue

I am looking to help optimize the below select statement 

I have created supporting indexes and updated statistics , the same query is taking 2sec in one database ( note that same table structure and same data on both tables ) but another database it's taking 95sec on the same server.

I compared the execution plan for both 2sec vs 95sec it's exactly matching . Any clue on why it's running faster on one database but taking lot of time on other database.

These tables not heavily fragmented and stats are up to date on both database.

 

These tables contains 200million rows. Another important note is that indexes is not playing any role here , with or with out indexes query is taking 95sec 

 

declare @v_vch_cut_off_date date =cast( getdate()-10 as date)

 

Select

 

        ztr.work_day,tlms.work_day,

 

              ztr.from_zone,zl.travel_zone_name,

 

              ztr.from_business_unit,tlms.business_unit,

 

              ztr.from_process,tlms.process,

 

              ztr.from_item_category,tlms.item_category

 

       FROM dbo.t_zone_travel_report(nolock) ztr

 

       LEFT JOIN dbo.t_lms_process_time (NOLOCK) tlms

 

       ON ztr.wh_id=tlms.wh_id

 

          AND ztr.employee_id=tlms.employee_id

 

          AND ztr.start_tran_datetime >= tlms.start_tran_datetime AND ztr.start_tran_datetime < tlms.next_tran_startdatetime

 

       -- AND ztr.log_id >= tlms.log_id AND (ztr.log_id < tlms.next_log_id OR tlms.next_log_id=999999999)

 

          AND tlms.work_day >= @v_vch_cut_off_date

 

       LEFT JOIN dbo.t_travel_zones_locations (NOLOCK) zl

 

              ON ztr.wh_id=zl.wh_id

 

              AND ztr.from_location=zl.location_id

 

       WHERE (ztr.work_day >= @v_vch_cut_off_date OR ISNULL(ztr.work_day,'') = '' )

 

                 AND (

 

                             ISNULL(ztr.from_zone,'') <> ISNULL(zl.travel_zone_name,'')

 

                             OR ISNULL(ztr.from_business_unit,'') <> ISNULL(tlms.business_unit,'')

 

                             OR ISNULL(ztr.from_process,'') <> ISNULL(tlms.process,'')

 

                             );

2 Replies

  • IrfanDBA's avatar
    IrfanDBA
    Copper Contributor
    Feb 20, 2025

    Lot of stuff to check and compare servers and Its not that easy to compare.

    You need to deep dive into things like
    -- Hard disk cluster size.
    -- server size, O/S, memory, # of CPUs
    -- allocated RAM to SQL Server, MaxDOP settings,
    -- allocated TEMPDBs and their sizes
    -- indexes and # of pages with data
    -- statistics, execution plan
     

  • petevern's avatar
    petevern
    Brass Contributor
    Jan 05, 2025

    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

Resources