Início > Não categorizado > Fooling Statistics and Number of CPUs v1

Fooling Statistics and Number of CPUs v1

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.

clip_image001

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:

clip_image002

Error Log file after restart SQL Server:

clip_image003

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)
Anúncios
Categorias:Não categorizado
  1. Nenhum comentário ainda.
  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: