際際滷

際際滷Share a Scribd company logo

Resource Governor in sql server 2008

Bora Choi
Bora Choi

MS SQL Server 襴り襴蠍磯

1 of 13
Ebay Korea DBA Team 豕覲企
RESOURCE GOVERNOR 覈 DBA SQL Server instances Resource 朱 蠍磯ゼ . [ IT ] [ Other departments ] [ Customer ] 觜 蟲豢 豕 焔レ 覓, 螻, 襷蟆, Sales Web Report 願 . 褐 焔レ timed out 覦 覿襷 Service襯 襷 Data 覿 螳 譟壱襯
RESOURCE GOVERNOR . RESOURCE GOVERNOR 螳 蠍 朱 覦覯 Resource 襯 襷 語 Kill SET QUERY_GOVERNOR_COST_LIMIT 旧 朱 覿螳 襷 覓企ゼ 覲 Server 轟 Instance 襦 覿襴 Peak load Time 覿襯 譯殊 蠍 暑 Peak load Time 轟 蠏碁９ Password 覲蟆曙朱 谿 襦 覓 螻 煙 蠍壱讌 覈詩蟆
What is The RESOURCE GOVERNOR ? RESOURCE 觜 讌, SQL Server 螻 襴 蟯襴襯 SQL 2008 蠍一 [ 襴 覓語 ] Run-away queries on the Server 蠍一 ろ 一 蟆一 矩 企 覦朱 蟲覲 伎 (HostName, App Name.., User) [ 襴 蟯襴 ] Database 讌朱 ( IS, RS, AS 覈詩) SQL Server Instance 螳 蟯襴 CPU 螻 SQL Server 蟯襴 覃覈襴襷
RESOURCE GOVERNOR 蟲煙 Resource Pools 覯 覓朱Μ 襴 Internal Pool 覲蟆/ X, るジ Pool 覦讌 螻 . ( 企 Pool 螻 覿企 願) Default Pool 覲蟆 0 Pool WorkGroup 螳 豌 覿襯 譟郁唄 磯 語 豌 貉企 . internal Group, Default Group Classification RESOURCE GOVERNOR 語 覿襯襯 讌. 覿襯蠍 伎 覿襯 螳 . 覿襯 語 焔 襷 .
RESOURCE GOVERNOR 襦語
RESOURCE GOVERNOR- Resource Pools CPU, 覃覈襴 MIN MAX れ 覈 MIN 螳 覯 襴 100% 豐螻 , MAX 螳 MIN螻 100% 伎 螳 れ. MAX% = min(x, 100-sum(min%)), 螻磯 螻旧% = MAX% - MIN% Pool MIN % MAX % MAX% 螻磯 螻旧 % る Internal 0 100 100 0 企 Pool 覈詩 Default 0 100 30 30 Min( 100, 100-(20+50)), max-min =30-0 Pool 1 20 100 50 30 Min(100,100-50), 50-20 = 20 Pool 2 50 70 70 20 Min(70, 100-20), 70-50 = 30
RESOURCE GOVERNOR- Resource Pools 伎 Pool MIN % MAX % MAX% 螻磯 螻旧 % る Internal 0 100 100 0 Default 0 100 Pool 1 20 100 Pool 2 50 70 Pool 3 5 100 矩 Pool MIN 0 %, MAX 100 % 朱? 矩 Pool MIN 100% る ?
Step Create resource Pools Create Workload Groups Create classifier function Enable resource Governor Monitoring
DEMO
企 ? OLTP 蟆曙 貎朱Μ 讌 螳 襷れ 讌ъ朱襦 企ゼ 襦 る CPU 譯 讌 . 企 誤 CPU 覦 糾螳 螻 . DBA SQL Server 襯 誤蠍 讌 貎朱Μ襯 . 企 貎朱Μ螳 覯 覃覈 襴 轟 襴るゼ 讌 蟆 . SQL 2008 豢 覦煙 螳ロ. 豢 CPU 襦螳 豢螳 豌 CPU 覿襯 譯 蠍 .. 轟 れ & APP 語 伎 覃覈襴, CPU襯 覈 谿讌. るジ 觜れ レ 譯手鍵 , 覃覈襴, cpu (GINDEXDB.. ) 轟 Report 蠍磯蓋 襴 蠏碁９ 螳 襴 一 襯 螳襦 .
覈磯 -- 襴り襴category view -- veiw any definition, 覲蟆曙control server select * from sys.resource_governor_configuration with (nolock) SELECT classifier_function_id ,object_schema_name(classifier_function_id) AS 'Classifier UDF schema' ,object_name(classifier_function_id) AS 'Classifier UDF name' ,is_enabled FROM sys.resource_governor_configuration GO -- resource pool info select * from sys.resource_governor_resource_pools with (nolock) select * from sys.resource_governor_workload_groups with (nolock) -- 襴り襴襯朱蟆渚蟲焔誤覿 select * from sys.dm_resource_governor_configuration with (nolock) select * from sys.dm_resource_governor_workload_groups with (nolock) select * from sys.dm_resource_governor_resource_pools as with (nolock) select p.pool_id, p.name, p.statistics_start_time , p.total_cpu_usage_ms,p.used_memgrant_kb, p.active_memgrant_kb , p.min_cpu_percent, p.max_cpu_percent, p.min_memory_percent, p.max_memory_percent , g.name, g.importance,g.total_cpu_usage_ms, g.max_dop , g.total_query_optimization_count, g.active_parallel_thread_count from sys.dm_resource_governor_resource_pools as p with (nolock) join sys.dm_resource_governor_workload_groups as g with (nolock) on p.pool_id = g.pool_id order by p.pool_id
谿瑚 襭 BOL, MSDN, BLOGS http://msdn.microsoft.com/ko-kr/library/bb933866.aspx http://blogs.technet.com/sqlos/archive/2007/12/14/part-1-anatomy-of-sql-server-2008- resource-governor-cpu-demo.aspx http://blogs.msdn.com/psssql/archive/2008/01/10/sql-server-2008-resource-governor- questions.aspx

More Related Content

Resource Governor in sql server 2008

  • 1. Ebay Korea DBA Team 豕覲企
  • 2. RESOURCE GOVERNOR 覈 DBA SQL Server instances Resource 朱 蠍磯ゼ . [ IT ] [ Other departments ] [ Customer ] 觜 蟲豢 豕 焔レ 覓, 螻, 襷蟆, Sales Web Report 願 . 褐 焔レ timed out 覦 覿襷 Service襯 襷 Data 覿 螳 譟壱襯
  • 3. RESOURCE GOVERNOR . RESOURCE GOVERNOR 螳 蠍 朱 覦覯 Resource 襯 襷 語 Kill SET QUERY_GOVERNOR_COST_LIMIT 旧 朱 覿螳 襷 覓企ゼ 覲 Server 轟 Instance 襦 覿襴 Peak load Time 覿襯 譯殊 蠍 暑 Peak load Time 轟 蠏碁９ Password 覲蟆曙朱 谿 襦 覓 螻 煙 蠍壱讌 覈詩蟆
  • 4. What is The RESOURCE GOVERNOR ? RESOURCE 觜 讌, SQL Server 螻 襴 蟯襴襯 SQL 2008 蠍一 [ 襴 覓語 ] Run-away queries on the Server 蠍一 ろ 一 蟆一 矩 企 覦朱 蟲覲 伎 (HostName, App Name.., User) [ 襴 蟯襴 ] Database 讌朱 ( IS, RS, AS 覈詩) SQL Server Instance 螳 蟯襴 CPU 螻 SQL Server 蟯襴 覃覈襴襷
  • 5. RESOURCE GOVERNOR 蟲煙 Resource Pools 覯 覓朱Μ 襴 Internal Pool 覲蟆/ X, るジ Pool 覦讌 螻 . ( 企 Pool 螻 覿企 願) Default Pool 覲蟆 0 Pool WorkGroup 螳 豌 覿襯 譟郁唄 磯 語 豌 貉企 . internal Group, Default Group Classification RESOURCE GOVERNOR 語 覿襯襯 讌. 覿襯蠍 伎 覿襯 螳 . 覿襯 語 焔 襷 .
  • 7. RESOURCE GOVERNOR- Resource Pools CPU, 覃覈襴 MIN MAX れ 覈 MIN 螳 覯 襴 100% 豐螻 , MAX 螳 MIN螻 100% 伎 螳 れ. MAX% = min(x, 100-sum(min%)), 螻磯 螻旧% = MAX% - MIN% Pool MIN % MAX % MAX% 螻磯 螻旧 % る Internal 0 100 100 0 企 Pool 覈詩 Default 0 100 30 30 Min( 100, 100-(20+50)), max-min =30-0 Pool 1 20 100 50 30 Min(100,100-50), 50-20 = 20 Pool 2 50 70 70 20 Min(70, 100-20), 70-50 = 30
  • 8. RESOURCE GOVERNOR- Resource Pools 伎 Pool MIN % MAX % MAX% 螻磯 螻旧 % る Internal 0 100 100 0 Default 0 100 Pool 1 20 100 Pool 2 50 70 Pool 3 5 100 矩 Pool MIN 0 %, MAX 100 % 朱? 矩 Pool MIN 100% る ?
  • 9. Step Create resource Pools Create Workload Groups Create classifier function Enable resource Governor Monitoring
  • 10. DEMO
  • 11. 企 ? OLTP 蟆曙 貎朱Μ 讌 螳 襷れ 讌ъ朱襦 企ゼ 襦 る CPU 譯 讌 . 企 誤 CPU 覦 糾螳 螻 . DBA SQL Server 襯 誤蠍 讌 貎朱Μ襯 . 企 貎朱Μ螳 覯 覃覈 襴 轟 襴るゼ 讌 蟆 . SQL 2008 豢 覦煙 螳ロ. 豢 CPU 襦螳 豢螳 豌 CPU 覿襯 譯 蠍 .. 轟 れ & APP 語 伎 覃覈襴, CPU襯 覈 谿讌. るジ 觜れ レ 譯手鍵 , 覃覈襴, cpu (GINDEXDB.. ) 轟 Report 蠍磯蓋 襴 蠏碁９ 螳 襴 一 襯 螳襦 .
  • 12. 覈磯 -- 襴り襴category view -- veiw any definition, 覲蟆曙control server select * from sys.resource_governor_configuration with (nolock) SELECT classifier_function_id ,object_schema_name(classifier_function_id) AS 'Classifier UDF schema' ,object_name(classifier_function_id) AS 'Classifier UDF name' ,is_enabled FROM sys.resource_governor_configuration GO -- resource pool info select * from sys.resource_governor_resource_pools with (nolock) select * from sys.resource_governor_workload_groups with (nolock) -- 襴り襴襯朱蟆渚蟲焔誤覿 select * from sys.dm_resource_governor_configuration with (nolock) select * from sys.dm_resource_governor_workload_groups with (nolock) select * from sys.dm_resource_governor_resource_pools as with (nolock) select p.pool_id, p.name, p.statistics_start_time , p.total_cpu_usage_ms,p.used_memgrant_kb, p.active_memgrant_kb , p.min_cpu_percent, p.max_cpu_percent, p.min_memory_percent, p.max_memory_percent , g.name, g.importance,g.total_cpu_usage_ms, g.max_dop , g.total_query_optimization_count, g.active_parallel_thread_count from sys.dm_resource_governor_resource_pools as p with (nolock) join sys.dm_resource_governor_workload_groups as g with (nolock) on p.pool_id = g.pool_id order by p.pool_id
  • 13. 谿瑚 襭 BOL, MSDN, BLOGS http://msdn.microsoft.com/ko-kr/library/bb933866.aspx http://blogs.technet.com/sqlos/archive/2007/12/14/part-1-anatomy-of-sql-server-2008- resource-governor-cpu-demo.aspx http://blogs.msdn.com/psssql/archive/2008/01/10/sql-server-2008-resource-governor- questions.aspx