Maximum Degree of Parallelism (DOP) and Client Policy Interactions
I had a strange case raised from a customer at the end of last week.
Whilst preparing a virtual to physical migration of their NS server they found, after restoring the database and setting NS to work with it, that almost each time a client computer (out of the few on the NS) requested a configuration from the NS it received a new one.
This is not normal, and with a fair count of client the extra workload could have a pretty negative impact. Inspecting the policy file it was clear that the content did never really changed but only got shuffled around.
So we held the client migrations to the new platform and investigated.
The client policy data comes from the spPoliciesGetActive result set. This stored procedure returns data from the PolicyCache or from the various policies. So it contains many joins and a final sort to order the dataset by lastmodified date.
Now this works for all customers without any problem but in this case we had a bad combination:
- first the customer does a lot of policy imports, so many (actually an incredible lot if you ask me, with as many as 15,000 entries) policies have the same modifieddate (a perfect sql match, so within the same milli-second).
- second the SQL server was set with a non-standard Max DOP - the maximum degree of parallelism. This impacts how the query processor generate the SQL and also the results, most noticeably the ordering.
So after resetting the Mx DOP to 0 (default - which sets no limit on the count of threads to be used) the problem was gone.