SQL Server 2008 Performance Data Collection - PowerPoint PPT Presentation

Loading...

PPT – SQL Server 2008 Performance Data Collection PowerPoint presentation | free to view - id: 5cb6e-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

SQL Server 2008 Performance Data Collection

Description:

Data providers: Transact-SQL, SQL Trace, Performance Monitor Counters, DMVs, logs ... Disk Usage Data Files: Transact-SQL data collection in non-cached mode ... – PowerPoint PPT presentation

Number of Views:107
Avg rating:3.0/5.0
Slides: 31
Provided by: hhog
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: SQL Server 2008 Performance Data Collection


1
SQL Server 2008 Performance Data Collection
David R. Ford Senior Consultant dford_at_hhogdev.com
2
Module Overview
  • Management data warehouse
  • Performance data collection
  • Performance data collection components
  • System collection sets
  • User-defined collection sets
  • Reporting
  • Centralized administration bringing it all
    together
  • Performance data collection and reporting

3
Management Data Warehouse
  • Centralized database for data collection
  • Supports collection from multiple SQL 2008
    servers
  • Host database on separate server so you dont end
    up collecting data on the data collector itself
  • Use standard database maintenance/management
  • Data/Log sizes should be pre-allocated to
    minimize auto-growth data estimates are
    250-350MB per day/per DB (data 100MB with 50MB
    auto-growth, log 10MB with 10MB auto-growth
    through MDW wizard)
  • Data/Log on separate drives (default data drive
    through wizard)
  • Recovery model should reflect backup/restore
    strategy (SIMPLE through wizard)
  • May want to pre-create a more custom/optimized
    database and select this database rather than
    create it as part of the MDW wizard

4
Management Data Warehouse Wizard
  • Before running the wizard
  • Determine centralized server/database for data
    collection
  • Start the SQL Server Agent on the server where
    data collection is being defined
  • Determine the roles of your team members in
    performance data management
  • Execute the wizard to
  • Setup Management Data Warehouse database
  • Setup local cache directory for cached data
    collection
  • Map Logins and Users and define roles
  • mdw_admin
  • mdw_reader
  • mdw_writer
  • It must be run on the instance storing the MDW,
    and on the instance where collection will be
    performed

5
Cache Directory
  • Stores frequently gathers data points but uploads
    them as a second and separate step (data
    collection will have 2 jobs)
  • Local to the server on which data collection is
    performed
  • Defaults to local temp directory
  • If directory is changed, make sure that the local
    SQL Server Agent has rights to read/write to this
    directory all cached data collection will fail
    (Server Activity and Query Statistics are cached
    by default in CTP6)
  • Files stored in the cache directory follow these
    naming conventions
  • servername_mssql10_instance_GUID_.cache

6
Log File Viewer Data Collection
7
Management Data Warehouse Setup
8
Module Overview
  • Management data warehouse
  • Performance data collection
  • Data Collection Components
  • System Collection Sets
  • User-Defined Collection Sets
  • Reporting
  • Centralized administration bringing it all
    together

9
What Runs Where?
2
Data Collection View Logs Data collection logs
as well as job history and SQL Agent logs
msdb
msdb
msdb
msdb
Target Servers Data collection started by running
MDW wizard
Jobs
Jobs
Jobs
Jobs
Schedules
Schedules
Schedules
Schedules
dir\.cache files
dir\.cache files
dir\.cache files
dir\.cache files
4
1
Reports Right-click for historical reports in
each system collection category
mdw
3
Schemas Core Tables, views, and stored
procedures for organizing and identifying
collected data Snapshots store the data
collected by the system data collection
sets Custom_snapshots this schema does not exist
until custom data collection is defined and
started
Centralized Server Master Server (MSX) Central
Management Server Policy Store for
centralization Management Data Warehouse Database
Mirroring Witness Reporting Server
10
Performance Data Collection Components
  • Targets servers
  • Collector types disk, query, server statistics
  • Data providers Transact-SQL, SQL Trace,
    Performance Monitor Counters, DMVs, logs
  • Collection item properties that define input and
    collection frequency
  • Collection set the grouping with which the
    UI/tools allow interaction and control
  • Job/schedule stored as regular msdb jobs with
    new predefined schedules for simplicity

11
System Data Collection
  • Disk Usage (retained for 730 days)
  • Disk Usage Data Files Transact-SQL data
    collection in non-cached mode gathered and
    immediately uploaded every 6 hours
  • Disk Usage Log Files Transact-SQL data
    collection in non-cached mode gathered and
    immediately uploaded every 6 hours
  • Query Statistics (retained for 14 days)
  • Query Activity special Query Activity collector
    type in cached mode gathered every 10 seconds
    and uploaded every 15 minutes
  • Server Activity (retained for 14 days)
  • DMV Snapshots Transact-SQL data collection in
    cached mode gathered every 60 seconds and
    uploaded every 15 minutes
  • Performance Counters Performance Monitor
    collection in cached mode gathered every 60
    seconds and uploaded every 15 minutes

12
Touring System Data Collection
13
Custom Data Collection
  • USE msdb… sp_syscollector_create_collection_set… s
    p_syscollector_create_collection_item…
  • Query has tabular definition (dont forget column
    names if using functions)
  • Define output table (result becomes
    mdw.custom_snapshot.outputtable in MDW)
  • Data Collection definition in xml structure
  • Use system data collection set as a template
    (right-click and select ?Script Data Collection
    as, CREATE To, New Query Editor Window)

14
Custom Data Collection
15
Data Collection Reporting
  • Historical reports are heavily cross-linked
  • Drill-through supports deeper insight
  • Disk Usage gives trends
  • Query Statistics History gives most expensive
    queries by CPU, Duration, Physical Reads,
    Logical Writes
  • Server Activity History has multiple sections
  • SQL Server v. System performance for CPU
    Utilization, Memory Usage, Disk I/O Usage,
    Network Usage
  • SQL Server Waits Logging, Latches, Locking, CPU,
    Network I/O, Buffer, …
  • SQL Server Activity Batch Requests/sec.,
    Logouts/sec, SQL Re-Compilations/sec, User
    Connections, Transactions, …

16
Disk Usage Collection Set
17
Disk Usage Collection Set Database
18
Disk Usage Standard Reports
19
Query Statistics
  • Navigation Controls
  • Navigation Legend
  • white no data collected gray data
    collected teal blue collected data over which
    the report is displayed

20
Query Usage Standard Reports
21
Server Activity History
  • Navigation Controls
  • Navigation Legend
  • white no data collected gray data
    collected teal blue collected data over which
    the report is displayed

22
Server Activity Standard Reports
23
Module Overview
  • Management data warehouse
  • Performance data collection
  • Centralized administration bringing it all
    together

24
Centralized Administration
  • Configure a robust, centralized server as
  • Central Management Server
  • Centralized Master Server setting all of the
    servers of a Central Management Server as targets
  • Centralized Performance Data Collection (MDW)
  • Centralized Policy Store
  • Always use a Management Data Warehouse thats
    centrally located (so that you dont get caught
    in collecting performance metrics on collection
    itself)
  • Downside to this is that reports are accessed
    locally despite the central MDW consider
    creating custom reports at the MDW to correlate
    the relationship between servers

25
Centralized Administration Performance Data
Collection and Reporting
Standard Performance Data Collection Reports
Generated these locally at the server where
collection is defined
Centralized Reporting Custom Performance Data
Collection reports can be written on a
centralized management data warehouse for better
server-to-server correlation
mdw
Policies
MSX
Central Management Server
Centralized Server Master Server (MSX) Central
Management Server Policy Store for
centralization Management Data Warehouse Database
Mirroring Witness Reporting Server
26
Module Review
  • Management data warehouse
  • Performance data collection
  • Performance data collection components
  • System collection sets
  • User-defined collection sets
  • Reporting
  • Centralized administration bringing it all
    together
  • Performance data collection and reporting

27
Training Resources
  • Microsoft Learning site
  • http//www.microsoft.com/learning/sql/2008.aspx
  • TechNet Site
  • http//technet.microsoft.com/en-us/sqlserver/defau
    lt.aspx

28
SQL 2008 Upgrade Resource
  • Search Microsoft Download site for SQL Server
    2008 Upgrade Technical Reference Guide

Brief Description This 490-page document covers
the essential phases and steps to upgrade
existing instances of SQL Server 2000 and 2005 to
SQL Server 2008 by using best practices. These
include preparation tasks, upgrade tasks, and
post-upgrade tasks. It is intended to be a
supplement to SQL Server 2008 Books Online.
29
Questions and Answers
  • Feel free to ask questions about today's
    demonstration or any other questions that you may
    have about SQL Server 2008.

30
SQL Server 2008 Performance Data Collection
David R. Ford Senior Consultant dford_at_hhogdev.com
About PowerShow.com