Parallellisatie in Analysis Services
Onlangs was ik bij een klant om een probleem in Analysis Services (2008 R2) op te lossen. Bij het processen van een SSAS database, kregen zij regelmatig de volgende foutmelding:
“OLE DB error: OLE DB or ODBC error: The query processor could not start the necessary thread resources for parallel query execution.; 42000.”
Zoals de foutmelding suggereert, betreft het hier een SQL Server fout die word veroorzaakt door een query op SQL server die tot teveel threads leidt. Hoewel het mij na heel wat uitzoekwerk nog steeds niet gelukt is volledig te begrijpen waardoor deze foutmelding nu precies veroorzaakt wordt, weet ik ondertussen wel dat er op hoofdlijnen twee workarounds zijn om het probleem te voorkomen:
- MAXDOP op SQL Server instellen
- het aantal parallelle processing jobs in SSAS verlagen
MAXDOP staat voor Max Degree Of Parallelism en is in te stellen via sp_configure (globaal), via MAX_DOP instellingen in een Resource Governor resource pool (bijv. per user) en via de OPTION MAXDOP query hint (per query). De default waarde is 0, wat betekent dat je aan SQL Server zelf overlaat hoeveel parallelle threads SQL Server aanmaakt. Veel DBA’s zullen ook aanraden de instelling daarop te laten staan. Maar op SQL Server databases waarop veel kleinere queries worden afgevuurd, zeggen sommige best practices weer dat je MAXDOP op 1 moet zetten om parallellisatie te voorkomen. En tijdens het processen van een Analysis Services database, worden er vaak een groot aantal kleinere queries gelijktijdig afgevuurd (met name tijdens het processen van dimensie attributen). Als je MAXDOP niet op 0 wilt laten staan en ook niet op 1 wilt zetten, kun je als vuistregel nog hanteren het aantal cores als instelling te gebruiken. Waarbij er nog discussie heerst of je daarvoor het aantal fysieke cores of logische cores (in het geval van hyperthreading) moet hanteren. Veel meer info over MAXDOP, inclusief een groot aantal links naar weer andere informatie, vind je hier.
Naast de SQL Server query optimizer die zelf bepaald of delen van een query al dan niet parallel uitgevoerd kunnen worden, zorgt ook Analysis Services zelf voor parallellisatie. Wanneer je bijv. één dimensie procest die een aantal attributen bevat, zal Analysis Services de queries voor die attributen default parallel uitvoeren. En wanneer je in één batch alle dimensies van een Analysis Services database procest, zullen deze allemaal parallel geprocest worden. Je kunt dit default gedrag op een aantal manieren beïnvloeden. Zo kun je bij het processen van een batch opties opgeven waarmee je het aantal parallelle taken wijzigt van ‘Let the server decide’ naar een aantal wat in jouw omgeving nog werkt.
Ook kun je per dimensie afdwingen dat de attributen binnen die dimensie sequentieel worden verwerkt door de dimensie eigenschap ProcessingGroup op ByTable te zetten in plaats van de default ByAttribute. Ook kun je binnen Analysis Services voor rigoureuzere maatregelen gaan en voor de hele SSAS instantie de parallellisatie terugdringen middels de server eigenschap ThreadPool – Process – MaxThreads.
Hoewel je met dit soort instellingen het probleem waar mijn artikel mee begint kunt voorkomen, is het lastig een algemeen advies te geven hoe dit op te lossen. Probleem is namelijk dat je met het terugdringen van parallellisatie ook de performance negatief kunt beïnvloeden. Ideale instellingen zijn er dan ook niet maar deze hangen af van de omstandigheden. It depends… Voor iedere Analysis Services batch de parallellisatie uitschakelen of MAXDOP op de hele server op 1 zetten, gaat mij vooralsnog te ver.
Nog een aantal links naar artikelen die ik heb geraadpleegd bij het onderzoeken van dit probleem:
- Analysis Services Query Performance Top 10 Best Practices, http://technet.microsoft.com/en-us/library/cc966527.aspx
- The query processor could not start the necessary thread resources for parallel query execution, http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/d4458d55-54d3-48d1-aa44-dafe34f92560
- The ins and outs of MAXDOP, http://blogs.msdn.com/b/cindygross/archive/2011/01/28/the-ins-and-outs-of-maxdop.aspx
- max worker threads Option, http://msdn.microsoft.com/en-us/library/ms187024(v=sql.105).aspx
- Processing appears to stall or become sluggish on multi-processor machines running Analysis Services 2005, http://blogs.msdn.com/b/psssql/archive/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005.aspx