/* Environment reset


EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
GO

EXEC sp_configure 'max degree of parallelism', 0;
RECONFIGURE
GO

EXEC sp_configure 'cost threshold for parallelism', 5;
RECONFIGURE
GO

*/

USE Credit;
GO

-- Examining the plan
-- Check exchange operators and 
-- row counts by thread
SELECT	m.firstname,
		m.lastname,
		c.charge_no,
		c.charge_dt,
		SUM(c.charge_amt) max_amount
FROM dbo.member m
INNER JOIN dbo.charge c ON
	m.member_no = c.member_no 
WHERE	c.charge_amt > 1000.00 AND
		m.region_no = 2
GROUP BY 
	m.firstname,
	m.lastname,
	c.charge_no,
	c.charge_dt
HAVING MAX(c.charge_amt) > 2000.00
OPTION (RECOMPILE);
GO

-- NonParallelPlanReason?
SELECT	m.firstname,
		m.lastname,
		c.charge_no,
		c.charge_dt,
		SUM(c.charge_amt) max_amount
FROM dbo.member m
INNER JOIN dbo.charge c ON
	m.member_no = c.member_no 
WHERE	c.charge_amt > 1000.00 AND
		m.region_no = 2
GROUP BY 
	m.firstname,
	m.lastname,
	c.charge_no,
	c.charge_dt
HAVING MAX(c.charge_amt) > 2000.00
OPTION (RECOMPILE, MAXDOP 1);
GO

-- High cost threshold for parallelism?
EXEC sp_configure 'cost threshold for parallelism', 500;
RECONFIGURE
GO

SELECT	m.firstname,
		m.lastname,
		c.charge_no,
		c.charge_dt,
		SUM(c.charge_amt) max_amount
FROM dbo.member m
INNER JOIN dbo.charge c ON
	m.member_no = c.member_no 
WHERE	c.charge_amt > 1000.00 AND
		m.region_no = 2
GROUP BY 
	m.firstname,
	m.lastname,
	c.charge_no,
	c.charge_dt
HAVING MAX(c.charge_amt) > 2000.00
OPTION (RECOMPILE);

EXEC sp_configure 'cost threshold for parallelism', 5;
RECONFIGURE
GO

-- Scheduler affinity?
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU=0;
GO

SELECT *
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';
GO

SELECT	m.firstname,
		m.lastname,
		c.charge_no,
		c.charge_dt,
		SUM(c.charge_amt) max_amount
FROM dbo.member m
INNER JOIN dbo.charge c ON
	m.member_no = c.member_no 
WHERE	c.charge_amt > 1000.00 AND
		m.region_no = 2
GROUP BY 
	m.firstname,
	m.lastname,
	c.charge_no,
	c.charge_dt
HAVING MAX(c.charge_amt) > 2000.00
OPTION (RECOMPILE);

-- Cleanup
ALTER SERVER CONFIGURATION
  SET PROCESS AFFINITY CPU=AUTO;

SELECT *
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';
GO

-- Bonus section.  Why is the following scan not parallel?
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[member2]') AND name = N'member2PK')
ALTER TABLE [dbo].[member2] DROP CONSTRAINT [member2PK]
GO

ALTER TABLE [dbo].[member2] 
ADD  CONSTRAINT [member2PK] 
PRIMARY KEY NONCLUSTERED 
(
	[member_no] DESC -- No longer ASC!
) ON [PRIMARY]
GO

SELECT m1.lastname, m1.firstname
FROM dbo.member m1
INNER JOIN dbo.member2 m2 ON
	m1.member_no = m2.member_no
OPTION (RECOMPILE, QUERYTRACEON 8649);
GO

-- Cleanup
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[member2]') AND name = N'member2PK')
ALTER TABLE [dbo].[member2] DROP CONSTRAINT [member2PK]
GO

ALTER TABLE [dbo].[member2] 
ADD  CONSTRAINT [member2PK] 
PRIMARY KEY NONCLUSTERED 
(
	[member_no] ASC
) ON [PRIMARY]
GO

