#眉標=SQL Server 2008
#副標=SQL Server 2008新功能概覽（4）
#大標=保障運算效能與分析
#作者=文／胡百敬


===<反灰>=============
程式1
-- 建立登入帳號，以測試不同帳號登入(login)時，擁有不同的資源使用量
CREATE LOGIN UserMarketing WITH PASSWORD = 'UserMarketingPwd', CHECK_POLICY = OFF
CREATE LOGIN UserExecutive WITH PASSWORD = 'UserExecutivePwd', CHECK_POLICY = OFF
GO

-- 在範例資料庫 adventureworks 加入對應的 user
USE AdventureWorks
GO

CREATE USER UserMarketing FOR LOGIN UserMarketing
CREATE USER UserExecutive FOR LOGIN UserExecutive
GO

EXEC sp_addrolemember 'db_owner', 'UserMarketing'
EXEC sp_addrolemember 'db_owner', 'UserExecutive'
==<end>==============




===<反灰>=============
程式2
USE [master]
GO
-- 建立資源集區
CREATE RESOURCE POOL PoolExecutive
CREATE RESOURCE POOL PoolMarketing
-- 建立負載群組
CREATE WORKLOAD GROUP GroupExecutive USING PoolExecutive
CREATE WORKLOAD GROUP GroupMarketing USING PoolMarketing
==<end>==============





===<反灰>=============
程式3
USE [master]
GO
-- 建立分類函數
CREATE FUNCTION CLASSIFIER_V1()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
	--透過使用者登入帳號名稱來區分，回傳資源群組名稱
	DECLARE @val varchar(32)
	if  'UserMarketing' = SUSER_SNAME() 
		SET @val = 'GroupMarketing';
	else if 'UserExecutive' = SUSER_SNAME()
		SET @val = 'GroupExecutive';
	return @val;
END
GO
-- 讓資源管理員使用分類函數
ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_V1)
GO
-- 讓設定的變更啟動
ALTER RESOURCE GOVERNOR RECONFIGURE
==<end>==============




===<反灰>=============
SELECT * FROM sys.dm_resource_governor_workload_groups
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_configuration
==<end>==============





===<反灰>=============
程式4
:Endless
	sqlcmd -S sql08demo -U UserExecutive -P UserExecutivePwd -d AdventureWorks -i Executive.sql > NUL
GOTO Endless
rem start sqlcmd -U UserExecutive -P UserExecutivePwd -d AdventureWorks -Q "WHILE 1=1 SELECT * FROM Sales.SalesOrderDetail ORDER BY OrderQty,UnitPrice"
==<end>==============




===<反灰>=============
WITH ProductSales(ProductID, OrderYear, OrderTotal) AS (
      SELECT det.productID, YEAR(hdr.orderdate), det.linetotal
      FROM sales.salesorderdetail det JOIN sales.salesorderheader hdr 
   ON det.salesorderid = hdr.salesorderid )
   SELECT 
      ProductSalesPivot.productID, 
      Total_Sales_2001 = ISNULL([2001], 0), 
      Total_Sales_2002 = ISNULL([2002], 0), 
      Total_Sales_2003 = ISNULL([2003], 0), 
      Total_Sales_2004 = ISNULL([2004], 0)
   FROM ProductSales PIVOT ( SUM(OrderTotal)
      FOR OrderYear IN ([2001], [2002], [2003], [2004])
   ) AS ProductSalesPivot
   ORDER BY ProductSalesPivot.ProductID
==<end>==============




===<反灰>=============
-- 設定集區使用 cpu 的百分率
ALTER RESOURCE POOL PoolMarketing
WITH (MAX_CPU_PERCENT = 10)

-- 啟動設定
ALTER RESOURCE GOVERNOR RECONFIGURE
==<end>==============