SQL Fascination

MaxDop is in some sense a bit of a misnomer, in that you would think ‘Max Degree of Parallelism’, set by the system administrator would be the last and final word on the matter; That is your maximum, and there are no choices to be made.

However, whilst at the SQL Immersion event in Dublin hosted by Prodata I had made an off hand comment about increasing the thread count beyond the MaxDop setting whilst creating an on-line index on an OLTP based system that had MaxDop 1 set.

That gained me some quizzical looks, in that most assume MaxDop is set and has to be adhered to, so surely what I was indicating was not possible? Well – yes it is, and there is even a KB that relates to it and the difference between SQL Server 2000 /2005 and 2008. http://support.microsoft.com/default.aspx/kb/329204

I should also mention that the BoL is less than precise about the situation, against SQL 2008 the BoL states for ‘Max Degree of Parallelism Option’:

 Set max degree of parallelism to 1 to suppress parallel plan generation.

 And against the ‘Degree of Parallelism Page’ :

For example, you can use the MAXDOP option to control, by extending or reducing, the number of processors dedicated to an online index operation. In this way, you can balance the resources used by an index operation with those of the concurrent users.

So that’s entirely clear…

You can infer (aside from the KB) that something is not what it seems when  it states  ‘by extending or reducing’ – how can you extend it? that would not make logical sense if it was a hard limit.

So we have a slightly bizarre situation in which you can override the server level setting with your own value. Initial thought is, this is a bit dangerous isn’t it?

Would you want the average user or developer to start implementing the appropriate query hint and override you? They would get more CPU so they wouldn’t hesitate (but not guarenteed better performance for it, since they would all be doing it)

To perform a test, I used a large partitioned table since the parallelism that occurs when querying a partitioned table is far easier to predict and engineer specifix scenarios to. The table had ~688 million rows in it and the selection was asking for a simple row count from 2 of the available partitions. The server used was an 8-core.

Select Count(*) From LargePartitionedTable Where MyPartitionColumn in (200801, 200901)

The baseline case was setting the server to a maxdop of 1

sp_configure 'max degree of parallelism', 1
go
reconfigure
go

The query plan is not particularily complex and the two key parts are that the clustered index scan. 

 The baseline case of using MaxDop 0 was then set up and the same query run. 

sp_configure 'max degree of parallelism', 0
go
reconfigure
go

As expected, the query ran up 8 threads (8 core machine) and only 2 of the threads processed any work, since SQL 2005 threading model is to assigned 1 thread per partition, when more than 1 partitioned is requested.

So the base scenarios are in place, now the query is altered slightly to override the MaxDop upwards.

sp_configure ‘max degree of parallelism’, 1
go
reconfigure
go
Select Count(*) From tblODS Where TimelineID in (200801, 200802) Option (maxdop 4) 

If the override works, the thread count will go up from 1, and as per the KB article it does.

So the MaxDop set by an administrator does not need to be obeyed – but does this apply to everyone? That is really the key issue and unfortunately it does not appear to fall the way you would wish it to. Placing the SQL into Mixed Authentication mode I created a user account, and gave it only the permission to select from the table and the show plan permission, nothing else.

Result?  The query parellelled again, indicating there is no special permission required for a user to overide the setting.

The facility to override it is useful, on an OLTP where you want to increase the indexing speed by using more threads it is essential – but to not require it as a granted permission to the service accounts / jobs performing those tasks – that seems slightly bizarre.

These tests were performed using SQL 2005, so I need to test SQL 2008 to see if the lack of controls continue to exist.