Sql-server – Maxdop in Profiler

maxdopprofilersql-server-2008-r2

Say I run a server side trace against a SQL Server 2008 R2 instance, which has Maxdop = 1 in place, then I run the trace through readtrace.exe to get RML files for replay.

Does the replay preserve the instance-level setting of Maxdop =1 such that none of the queries will run parallel on a maxdop = 0 server?

When I look at my trace, I see lots of events called 'Degree of Parallelism' with '1 – Select' in the EventSubClass. Does this force maxdop = 1 for the queries when they are replayed against a different instance?

Best Answer

The Degree of Parallelism event actually records the query parallelism in the BinaryData column, not the EventSubClass. See here:

0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.

From my simple tests, RML or trace replay do not enforce the Degree of Parallelism event, which kind of makes sense, as this could be overridden by server-level maxdop or a local MAXDOP query hint. SQL Server also normally decides whether to parallelise at query execution time depending on available resources. This event is also collected after a query has started, and does not appear to be one of the events that does stuff in replay mode, like SQL:BatchStarted, eg if you step through your replay trace, nothing happens in this event.

HTH

Related Question