Geek asks and answers

Accurate time running SQL Server

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

I need a gameplay loop (through a stored procedure) every "X" seconds.

I tried using SQL Job, but when I set the interval in seconds, the SQL server stops responding. If I set the interval for more than one minute, everything was fine.

I need a game cycle accurate in time, for example. the game loop will only start once and each "X" will be executed exactly (tolerance should be less than one second).

Can I do this using SQL Server features? Or should I create a Windows service that will repeat the game loop procedure? Or should I go the other way?

Thank!

EDIT:

The game loop stored procedure takes less than an interval.

+1
source share
2 answers

I would use the Windows service for this. It has a loop with a thread to make it wait every x seconds.

The problem with timer jobs of the type used on the SQL server is that there is a timer check service if there is work to be started. This timer job can be checked, for example, every 2 minutes, so accuracy of up to a second is not possible.

+1
source

To run smth at 1 second intervals, you can use the code:

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

This sp should start after each SQL Server starts:

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

More articles:


All Articles