1

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).

Improve this question
2
  • 1
    You need to figure out what is deadlocking, what part of your olap process is causing some pain (historical/fact data or dimension processing). Run a SQL Profiler Trace and add Deadlock and Lock Timeout under Locks. I have seen deadlocking and blocking caused by OLAP dimension processing in OLTP systems, even data warehouses (depending on how used).
    – Jason B.
    Commented Jul 20, 2016 at 14:02
  • That's quite a tall order. Is this the normal way that OLTP is transferred to OLAP? Via stored procedures that perform ETL directly? This is what seems wrong to me.
    – C Bauer
    Commented Jul 20, 2016 at 17:52

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

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

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