How to Run a Stored Procedure in SQL Server Every Hour

How to run a stored procedure in sql server every hour?

In SSMS navigate to SQL Server Agent-->Jobs
Right click on the Job Folder and select new job

on the dialog that pops up, give the job a name
click on steps, then on new, you will see a dialog like the following, pick correct DB and type your proc name

alt text

after that click on schedule, pick new and you will see something like the image below, fill all the stuff you need and click ok, click ok on the job and you should be set
alt text

SQL Server: How to run a query every hour, and store results in a table? Replace them on update

As mentioned in the comment FORMAT is a massive performance hitter; and my massive i really do mean massive. Take this DB<>Fiddle which uses FORMAT and CONVERT to change the value of a date. The query using CONVERT executes if 46ms (on db fiddle), yet the FORMAT query took 1218 ms! That's 26 times slower.

Changing format(m.addate, 'MM') to RIGHT('00' + CONVERT(varchar(2),DATEPART(MONTH,DATEADD(DAY,N-1,0))),2) will have significant performance benefits on your query. Although the latter looks more complex, it will (as the fiddle shows) out perform FORMAT by a significant margin. Honestly, I recommend never using FORMAT, Microsoft got things really wrong with that function.

I do, also, however, suggest adding RIGHT(LEFT(M.acKey,5),3) as a persisted column to your table:

ALTER TABLE tHE_Move ADD {Meaningful Name} AS RIGHT(LEFT(M.acKey,5),3) PERSISTED;

Then you can also add that value to an index (new or existing) and it'll also greatly benefit the performance of your query; maybe pushing it to only a few seconds.

How to execute stored procedure after every 5 seconds?

Use SQL Server Agent and create a job and schedule it to run every 5 seconds

Scheduled run of stored procedure on SQL server

Yes, in MS SQL Server, you can create scheduled jobs. In SQL Management Studio, navigate to the server, then expand the SQL Server Agent item, and finally the Jobs folder to view, edit, add scheduled jobs.

Executing a stored procedure every hour by using the .net code

Create a SQL Server Agent job

.net processes are too expensive in terms of memory for such tasks.

If you really need to run a .net application, look out for scheduled tasks.
Windows task scheduler is good for such tasks.

Stored procedure taking 4 hours to run

Pretty sure that whole looping thing could be rewritten along these lines. A couple of suggestions. You should ALWAYS be explicit with insert statements. Specify the columns in the insert statement and list out the columns in your select statement. When using an ORDER BY you should avoid using the ordinal position in favor of using the column name. If your query changes at some point and you don't fix the order by your results may come in an unexpected order.

ALTER PROCEDURE [dbo].[spWIPMatl]
WITH RECOMPILE AS
BEGIN

    with NewValues as
    (
        SELECT DATA0006_1.RKEY
            , SUM(DATA0095_1.QUANTITY * DATA0017_1.STD_COST) AS Material_cost
            , SUM(0.35 * DATA0095_1.QUANTITY) AS Sold_cost
        FROM DATA0095 AS DATA0095_1 
        INNER JOIN DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY 
        INNER JOIN DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY 
        RIGHT OUTER JOIN DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE DATA0017_1.P_M = 'P'
            and LEFT(data0017_1.INV_PART_NUMBER, 3) in ('25-', '85-')
            and DATA0095_1.TRAN_TP in (13, 14)
        GROUP BY DATA0006_1.WORK_ORDER_NUMBER
            , DATA0006_1.ROOT_PTR
            , DATA0006_1.RKEY
            , DATA0006_1.QUAN_SCH
            , DATA0006_1.QUAN_REJ
            , DATA0017_1.INV_PART_NUMBER

        union all

        SELECT DATA0006_1.RKEY
            , SUM(DATA0095_1.QUANTITY * DATA0017_1.STD_COST) AS Material_cost
            , SUM(0.8 * DATA0095_1.QUANTITY) AS Sold_cost
        FROM DATA0095 AS DATA0095_1 
        INNER JOIN DATA0017 AS DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY 
        INNER JOIN DATA0067 AS DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY 
        RIGHT OUTER JOIN DATA0006 AS DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE DATA0017_1.P_M = 'P'
            and LEFT(data0017_1.INV_PART_NUMBER, 3) = '35-'
            and DATA0095_1.tran_tp in (13, 14)
        GROUP BY DATA0006_1.WORK_ORDER_NUMBER
            , DATA0006_1.ROOT_PTR
            , DATA0006_1.RKEY
            , DATA0006_1.QUAN_SCH
            , DATA0006_1.QUAN_REJ
            , DATA0017_1.INV_PART_NUMBER

        union all

        SELECT DATA0006_1.RKEY
            , SUM(DATA0095_1.QUANTITY * DATA0017_1.STD_COST) AS Material_cost
            , SUM(0 * DATA0095_1.QUANTITY) AS Sold_cost

        FROM DATA0095 DATA0095_1
        INNER JOIN DATA0017 DATA0017_1 ON DATA0095_1.INVT_PTR = DATA0017_1.RKEY
        INNER JOIN DATA0067 DATA0067_1 ON DATA0095_1.SRCE_PTR = DATA0067_1.RKEY
        RIGHT OUTER JOIN DATA0006 DATA0006_1 ON DATA0067_1.WO_PTR = DATA0006_1.RKEY
        WHERE data0017_1.P_M = 'P' 
            and LEFT(data0017_1.INV_PART_NUMBER, 3) not in ('35-', '85-', '25-') 
            and DATA0095_1.tran_tp in (13, 14)
        GROUP BY DATA0006_1.WORK_ORDER_NUMBER
            , DATA0006_1.ROOT_PTR
            , DATA0006_1.RKEY
            , DATA0006_1.QUAN_SCH
            , DATA0006_1.QUAN_REJ
            , DATA0017_1.INV_PART_NUMBER
    )

    UPDATE a SET WIPmatl = WIPmatl + nv.Material_cost
        , WIP_sold = WIP_sold + nv.Sold_cost 
    from tempWIPAeroV1 a
    join NewValues nv on nv.RKEY = a.RKEY

    truncate table WIPAeroV1 --truncate will be quicker because it only has to log page drops instead of every row.
    insert into WIPAeroV1 select * from tempWIPAeroV1 --you should always specify the columns in insert statements
END

SQL Stored Procedure is Taking Hours to Execute from Application but not from SSMS (Azure SQL)

Ran the following query to get the handle to the bad query plan:

;WITH x AS 
(
    SELECT
        qs.[sql_handle], qs.plan_handle,
        txs = qs.statement_start_offset, 
        txe = qs.statement_end_offset,
        [size] = cp.size_in_bytes, 
        [uses] = SUM(cp.usecounts), 
        [last] = MAX(qs.last_execution_time)
    FROM 
        sys.dm_exec_query_stats AS qs
    INNER JOIN 
        sys.dm_exec_cached_plans AS cp 
        ON qs.plan_handle = cp.plan_handle
    GROUP BY 
        qs.[sql_handle], qs.plan_handle, cp.size_in_bytes,
        qs.statement_start_offset, qs.statement_end_offset
) 
SELECT 
    x.plan_handle, 
    size, uses, [last],
    [statement] = COALESCE(NULLIF(
        SUBSTRING(t.[text], x.txs/2, 
          CASE WHEN x.txe = -1 THEN 0 ELSE (x.txe - x.txs)/2 END
          ), ''), t.[text]) 
FROM x 
CROSS APPLY sys.dm_exec_sql_text(x.[sql_handle]) AS t
WHERE (t.text LIKE '%UNION%' AND t.text LIKE '%CompetitorAggregation%');

Then, an admin on the database server ran the following:

DBCC FREEPROCCACHE (<plan_handle from above query>)

EXEC sp_recompile [<name of the stored procedure>]

After these queries were executed, we are down to a 15 minute run time which is quite acceptable for us, as this process does not run very often (every 6 hours).

Update: The problem is back, after only a few days. We need a more permanent solution.

Update (2): I inspected the query plan of the stored procedure using the following query:

SELECT q.last_execution_time, p.is_parallel_plan, t.query_sql_text, p.query_plan FROM
sys.query_store_query q
join sys.query_store_plan p on q.query_id = p.query_id
join sys.query_store_query_text t on q.query_text_id = t.query_text_id
WHERE q.object_id = object_id('dbo.ProductAggregation_Update')

I found something interesting. The insert into the non-clustered index for the temp table has a very high cost. So I reworked the temp table to the following:

IF OBJECT_ID('tempdb..#ProductAggregation') IS NOT NULL DROP TABLE [#ProductAggregation];
CREATE  TABLE [#ProductAggregation](
PID decimal(12,0) NOT NULL,
ISBN13 varchar(13) NOT NULL,
CompetitorAggregationId varchar(32) NULL,
EBookWorkId int NULL,
ProductAggregationId varchar(32) NULL

PRIMARY KEY CLUSTERED (PID, ISBN13)
);

Hopefully this will speed it up. There was significant subtree cost (around 4000) associated with inserting into the non-clustered index, IX_tmpProductAggregation_PID.

Update (3): After making the above optimization, our execution time is down to 8 minutes.

Update (4): Even with above optimization, we're getting run times sometimes up to 1 hour.

So I added a left join with the target table and a comparison of DateChanged between source (CompetitorAggregation) and target (ProductAggregation), to narrow down the data set to only the records that were changed since last run. This has reduced run time down to 1.5 minutes. We'll have to test to see if we get spikes to 1 hour again or other regressions.

Update (5): I have found a way to get rid of the UNION also. I have separated it into two insert statements and changed the primary key like so:

PRIMARY KEY CLUSTERED (PID, ISBN13) WITH (IGNORE_DUP_KEY = ON)

This gets rid of the DISTINCT Sort operation in the query plan.

How to Run a Stored Procedure in SQL Server Every Hour