I am using a SQL 2008 database (not R2 sadly but have also tested this on an R2 database server) and have an ELT process running. Data is extracted from source and loaded to staging tables in the destination DB, and then the transform processes are run. On Tuesday (26 Nov) for no known reason, the procedure which 'shreds' the staging data for providers into the relational tables went from running approx 9 per second to one every 5½ minutes. No parts of the database, ELT process (not a typo!), database server etc were changed.
With around 800 records every night (and up to several thousand) this single step of the process has gone from taking just over a minute to potentially taking just over 3 days (this doesn't happen in practise of course because we notice and stop the long running job). This happens even if I manually run the code which is within the procedure rather than letting it run as part of SSIS, and the same happens if I run through the code for just one record rather than letting the loop go through.
By sheer chance I happened to discover that if I ran the following code against the provider staging table and the provider destination relational table, suddenly my ELT process runs as normal again and zips through everything:
update statistics tblProviderStaging with fullscan update statistics tblProvider with fullscan
My question (sorry for the journey to get here) is why would I have to run this manually every morning? I can't even run it before the ELT as it makes no difference - only affects the process if I run it while the process is slowly churning through. Presently I am running this at 7am every morning (SSIS kicks off at 6:30). Auto Update Statistics and Auto Create Statistics are both set to 'True'.
I have never, in my limited experience, come across a scenario where I have to manually run this every morning? And the fact that I can't just run it either but have to run it while the ELT is still running? We have tried running the SSIS package from a different server, against a different destination database server (restored the DB to another DBS), have checked all indexes, I/O performance is running as normal, there is no undue server load happening while this runs (it was running about 18% capacity and nothing to speak of on any of the performance metrics).
The staging table in question has just over 1.2 million rows and uses up just over 1.5GB of space (including index size). The destination table in question has 430k rows and uses around 140 MB of space (including index size). This has not changed significantly.
If there is any more information I need to provide please let me know but I'm shooting into the dark here so I don't know what is helpful and what is just in the way. If anyone has any ideas of places and things to check to see why I am having to do this please shout!
Please note we have run checkDB which has not shown any issues in the DB.