4

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.

Improve this question
4
  • While running your ETL process, have you tried to determine what the process is waiting on? sp_who2 or SELECT * FROM master.sysprocesses WHERE spid > 50
    – Craig Efrein
    Dec 3, 2013 at 10:42
  • Yes - nothing. I write each part except the update statements with(nolock) and this executes at a time where no-one else is on the database. I had one of the DBAs (I'm a dev here although was DBA in last job but don't have access now to run sys processes or sp_who2 as no server level access only DB level) run this and they advise there was no locking, no wait times.
    – Elatesummer
    Dec 3, 2013 at 11:02
  • @Elatesummer, perhaps you can motivate someone to run a GRANT VIEW SERVER STATE to your login?
    – Craig Efrein
    Dec 3, 2013 at 11:03
  • thanks @CraigEfrein - I have asked for this and have been told there is a ten day lead time so hopefully I will get some perm levels soon (driving me nuts!)
    – Elatesummer
    Dec 3, 2013 at 11:08

3 Answers 3

Reset to default
3

SQL Server uses a cost based formula for determining how to satisfy queries.

"It'll cost 10 random reads in a billion row table vs 1000 sequential reads. I think I can satisfy the needs faster by doing the 1000 reads since they'll all in order" and decision made off it goes. Unfortunately, when the engine opens the data file to find those 1000 sequential rows, the data has been updated and instead of living in there, there's simply a pointer saying "sorry we had to move, try this address in the next state" And you now have 1000 forwarding rows to contend with. The database engine can't go back and say let's try re-evaluating this costing metric because those 10 reads sound a lot better.

That's probably what is happening (simplified) with your data. The statistics on your data distribution are all out of whack after your ELT processing which is typical-you've just moved a pantsload of data so what was true before may not be as true now. Depending on usage, I will have an explicit call to a reindexing job (thank you Ola) after ETL is complete so that now that all my data is up to date, I need to get all the metadata the server uses up to date as well.

Improve this answer
0
2

You may be experiencing an issue with ascending keys, which is leading to a poor execution plan choice.

SQL Server builds statistics with the assumption that the data will by in large be similar in the future. However, when data typically ascends, most new insertions are out of the previously found range. This can lead to poorly performing plans as filters selecting recent data seem to exclude the entire relation when in fact a significant number of rows are included.

Try the trace flags mentioned in the linked article:

-- enable auto-quick-statistics update for known ascending keys
dbcc traceon( 2389 )

-- enable auto-quick-statistics update for all columns, known ascending or unknown 
dbcc traceon( 2389, 2390 ) -- never enable 2390 alone

I'm left wondering why your staging table contains 12 million rows when you mention the daily increment being 1-2k. If you've previously processed the data why not truncate the table before loading the increment for processing.

Improve this answer
2
  • Thank you, will give this a go. It's huge because the source system sometimes updates existing rows and sometimes adds new, and we are required to keep a full history of every change. We don't have any CDC software either. I am pushing for a change to this methodology though, of course!
    – Elatesummer
    Dec 3, 2013 at 12:30
  • Oops. I can't count. It's 1.2 million not 12 million - OP updated
    – Elatesummer
    Dec 3, 2013 at 15:01
0

As part of another process, I had to rebuild the staging table. Doing this has resolved the problem so all I can think is that there was a consistency error somewhere which was not reported by the checkdb function?

If this happens again then I will enable the flags suggested in the previous answer (which I have upvoted) but as this appears to have been resolved by rebuilding the table I will leave it as this for now.

Improve this answer
2
  • Rebuild = drop and create? Did you create all the same stuff that was on the table (indexes, triggers, statistics)?
    – billinkc
    Dec 6, 2013 at 13:26
  • yes, to all parts - recreated all keys, indexes, there were no triggers on this one, statistics etc (we needed to do this because we were adding another column in the middle).
    – Elatesummer
    Dec 6, 2013 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.