Dev ›
Асинхронное обновление статистики FULL SCAN MS SQL ›
#1
07.04.23 9:24
выполняется асинхронно
DECLARE @name VARCHAR(50)
declare @cmd varchar(100)
DECLARE db_cursor CURSOR FOR
SEL ECT table_name
FROM INFORMATION_SCHEMA.TABLES
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print ( 'UPD ATE STATISTICS [dbo].'+@name+' WITH FULLSCAN')
--EXEC ( 'UPD ATE STATISTICS [dbo].'+@name+' WITH FULLSCAN')
set @cmd = 'use МояБазаДанных UPD ATE STATISTICS [dbo].'+@name+' WITH FULLSCAN'
EXEC [dbo].[sp_async_execute]
@sql = @cmd,
@database = 'МояБазаДанных ',
@jobname = @name
FETCH NEXT FR OM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
--/// ниже процедура sp_async_execute
USE [МояБаза]
GO
/****** Object: StoredProcedure [dbo].[sp_async_execute] Script Date: 07.04.2023 16:13:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CRE ATE PROCEDURE [dbo].[sp_async_execute](@sql varchar(4000), @jobname varchar(200) = null,
@database varchar(200)= null, @owner varchar(200) = null ) AS BEGIN
SET NOCOUNT ON;
declare @id uniqueidentifier
--Create unique job name if the name is not specified
if @jobname is null set @jobname= 'async'
se t @jobname = @jobname + '_' + convert(varchar(64),NEWID())
if @owner is null se t @owner = 'sa'
--Create a new job, get job ID
execute msdb..sp_add_job @jobname, @owner_login_name=@owner, @job_id=@id OUTPUT
--Specify a job server for the job
execute msdb..sp_add_jobserver @job_id=@id
--Specify a first step of the job - the SQL command
--(@on_success_action = 3 ... Go to next step)
execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step1', @command = @sql,
@database_name = @database, @on_success_action = 3
--Specify next step of the job - delete the job
declare @deletecommand varchar(200)
se t @deletecommand = 'execute msdb..sp_delete_job @job_name='''+@jobname+''''
execute msdb..sp_add_jobstep @job_id=@id, @step_name='Step2', @command = @deletecommand
--Start the job
execute msdb..sp_start_job @job_id=@id
END
GO