Hello Geeks,
Welcome to the new series of seven wonders of SQL Server. These are not the wonders which make you say Aww. These are the ones which make you really wonder why. I will be covering the seven of such jaw dropping situations where you wonder what is happening and few bizarre reasons why it is the way it is.
Last week we have seen how space monitoring can be a daunting task and how it can be managed. This week I will be covering the most crucial part of database server designing.
Have you ever seen a transactional database being used for reporting? Yes, more than often, this is a very common scenario. And the worst part is having a separate datawarehouse database for your reports and host it on the same machine as the transactional database.
OLTP and OLAP do not mix well. Although the SQL Server product team is working towards closing this gap, it is far from reality for most big shops. SQL Server 2014 had Clustered Columnstore indexes to increase performance of DW queries. It had few limitations. But in SQL server 2016, Operational Analytics helps in having real time reporting from OLTP database with help of CCI built on a transactional table. Mix it with in-memory tables and you have a better solution. But considering the limitations on in-memory tables, the big datawarehouse databases cannot be replaced with Operational Analytics.
Coming back to the problems with having DW and OLTP databases in single instance or different instances on same machine, they don’t gel well.
- Many OLTP environments work best with MAXDOP 1 and DW queries work best with parallelism.
- OLAP queries do huge IO which creates IO bottleneck for OLTP queries. In one of my previous companies, we had an issue with bottleneck at switch level where the IO requests from DW servers were choking and creating problems for OLTP server IO requests. The solution was to rezone the servers to different ports at the switch layer. You will have less options when you are using single machine.
- If you are running reports over your OLTP tables, consider the number of indexes you need for your reports. All these indexes needs to be kept up to date with inserts, updates and deletes from your OLTP workload. This slows down your transactions drastically.
- The ripple effect is OLAP and OLTP queries start creating blocking, unavoidable deadlocks and timeouts, application slowness, revenue loss, etc. A total disaster as you did not separate two different entities.
- Backups, Index and statistics maintenance and the list goes on…
So my advice, keep the design granular and segregate based on purpose and usage of the databases. There could be many reasons to cramp up things into single server. This is not always suitable for bigger environments. Remember, divide and rule. 🙂
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
