Transcript We have control!

Chris Testa-O’Neill
EMC Consulting
Agenda
 Resource Governor
 Integrating Performance Monitor and Profiler
 Policy Based Management
 Performance Data Collector
Who am I
 Practise Consultant for EMC Consulting
 UK SQL Server Organiser for Manchester and Leeds
 SQL Bits Organiser
 Microsoft Elearning Author courses for SQL Server
2008
 Fill out feedback to win a free Elearning voucher
 SQL Server MVP and MCT
Resource Governor
 Monitor and manage the workloads on your SQL Server
system
 Enterprise Edition feature only
 Control CPU and memory usage specifically
 Works well with applications of similar requirements
 System Monitor Counters available
 DAC connection not subject to resource governor
Resource Governor Components
 Resource Pools
 represents the physical resources of the server
 Internal and default
 Workload Groups
 serves as a container for session requests that are similar
 Classification Function
 a set of user-written criteria contained in a function.
Demonstration
Using Resource Governor
Demonstration
 Integrating Performance Monitor with SQL Server
Profiler
Policy Based Management
 enforce the configuration of SQL Server
 on all editions for single instance
 Enterprise and Standard for multiple instances
 Can be used to centrally managed multiple servers
 Can export and import policies
 Can evaluate policies
Policy Based Management
Components
Component
Description
Policy
A SQL object that holds the information required to enforce a
policy
Facet
A facet is an object within a policy that is used to represent a
SQL Server component
Target
A SQL Server object such as an Instance or a database to which
the policy is applied to
Condition
Specifies a set of allowed states of a Policy-Based Management
managed target with regard to a facet.
How Policies are enforce
 Manually
 Scheduled
 On change prevent – (DDL triggers)
 On change log only – (event notifications)
Demonstration
 Policy Based Management
Performance Data Collector
 Stores information about different sets of data across
your database servers
 Stored in a relational database known as a
management data warehouse.
 Displays reports about queries, system and disk usage.
 Custom performance data collectors can be created
Data collector components
Component
Description
Target
An instance of the Database Engine used to manage
performance data collector (PDC)
Target type
Object that the PDC collects information (instance,
database)
data provider
Provides data connection information for the PDC
collector type
SSIS packages used to collect and upload performance data
collection item
An instence of a collector types used to collect information
collection set
A collection set is a unit of data collection that a user can
interact with
collection mode
The manner in which the data is collected and stored
management
data warehouse
A relational database used to store collected data
Create a Management Data
Warehouse
 Wizard driven
 Create Logins and Map Them to Data Collector Roles
 specific roles for data collection and management data
warehouse tasks
 Configure the Management Data Warehouse
 Create an new database or use an existing database
 Create Proxies
 proxies can be used for data collection and upload to the Data
Management Warehouse
Disk Usage Activity collection sets
 This collection set gathers the following data:
 Snapshots of data file sizes obtained from
sys.partitions and sys.allocation_units.
 Snapshots of log file sizes obtained from DBCC
SQLPERF (LOGSPACE).
 Snapshots of I/O statistics from
sys.dm_io_virtual_file_stats.
Server Activity collection sets
 This collection sets collects data samples from the
following sources:
 sys.dm_os_wait_stats
 sys.dm_os_latch_stats
 sys.dm_os_schedulers
 sys.dm_exec_sessions , sys.dm_exec_requests,
sys.dm_os_waiting_tasks (using a joined query)
 sys.dm_os_process_memory
 sys.dm_os_memory_nodes
Query Activity collection sets
 Query Statistics collection set gathers data about
query statistics and individual query text, query plans,
and specific queries
Custom Data Collectors
 Have the ability to create your own
 Use SQL Server Profiler
Demonstration
 Create a Management Data Ware house
 View system data collectors
 Create a custom data collector