Blog – Fabiano Neves Amorim

There are many cases when QO can create an execution plan that access the table using a join operator, and using parallelism. To see a sample I’ll show the index intersection.

It is used when SQL can join the result of two indexes to return the query.

An interesting sample could be the following query using AdventureWorksDWR2:

   SELECT [ProductAlternateKey], [ProductSubcategoryKey]

   FROM DimProduct

   WHERE [ProductAlternateKey] = 'BA-8327'

   OR [ProductSubcategoryKey] = 12

   OPTION (RECOMPILE)

The query is using the indexes by ProductAlternateKey and ProductSubcategoryKey on the table DimProduct to read all the data, joining the results using the Hash Join and then it applies the filter on the where clause.

Now to simulate a parallel plan, we can do some good tricks :-). First you can fool SQL about the number of CPUs that you have. Second you can fool SQL about the size of your table.

To fool SQL about the number of CPUs you can start the SQL using the parameter –P16.

SQL Server Configuration Manager:

Error Log file after restart SQL Server:

Alternatively you can check how many CPUs do you have using the DMV sys.dm_os_sys_info.

   SELECT cpu_count FROM sys.dm_os_sys_info

cpu_count

———–

16

And to fool SQL Server about the size of the table, we could change the statistics of the clustered index of the table. For that, we can use the UPDATE STATISTICS and specify the RowCount and PageCount options.

   UPDATE STATISTICS DimProduct [PK_DimProduct_ProductKey] WITH RowCount = 500000, 

   PageCount = 100000

After run the update statistics our query starts to use parallelism to perform the reads and join using all CPUs in parallel.

   SELECT [ProductAlternateKey], [ProductSubcategoryKey]

   FROM DimProduct

   WHERE [ProductAlternateKey] = 'BA-8327'

   OR [ProductSubcategoryKey] = 12

   OPTION (RECOMPILE)