Maxdop hint for Views
With the advent of multi core processors the issue of parallelism is likely to cause more problems than before – the usual posted answer on forums to resolve parallelism ( CXPACKET ) locks is to disable or severely restrict processors.
I’m sure this is a discussion that could run and run but I’m not concerned with this just now, my personal stance is that I dislike processor restrictions, I liken it to buying a V8 engine and removing 4 spark plugs to keep the speed to 70mph!
However – Views – bane of my life , well complex views, difficult to tune, nearly always seem to generate the query from hell, very prone to parallelism blocking, on an 8 way box I’ve had over 70 threads generated from a view, most times taking the query out of the view, and adding a maxdop statement will resolve performance – only you can’t put the maxdop statement in the view.
The issue with parallelism is that you can very easily starve yourself of threads, if you have concerns then run dbcc sqlperf(umsstats).
I’ve seen the number of worker threads increased to try to resolve this but unless there’s been a change, increasing the worker threads can also cause problems as I believe a process has to “round robin” all available workers before execution. ( SQL 2005 of course handles workers dynamically - however I'm sure there has to come a point where the cpu's throw in the towel ! )
So assuming you can’t escape from the view, because the application can’t call a stored procedure, what can you do?
Well in SQL 2000 nothing, but in SQL 2005 there are plan guides, I’m not going to post lots of sample code, mainly because I don’t have an 8 way server to hand or a complex view which illustrates the issue, but here’s how you attach a maxdop statement to dynamic sql / view.
For those DBA’s supporting applications which generate dynamic sql which you can’t alter in the application, this is also a way to add hints, rather than crippling your nice multicore server!
here's an example plan script
exec dbo.sp_create_plan_guide
@name = N'PlanGuide_101',
@stmt = N'SELECT STATEMENT',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@P1 varchar(20)',
@hints = N'OPTION (MAXDOP 1)'
Go
The subject of plan guides is well covered in BOL and there are further enhancements to their use in SQL 2008.