I have a job that essentially does nothing and takes 13 seconds to run. But the Step only takes 1 sec. The job set is using these TSQL commands. set nocount on select GETDATE() as before I have job set to run every 10 seconds but according to job history. Job takes 13 seconds and Step only take 1. This makes no sense. There are other programs on the system using SQL but there is no apparent bottlenecks with system resources or SQL resources. No errors reported by SQL. Any help would be appreciated.

The explanation that come closest at hand is that some query/ies is in msdb is slow. You could enable Query Store. Not that you can do a lot to optimise in msdb, but there could be some history table(s) that could benefit from a purge. And a job that runs every ten seconds is likely to produce quite an amount of data.

SQL Server 2022 Agent runs job to slow.

LiddellJim-7791 0

I have a job that essentially does nothing and takes 13 seconds to run. But the Step only takes 1 sec.

The job set is using these TSQL commands.

set nocount on

select GETDATE() as before

I have job set to run every 10 seconds but according to job history.

User's image

Job takes 13 seconds and Step only take 1.

This makes no sense. There are other programs on the system using SQL but there is no apparent bottlenecks with system resources or SQL resources. No errors reported by SQL.

Any help would be appreciated.

1 answer

Erland Sommarskog 110.4K Reputation points MVP

2024-08-23T21:02:44.66+00:00

The explanation that come closest at hand is that some query/ies is in msdb is slow. You could enable Query Store. Not that you can do a lot to optimise in msdb, but there could be some history table(s) that could benefit from a purge. And a job that runs every ten seconds is likely to produce quite an amount of data.
Please sign in to rate this answer.
LiddellJim-7791 0 Reputation points

2024-08-26T16:13:49.7233333+00:00

Thanks for your comments. I'll watch the Query Store data. So yesterday I had them shutdown the application such that nothing was accessing the database except this job. The job acted the same. Still shows 13 second duration and the Step shows 1 sec. I suspect that there is something wrong with SQL server.

Erland Sommarskog 110.4K Reputation points MVP

2024-08-26T20:43:11.7566667+00:00

Maybe I should ask what you really want to do. SQL Server Agent is designed mainly for running maintenance jobs. Running something every ten seconds may not have been a design goal. For a job is to run for an hour or two, twelve seconds overhead for starting the job and noting its completion is entirely ignorable.

For a job that is to run every ten seconds it starts to get problematic. And to add insult to injury, since the jobs runs so oftem, the log and history tables will grow quite a bit, which is not helping for performance.

LiddellJim-7791 0 Reputation points

2024-08-28T16:31:59.2566667+00:00

First, thank you for your comments.

This type of job we have used on other project without problems. It's basic purpose is to go through one of the database table and perform a bunch of count functions. We are an automated inventory control system and have 'Move' records which tells us where to move something from one place to another. This job counts the number of moves that are moving from one point to another. We save this data in another table. This job frees up out application programs from doing these counts online. We have used this type of job successfully in many other system where the SQL Agent runs the job and step in less than a second. We just have this one site where this problem is occurring and can't figure out why it is behaving so badly.

Thanks again for you help.

Erland Sommarskog 110.4K Reputation points MVP

2024-08-28T20:58:42.2566667+00:00

It is unheard of bad performance in msdb. Here is a blog post, which is a bit old, but which may still be useful: https://weblogs.sqlteam.com/geoffh/2008/01/21/msdb-performance-tuning/
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Server 2022 Agent runs job to slow.

1 answer

Your answer