I run a monthly process that essentially interfaces data between two SQL servers. The process - a script is used to launch a stored procedure - updates an accounting table in one database on server A using sales records from a second database on server B. It's a relatively simplistic procedure; check the record in server B meets specified month-end criteria, and if it does then bring the record across - with a couple of extra fields joined in fron other server B tables.
At the beginning of the year this process, which generally brings around 1,000 sales transactions and 10,000 transaction lines, took about 3 minutes to run.
However, in July our dba (not really SQL trained) reconfigured server B after a hardware failure, and everything was theoretically set up just the way it was before. This seemed to work in that none of the sales staff using server B on a day-to-day basis have any problems but my month-end process now takes 6 hours - instead of 3 minutes.
The dba and myself have no idea on what might have changed to cause this massive increase in process time for what remains a simplistic stored procedure.
Does anyone have any idea on what we might be able to check to diagnose / fix this issue so I get back to a 3 minute run time?