I am in need of some tutoring and advisement. I am a nubee when it comes to designing processes as well as my dba expertise is sorely lacking. My situation is that I have a table with in excess of 500 million rows and will grow by 80+ million every month through a stored proc. The table has a primary key of 10 columns, although I could cut this down to 5 columns.
The current process is:
drop table tbl_tmp
select * into tbl_tmp
drop table tbl_mstr
select a bunch of columns(31) into tbl_mstr from tbl_tmp inner join to a current date table
alter table tbl_mstr add constraint [pk_tbl_mstr] primary key clustered (11 columns)
I am doing it this way as I need to clear all of the current year’s records and preserve all previous year’s stuff (and I don’t know any better). My initial process was to delete from where condition... current date tbl. This method seemed to take an excessive amount of time.
I thought of insert/merge/delete, but then I still had to run a delete against tbl_mstr. So I figured it still had to find the stale records amongst all those millions of rows. Again excessive time.
The box I run this on is a VM on something… but I have 4 cpus and 16gb ram running SQL Server 2016 Standard edition. My current process takes 65 minutes. I have run with 2 cpus and the time is not much different, although this leaves little cpu room for other processes.
Any advice/suggestions would be appreciated.