We have a mixed workload that includes web queries (customers), ETL jobs, and BI reporting users. When there are no ETL jobs or BI reports running, our web (tactical) queries average 2 seconds. When other workloads are running, especially long-running jobs, our tactical performance is terrible (30 - 60 seconds). I've used TASM to give 90% priority to tactical and I've limited the other workloads to 50% CPU. Does anyone have any suggestions for protecting the tactical queries from the other workloads?
It is tough to guess as to what exactly is going on but some thoughts. Is there possibly a lock situation here where the tactical queries are getting blocked? Are the amount of long running jobs a big number? If so, you may want to consider throttles for the longer running jobs. Also what is the overall WD strategy you have setup as you may need some finer granularity. Also have you tried a smaller relative percentage for the longer running queries? Like 90% tactical, 10% to long running jobs? Are the longer running jobs experiencing nasty skewing? If so, you may want a TASM exception to track this and either fix the SQL or have those jobs downgraded to a lesser percentaged WD. There really is no one size fits all solution here as the solution is very case dependent with a number of variables to consider.