Management of data warehouse (performance)

Management of data warehouse (performance)

• Greg Low (again)

Agenda

• Show
  • Definition of the new tools and features
  • Demonstrations
• Flow
  • Explanation of how the features work
  • Demonstration of the features components
• Know and Go
  • Internals and architecture
  • Planning and Implementation

SQL Sever 2008 Performance Monitoring Tools

• Data Collector
• Management Data Warehouse
• Performance and Configuration* Reports
• Activity Monitor
• Graphical Showplan*
• SQL Profiler*
• Dynamic Management Views*
• Resource Governor (Enterprise Only)
• XEvents
• Database Engine Tuning Advisor (Enterprise Only)*

*=Enhanced Feature Take a snapshot (copy) of data

• from system data management views
• into a static copy
• e.g. sys.dm_exec_sessions

Activity Monitor (is really sweet)

• Processes
• Resource Waits
• Data File I/O
• Recent Expensive Queries

Data Collection and Evaluation Components

• SQL2K8 Engine Windows Service (Data Collector)
• SQL2K8 msdb
• Etc (missed it)

Data Collector Internals and Architecture

• Data Provider (Collection Item)
  • TSQL
  • Performance Counters (e.g. sys.dm_os_performance_counters)
  • Trace Definition
• Collection Set
  • Msdb Database tables
  • Linked to Agent jobs
• SSIS
• Management Data Warehouse

Planning Tips and Tricks

• Think Central
  • Collecting to a central MDW keeps you from monitoring the collection itself
  • Using a “Central Management Server” makes…
• Watch the Clock
• Space, the Final Frontier

Performance Monitoring Tools Roadmap

• Data Collection
  • Data Collection Sets
• Performance and Diagnostics Monitoring
  • System Collection Sets Reports
• Historical and baseline comparisons
  • Management Data Warehouse going forward
• Trouble-shooting and Tuning
  • Policy based management