I have no code to share... cause I don't think the code is the problem.
Every day of the week, my system at 0300, loads data from our ERP into our home built CRM.
The last major step is a match that tests incoming records (under 3000 a day) against the existing database data - name, address, phone, email and such, to find duplicates and merge them accordingly.
This match step takes about 25-40 minutes per day.
About 6 weeks ago, on Mondays ONLY, the match step blows up and takes 4 hours! Since 28 May, it has done this all but two Mondays. To say this is getting old is no joke.
I have tested the fragmentation of the various indexes and none are bad, with frag under 5%.
No other SQL jobs are running, backups ended about 1 hour before my 0300 job starts
This is a VB 2015 job that runs the loop, it brings in a record from the incoming data table, passes it to a proc that does the matching and saving of new records.
To make matters worse, this process has been running for 18 months without a problem, and will run the rest of the week just fine - meaning that tomorrow, the morning load steps will be done by 0330.
I am also researching the OS to make sure that an MS update didn't get me. The server is a Dell server with 2 Xeon processors 128 gb RAM, 64 bit WIn 2012 R2 Standard.
We checked that.... and the virus scan isn't running then! Good idea, tho.
It appears as if the Veeam network backup is the cause...it was backing up the entire 1.4 tb of the server and moving it across the intranet to the backup server. It was taking 12 hours! And that varied, but everytime my job ran into problems, it smelled of contention with the backup process.
Adjustments have been made.
I will update this thread next week to provide closure.
The latest and greatest... and a potential cause, which is leading to a bigger set of questions...
I upgraded from 2012 to 2016, things did improve, no question.
And in the process of doing another project, one of my tables, which stores just names, had blown out of proportions. I was expecting 7-8 million records, found 75 MILLION!!! And 67 million were the SAME CUSTOMER! Repeated 67 million times. I am testing my code... but can't figure out why one entry would be repeated to a factor of 10 what I expected to see.
Sounds like something where table constraints might help you prevent the problem in future. Is there a unique index you can put over the data? Or can you create an indexed view with a uniqueness constraint that will cause a failure if something attempts to double up records again?
If your match step is taking 24-40 minutes just to check < 3000 rows, you have a serious problem. You should start a new thread and post that match code along with the DDL for the tables and let us see what the problem may be.
Good point, however, the match is a bit of a pig... it compares names. addresses, phones and emails, uses joins to connect names to addresses, emails and phones, looks at partial names and addresses to compensate for spelling errors. I really need to clean it up. As usual, no time...
And it did it again. 4 hours to run the match.
This time, I have to figure out why I am getting SA password doesn't match messages. Last week, the account was locked. I think I know what I need to do there. log in using the SA account and resave the backup plans with the password attached. These bugs all appeared after upgrading from SQL 2012 to SQL 2016.