I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. Please assume that some of the basic tenets of OLAP+OLTP are things I am unfamiliar with.
In my organization there is a concept of "Consolidating" the OLTP data to a series of tables prefixed by conso which are essentially denormalized structures (the OLAP, I think). This occurs overnight and the jobs which perform the work are extremely slow, on the order of several hours. They also do not scale well as we add more customers the processing time has shot up.
There is also parameters to these consolidation jobs that cause them to run in a smaller context, and this is triggered when a customer updates the data for a particular metric. That is generally pretty fast.
Unfortunately, the night time consolidation jobs are causing deadlocks for some customers and other issues that make the process extremely problematic. We might wake up to alarm bells because the consolidation worked for 1/2 of the customers and not the rest.
What can be done to avoid this issue? Is there a best practice for running OLTP=>OLAP transactions? Determining the source of the deadlocks are outside of my experience and I did not write the consolidation jobs. I would leverage the person who wrote the original jobs, but they are not competent or this wouldn't be happening in the first place (I assume).
Best Answer
Perhaps this problem has been solved meanwhile. Databases need dedicated knowledge and skills, any 10 year old kid can play around with SQL Server Management Studio, but creating and maintaining databases is not that easy.
For SQL Server , make sure that Snapshot Isolation level is enabled. It's disabled by default, with the database in data locking mode, where long running queries block write acces, or even certain read accesses. I think Snapshot Isolation was introduced in SQL Server for analysis/OLAP.
Also, the OLTP tables should have proper indices. There should be somebody in the company knowing at least the basics of all this.