SQL Server Job with precise timing

Question

I have a DB with game data (map, players, etc...) and I have a game core mechanics written in T-SQL stored procedure.

I need process game loop (via the stored procedure) every "X" seconds.

I tried used the SQL Job, but when I set the interval to seconds, the SQL server stops responding. If I set the interval greater than one minute, all was ok.

I need game loop precise in time, e.g. the game loop will run only once and will be executed every "X" precisely (tolerance should be less than one second).

Can I do it with SQL Server capabilities? Or should I create a windows service which will repeatly execute game loop procedure? Or should I go another way?

Thanks!

EDIT:

The game loop stored procedure takes less than the interval.

How long does your procedure take to run? If it's ever greater than a second, you're going to run into some difficulties, I imagine. — Paddy, May 11 '10 at 09:28
The procedure takes a second/two/three, however always should take less then the interval - its one of the requirement for correct system functionality. — TcKs, May 11 '10 at 09:51
Perhaps a better question would be - what happens if it doesn't (unforeseen lock, system hangup)? — Paddy, May 11 '10 at 09:54
@Paddy: I see where do you point. However, this is not the problem. The game loop is not so complex to achieve bug-free & deadlock-free (ehm :D) situation. The game loop is core element, the game system can not work without it. — TcKs, May 11 '10 at 10:11
Could you use messaging instead so you are not bound to time? — Adam Fyles, May 11 '10 at 12:52
@foosnazzy: No, the game is realtime (in fact in core is turnbased but with very fast turns). The requests from users are in queued in special table and in game loop are request proceeded + are proceeded some inner game mechanics. — TcKs, May 11 '10 at 13:25
So could the requests go into the Sql Server Service Broker(queue) and processed sans the loop? This would make the processing more real-time than the loop as messages would be processed as they are received. — Adam Fyles, May 12 '10 at 14:54
@foosnazzy: I will take a look to the Service Broker. However how should I implement in service broker the ingame logic, which is independent of user's input? — TcKs, May 12 '10 at 16:59

score 1 · Accepted Answer · answered Apr 05 '11 at 20:33

1

I would use a windows service for this. Have a loop with a thread sleep in it to get it to wait every x seconds.

The problem with timer jobs of type used in SQL server, is that there is a timer service checking if there is a job that need to be run. This timer job may check for example every 2 minutes, so precision down to a second is not possible.

answered Apr 05 '11 at 20:33

Shiraz Bhaiji

64,065
34
143
252

I did it that way. It's running for weeks and no error happend. So it's propably the right solution. – TcKs Apr 06 '11 at 07:34

score 1 · Answer 2 · answered May 04 '11 at 13:48

To run smth with interval of 1 sec you can use code:

CREATE PROCEDURE SP_Execute_job 
AS 
    WHILE 1=1
    BEGIN
     WAITFOR DELAY '00:00:01'
     EXECUTE 'somewhat job'
    END
END

This sp should be executed after each SQL Server start:

exec sp_procoption N'SP_Execute_job', 'startup', 'on'

SQL Server Job with precise timing

2 Answers2

Linked