Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

research Question

long running SQL/T SQL archive job

Avatar of marrowyung
marrowyung asked on
Microsoft SQL ServerSQL* SQL SERVER 2017
20 Comments1 Solution65 ViewsLast Modified:
hi,

right now we have a trouble that when we doing this for long time, it will blocks other queries:

set IDENTITY_INSERT db_archivedbo.long_running_query on
go

declare @now date
set @now = dateadd(day, -30, getdate())
select @now

INSERT INTO db_archivedbo.long_running_query
           ([id]
         ,[check_date]
           ,[start_time]
           ,[total_elapsed_time]
           ,[client_net_address]
           ,[program_name]
           ,[database_name]
           ,[sql]
           ,[session_id]
           ,[connect_time]
           ,[login_name])
select [id]
   ,[check_date]
    ,[start_time]
    ,[total_elapsed_time]
    ,[client_net_address]
    ,[program_name]
    ,[database_name]
    ,[sql]
    ,[session_id]
    ,[connect_time]
    ,[login_name] from db.dbo.long_running_query where [check_date] < @now

delete from db.dbo.long_running_query where [check_date] < @now
go

set IDENTITY_INSERT db_archivedbo.long_running_query off
go

any way to make the process much faster ? from my point of view:
1) make sure that this table: SQLdb_archivedbo.long_running_query, do not have too much index so the insert will be much faster!
2) data partition the table SQLdb_archivedbo.long_running_query so that the write is faster.

any other suggestion on this? this insert as a  batch process should be very fast already right ?or how can I break the insert process into separate batch and it make do not block other process?