The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P125: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 602 Chapter 13 Performance Tuning 0 DMVs can be server-scoped or database-scoped reflecting the data tracked within the DMV 0 DMVs run continually in the background with a very low overhead data provided is either cumulative since last service restart or a snapshot view. Partitioning 0 Partitioning can be used to split a large table into a series of smaller tables transparent to the application. 0 Horizontal partitioning can be used to split a table into several smaller tables with all columns and a smaller number of rows often by date . 0 Vertical partitioning divides a large table into several smaller tables each with all rows but a subset of columns. Performance Data Collection Data Collector 0 The Performance Data Collection allows collection of performance data from a number of servers to a central data warehouse for analysis and reporting. 0 Performance data is stored in the Management Data Warehouse MDW which is a relational database intended to be an organization-wide repository for performance data. 0 Data collection is the process of harvesting performance data from target servers to store in the MDW . Resource Governor 0 Resource Governor provides capability to identify inbound connections to SQL Server and provide these connections with minimum and maximum resource Memory CPU allocation. 0 Resource pools are used to control physical server resources while workload groups map users groups to resource pools the classifier functions are used to assign users to workload groups. 0 The Dedicated Admin Connection DAC is not subject to the resource governor. Performance Tuning Chapter 13 603 Exam Objectives Frequently Asked Questions Q What s happening when SQL Profiler reports missing events A This happens occasionally with very busy servers when tracing to a table or to SQL Profiler. In this situation the SQL Profiler tool is unable to keep up with the volume of events that are generated. Consider using a server side trace and saving the trace to a flat file
