#sqlservermyth cost threshold for parallelism = seconds

“It’s like a lion and a tiger mixed… bred for its skills in magic.”

The definition of cost threshold for parallelism is similar to this definition of a Liger. It is based on fact but when retold it magically becomes inaccurate. “The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.” The key words are the last five. They are intended to tell you that the calculation for estimating seconds is based on the characteristics of some SQL Server which probably will not match your current, past or future SQL Servers.

Image003

So what time is it?

Try executing the script below on a SQL Server with its cost threshold for parallelism value set between 1 and 24. You should find that querying 33487 records (each requiring one logical read) will not parallelize but querying 33488 records will parallelize. It has already been executed on several different SQL Server versions with varying hardware characteristics and the same result occurred each time. Attempts were also made to try and cause parallelizing to occur with 33487 records but increased CPU due to calculations of math functions but the attempts were unsuccessful. Let us temporarily ass/u/me that IO is the only differentiator to cause parallelism even though that is not the case. If it were true then it would mean the time it takes your SQL Server to perform 33488 reads is what time a query would need to take before parallelism will be used. At threshold 25 the reads breakpoint was 33670 and at threshold 45 the reads breakpoint was 60608.

(NOTE: this was an overly simplistic test for a feature as complex as parallel execution plans but hopefully it still proves the intended point)

 

Image005

Myth Busting

Tony Davis : SQL Server Myths

Demystifying SQL Server: SQL Server Myths Debunked (Part 1)

Demystifying SQL Server: SQL Server Myths Debunked (Part 2)

CommonSQLServerMyths.pdf  <a pox on your house! if you do not read all 59 pages>

T-SQL Tuesday #11 Round up, Misconceptions in SQL Server « « Sankar ReddySankar Reddy

T-SQL Tuesday: Common SQL Server myths – Series I–Setup and Services « TroubleshootingSQL

Common SQL Server myths – Series II – SQL Memory « TroubleshootingSQL

My Favourite SQL Server Myths and Realities – LivingForSqlServer – SQLServerCentral.com

 

Image007

People

Tony Davis

Adam Haines

Paul Randal

Sankar Reddy

Amit Banerjee

Ramkumar Gopal

 

Image009

Script

SET NOCOUNT ON;

GO

SET ROWCOUNT 0;

GO

CREATE DATABASE [DELETEME] ON

                PRIMARY (NAME = N’DELETEME_data’, FILENAME =N’C:\Temp\DELETEME_data.mdf’, SIZE = 16384KB , FILEGROWTH =16384KB)

                LOG ON (NAME = N’DELETEME_log’, FILENAME =N’C:\Temp\DELETEME_log.ldf’, SIZE = 16384KB , FILEGROWTH =16384KB);

GO

ALTER DATABASE [DELETEME] SET AUTO_CREATE_STATISTICS OFF;

GO

ALTER DATABASE [DELETEME] SET AUTO_UPDATE_STATISTICS OFF;

GO

ALTER DATABASE [DELETEME] SET RECOVERY FULL;

GO

ALTER DATABASE [DELETEME] SET MULTI_USER;

GO

USE [DELETEME];

GO

CREATE TABLE [dbo].[JUNK] (TRASH CHAR(8000) NOT NULL DEFAULT SPACE(8000));

GO

INSERT INTO [dbo].[JUNK] VALUES (DEFAULT);

GO

INSERT INTO [dbo].[JUNK] SELECT * FROM [dbo].[JUNK];

GO 15

DECLARE @MORE_ROWS INT;

SELECT @MORE_ROWS = 33487  COUNT(*) FROM [dbo].[JUNK];

SET ROWCOUNT @MORE_ROWS

INSERT INTO [dbo].[JUNK] SELECT * FROM [dbo].[JUNK];

GO

CHECKPOINT;

GO

SET ROWCOUNT 0;

GO

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

GO

PRINT ‘PREDICTING WITHOUT PARALLELISM’;

GO

SET SHOWPLAN_ALL ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET SHOWPLAN_ALL OFF;

GO

SET STATISTICS IO ON;

GO

SET STATISTICS TIME ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET STATISTICS IO OFF;

GO

SET STATISTICS TIME OFF;

GO

PRINT ‘ADDING JUST 1 MORE ROW’;

GO

INSERT INTO [dbo].[JUNK] VALUES (DEFAULT);

GO

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

GO

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

GO

PRINT ‘PREDICTING WITH PARALLELISM’;

GO

SET SHOWPLAN_ALL ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET SHOWPLAN_ALL OFF;

GO

SET STATISTICS IO ON;

GO

SET STATISTICS TIME ON;

GO

SELECT DISTINCT 0 FROM [dbo].[JUNK];

GO

SET STATISTICS IO OFF;

GO

SET STATISTICS TIME OFF;

GO

DROP TABLE [dbo].[JUNK];

GO

USE [master];

GO

DROP DATABASE [DELETEME];

GO

Bustin’ makes me feel good

Advertisements

About Robert Matthew Cook

Hello, my name is Robert Matthew Cook. This blog is autoposted to from my main blog at www.sqlmashup.com. For more profile information or to leave a comment, please visit me there.
This entry was posted in Uncategorized. Bookmark the permalink.