Parallelism demo

Parallelism-grants.sql

/* Take batch mode & adaptive grants out of the equation: */
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130
GO
/* Set up a couple of indexes to help,
and to give SQL Server some good statistics: */
CREATE INDEX Location ON dbo.Users(Location)
	WITH (ONLINE = OFF, MAXDOP = 0);

CREATE INDEX OwnerUserId ON dbo.Posts(OwnerUserId)
	WITH (ONLINE = OFF, MAXDOP = 0);
GO

/* Find the top-scoring posts from Jon Skeet's hometown: */
SELECT TOP 101 *
FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
WHERE u.Location = N'Reading, United Kingdom'
ORDER BY p.Score DESC
OPTION (MAXDOP 2, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

/* Go a little more parallel: */
SELECT TOP 101 *
FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
WHERE u.Location = N'Reading, United Kingdom'
ORDER BY p.Score DESC
OPTION (MAXDOP 4, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

/* A little more: */
SELECT TOP 101 *
FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
WHERE u.Location = N'Reading, United Kingdom'
ORDER BY p.Score DESC
OPTION (MAXDOP 8, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

/* Sweet 16: */
SELECT TOP 101 *
FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
WHERE u.Location = N'Reading, United Kingdom'
ORDER BY p.Score DESC
OPTION (MAXDOP 16, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

/* Bitter 32: */
SELECT TOP 101 *
FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
WHERE u.Location = N'Reading, United Kingdom'
ORDER BY p.Score DESC
OPTION (MAXDOP 32, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

/* Wild and crazy 64 cores: */
SELECT TOP 101 *
FROM dbo.Users u
	INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
WHERE u.Location = N'Reading, United Kingdom'
ORDER BY p.Score DESC
OPTION (MAXDOP 64, USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));