Share via

SQL Job taking long time to execute

Amit Jaiswal 1 Reputation point Microsoft Vendor
2022-03-07T08:07:47.23+00:00

We are using sql virtual machine with Windows Server 2019 Datacenter and there is one job saasmasterapi which take long time to run...and we have another sql VM with same configuration and there same job saasmasterapi takes very less time ie 5min

Please suggest how to identify why its taking long time to run

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Amit Jaiswal 1 Reputation point Microsoft Vendor
    2022-03-07T08:56:04.227+00:00

    Please find the details of SaasMasterApi what exactly doing---

    SaaS_LoadMasterDataApi Job sync all SaaS Portal master table record into API DB. If it fails then User permission might be impact

    Also LoadMasterDataApi.dtsx : to sync Master Data(all lookup data in Application & Business Profile and User details & roles) from SaaS Portal Db to SaaS API Db.

    0 comments
  2. Ronen Ariely 15,186 Reputation points
    2022-03-07T09:13:02.357+00:00

    Hi,

    Let's start with collecting some info so we will be able to provide more focuses options. For each point please check both machines and provide the information

    (1) Machine resources: What template did you use to create the VM (if you changed the default resource then pls elaborate)

    (2) What is the version of SQL Server?

    Execute: SELECT @@VERSION

    (3) What is the full code to create the JOB ?

    (4) If the job uses tables then please provide the DDL of the tables + amount of rows + DML for a few rows, so we will be able to reproduce the scenario in our servers

    (5) Make sure that you execute the JOB using same level of user

    (6) Check Waits, locks, deadlocks, blocking during the execution of the job - most likely another task is blocking the execution in one server

    (7) rebuilt indexes and updated stats on both and try again

    (8) Confirm no issues with machine level resources such as disk IO, CPU, memory, etc.

    (9) Try to narrow the issue down as much as you can. Clean the jobs as you can and leave the part which lead to the issue - will be simpler to continue if the issue related to single query for example.

    (10) Compare the Execution Plan in both machines

    (11) Inform us if you have any other relation or object like AlwaysOn Availability, replication and so on

    (12) SQL Server Management Studio contains an Agent GUI that can show us details on recent job outcomes, as well as the ability to monitor jobs in progress. In the Object explorer windows go to: SQL Server Agent -> Jobs -> Job Activity Monitor

    180548-image.png

    use it on both machine and try to get more insights

    ...

    If these will not help then you can follow this post and build a Job performance tracking solution: https://www.sqlshack.com/tracking-job-performance-sql-server/

    0 comments
  3. Amit Jaiswal 1 Reputation point Microsoft Vendor
    2022-03-07T12:25:27.573+00:00

    (1) Machine resources: What template did you use to create the VM (if you changed the default resource then pls elaborate) :- I have created the slq VM through azure portal

    (2) What is the version of SQL Server? : On both Machine version is same - Microsoft SQL Server 2019 (RTM-CU13) (KB5005679)

    Execute: SELECT @@VERSION

    (3) What is the full code to create the JOB ? - No ideas

    (4) If the job uses tables then please provide the DDL of the tables + amount of rows + DML for a few rows, so we will be able to reproduce the scenario in our servers - No sure on this may be devloper aware of it

    (5) Make sure that you execute the JOB using same level of user : Yes its using same account saas-t

    (6) Check Waits, locks, deadlocks, blocking during the execution of the job - most likely another task is blocking the execution in one server - please find the attahed report

    (7) rebuilt indexes and updated stats on both and try again - We have tried this option but issue remain same

    (8) Confirm no issues with machine level resources such as disk IO, CPU, memory, etc. - No issue with disk IO, CPU, memory

    (9) Try to narrow the issue down as much as you can. Clean the jobs as you can and leave the part which lead to the issue - will be simpler to continue if the issue related to single query for example.

    (10) Compare the Execution Plan in both machines - both are same

    (11) SQL Server Management Studio contains an Agent GUI that can show us details on recent job outcomes, as well as the ability to monitor jobs in progress. In the Object explorer windows go to: SQL Server Agent -> Jobs -> Job Activity Monitor - Not able to select the folder