Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - There are multiple performance troubleshooting tools which a DBA can use inside SQL Server

  • There are multiple performance troubleshooting tools which a DBA can use inside SQL Server from Perfmon, SQL Profiler and multiple third Party tools. One of the hidden features most forgotten is the Data Collector inside SQL Server. What is a Data Collector? What are the predefined collectors inside SQL Server which can be used?

    Posted on 01-29-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

7  Answers  

Subscribe to Notifications
  • Score
    9

    What is a Data Collector?

    The data collector is a component of SQL Server that collects different sets of data. Data collection either runs constantly or on a user-defined schedule. The data collector stores the collected data in a relational database known as the management data warehouse.

    The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.

    The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data that you collect by setting different retention periods for your data.

    The data collector supports dynamic tuning for data collection and is extensible through its API.

    The data collector is integrated with SQL Server Agent and Integration Services, and uses both extensively.

    Data Collector Architecture and Processing Plz click....

    Managing Data Collection Using Transact-SQL plz click....


    Collectors Inside SQL Server

    The data collector provides predefined collector types that you can use for data collection. The collector types provide the actual mechanism for collecting data and uploading it to the management data warehouse.

    GENERIC T-SQL QUERY COLLECTOR TYPE

    The Generic T-SQL Query collector type executes a user-provided Transact-SQL statement as an input parameter, saves the output from the query, and then uploads the output to the management data warehouse.

    This collector type is registered in the core.supportedcollectortypes view in the management data warehouse.

    This collector has the following input parameters:

    • Value The Transact-SQL query. You can provide more than one query as input.

    • OutputTable - Provide the name of the table for saving query results before they are uploaded to the management data warehouse.

    • Database - Specify the database or databases to run the query against. You can specify databases by name or use the * wildcard character to specify all the databases on the server. If you do not provide a value for Database, the query is run against all the system databases.

    GENERIC SQL TRACE COLLECTOR TYPE

    The Generic SQL Trace collector type uses SQL Trace to monitor the SQL Server Relational Engine. The trace data can come from a default trace or from one or more custom traces.

    This collector type is registered in the core.supportedcollectortypes view.

    The default trace, controlled by a server-wide setting, runs continuously on the server and captures general events of interest. These events of interest are not related to individual batch execution. This is a low-impact trace. Custom traces can collect any events and can have low or high impact, depending on which events are selected and the server activity at the time that the trace is running. In most cases, custom traces do not run continuously.

    The Generic SQL Trace collector type runs a server-side trace that stores data in a file or set of files. The trace data is obtained from trace files by using the fntracegettable() system function. If configured to do so, the collector processes the data and then uploads the data to the management data warehouse.

    The Generic SQL Trace collector type is configured to remove unused files and to keep a fixed amount of space for stored trace data.

    PERFORMANCE COUNTERS COLLECTOR TYPE

    You can use the Performance Counters collector type to collect specific performance counter information from a computer that is running SQL Server 2008. You can use this data later as a baseline for troubleshooting or for capacity planning.

    This collector type is registered in the core.supportedcollectortypes view.

    This collector type has the following input parameters:

    • Objects. The SQL Server objects running in an instance of SQL Server.

    • Counters. The counters associated with a SQL Server object.

    • Instances. The instances of the specified object.

    QUERY ACTIVITY COLLECTOR TYPE

    The Query Activity collector type is a custom collector type used by the Query Statistics collection set, one of the predefined system data collection sets.

    This collector type is used to collect query statistics and query activity information along with the query plan and query text for queries that meet predefined criteria. If you create your own collection set using this collector type, it will collect the same information as the Query Statistics collection set.

    For study you can click..... Collector Types


    The data collector provides an extensive collection of stored procedures that you can use to perform any data collection task. In addition, there are functions and views that you can use to get configuration data for the msdb and management data warehouse databases, execution log data, and data that is stored in the management data warehouse.

    For study Plz click......

    Thanks

    Yogesh

    Replied on Jan 29 2012 12:59AM  . 
    Yogesh Kamble
    141 · 1% · 349
  • Score
    3

    Data collector is an out of box feature which comes with SQL Server 2008/above versions.This feature deals with capturing performance data mainly using API's.The data is stored and manipulated using a database MDW(Management Data Warehouse).When configuring data collector we are allowed to create MDW or use existing database which can be used as MDW.

    Data collector collects 3 different types of data and this available for uses to review and report -

    1. Server activity
    2. Disk activity
    3. Query stats
    Replied on Jan 29 2012 3:33PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    8

    The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule. The data collector stores the collected data in a relational database known as the management data warehouse. The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.

    The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data that you collect by setting different retention periods for your data. The data collector supports dynamic tuning for data collection and is extensible through its API.

    The data collector API, in Microsoft.SqlServer.Management.Collector, allows programmatic control of all configuration operations through the object model. In addition, many of the data collection operations that use the API are implemented as stored procedures that are installed on the server.

    The following illustration shows key elements of the data collector object model.

    1. CollectorConfigStore
    2. CollectionSetCollection
    3. CollectionSet
    4. CollectionItemCollection
    5. CollectionItem

    The data collector installs three System Data collection sets during the SQL Server 2008 Setup process. These collection sets can be configured to suit your monitoring requirements but cannot be deleted. The System Data collection sets consist of the following:

    Disk Usage. Collects data about disk and log usage for all the databases installed on the system.

    Server Activity. Collects resource usage statistics and performance data from the server and SQL Server.

    Server Activity – Performance Counters

    Query Statistics. Collects query statistics, individual query text, query plans, and specific queries.

    alt text

    Usage:(The procedure that I have been used in my latest project)

    Viewing Data Collection Sets

    Connect to SQL Server in SSMS

    Expand Management folder and then expand Data Collection and then System Data collection sets

    You can find Disk Usage, Query Statistics, Server Activities and Utility Information.

    Just right click on the required set and then click on properties

    In the window you can find the details as shown below (Ive taken it for server activity collection set) In the first image Ive chosen DMV details , so you can get the queries used. In the second image Ive chosen to get performance counter details, so in the below pane it shows the counters used.

    Start or Stop Collection Set

    Connect to SQL Server in SSMS

    Expand Management folder and then expand Data Collection and then System Data collection sets

    You can find Disk Usage, Query Statistics, Server Activities and Utility Information.

    Just right click on the required set and then click on Start Data Collection Set or Stop Data Collection Set

    Viewing Data Collection Reports

    Connect to SQL Server in SSMS

    Expand Management folder and then right click on Data Collection , then click on Reports and then Management Data Warehouse and then click on the respective collection set.

    Replied on Jan 29 2012 11:53PM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    2

    A data collection is a container that allows you to specify what data to collect, when to collect it, and how long to keep it. It does this by using Data Collector.

    Data Collector can either be configured to run on your defined schedule or can run all the time and collect different sets of data pertaining to performance diagnostics, historical data for baseline comparisons, policy based data etc

    SQL Server 2008 provides three different system Data Collection definitions. These Data Collections are Disk Usage, Query Activity, and Server Activity.

    Replied on Jan 30 2012 12:08AM  . 
    Nirav
    37 · 5% · 1593
  • Score
    9

    The data collector is a component of SQL Server 2008 that collects different sets of data.
    Data collection either runs constantly or on a user-defined schedule.

    Data Collector is an optional feature of SQL Server 2008 that collects and stores information about SQL
    Server’s 2008’s status and performance over time, using pre-defined data collectors.

    • Once data is collected and stored in the Management data warehouse database, it can be used for baselining, planning, troubleshooting, performance tuning, and much more.
    • Built-in reports are included that help the DBA become more proactive

    The data collector supports dynamic tuning for data collection and is extensible through its API.

    The data collector provides an historical report for each of the System Data collection sets.
    Data Collector includes three data collection sets,
    which are used to collect data from targets and put it in the management data warehouse

    Benefits of the Data Collector

    • Easy to install and configure, and comes with 2008
    • By default, collects most of the key data needed by a DBA to resolve many different SQL Server-related problems, especially performance-related ones.
    • Allows you to customize what data is collected, in case you don’t like the default data collected
    • Provides pre-defined reports
    • Allows you to manually query, or create Reporting Services reports, on the historical data stored in the management data warehouse

    Limitations of the Data Collector

    • It can only collect data from SQL Server 2008 (not previous versions).
    • Customizing data collection and reports is not particularly easy
    • In most cases, a dedicated SQL Server instance is required, if many instances are to be monitored
    • Memory, CPU and disk I/O resources are consumed on the SQL Server instances being monitored
    • Once installed, it can’t be uninstalled, only disabled

    How Much Overhead is Used by the Data Collector

    • The Data Collector will add about 4% to the current CPU load of the SQL Server instance being monitored.
    • Using default data collection settings, the Data Collector can generate about 250-300 MB of data every day per SQL Server instance being monitored,which is stored in the MDW.
    • Each dcexec.exe process uses 30-100 MB memory each, and 2+ are running all the time.

    The data collector is integrated with SQL Server Agent and Integration Services, and uses both extensively.

    Key Components Used by the Data Collector

    • SSMS & SPs: To access and configure the data collector.
    • Local Cache: Used to store collected data temporarily before being moved to the MDW.
    • msdb: Stored data collector configuration info.
    • MDW: Relational database used to store data.
    • SSIS Packages: Used to collect data from targets, and to move data to the MDW.
    • SQL Server Agent Jobs: Used to run the scheduled SSIS packages. All activities are scheduled.
    • dcexec.exe Process or the Data Collector Run-time component: Each collection set has its own process, and it runs only during the collection process. This process runs outside of sqlserver.exe.

    A collection set can be designated to collect and send data to the MDW in two different ways

    (1) Cached
    Data is collected on one schedule, and stored in the local cache. Another schedule runs to move it from the local cache to the MDW. The Server Activity and Query Statistics collection sets use this option. Best for frequent data collection (more often than every five minutes) to reduce performance hit
    (2) Non-Cached
    Data is collected and sent to the MDW as a single job, in a single step. Job runs continuously. The Disk Usage collector set uses this method. Best used when data is collected in periods of 5 minutes or greater to reduce performance hit.

    Collection Sets, Types, & Items

    (1) Collection Set
    Disk Usage, Query Activity, Server Activity

    (2) Collection Type
    T-SQL Query, Performance Counter, Query Activity, SQL Trac

    (3) Collection Item
    Performance Counters, DMV Stats, Profiler Events, And many mor

    What are the predefined collectors inside SQL Server which can be used?

    (1) Disk Usage Collection Set

    • Tracks the growth of database and log files and provides file-related statistics on a regular basis.
    • Captures this 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.dmiovirtualfilestats

    (2) Server Activity Collection Set

    • Provides an overview of SQL Server activity, SQL Server resource utilization and SQL Server resource contention
    • Captures this data:
      • sys.dmoswaitstats
      • sys.dmoslatchstats
      • sys.dmosschedulers
      • sys.dmexecsessions , sys.dmexecrequests, sys.dmoswaitingtasks(using a joined query)
      • sys.dmosprocessmemory
      • sys.dmosmemory_nodes
      • Additional system and SQL Server performance counters

    (3) Query Statistics Collection Set

    • Gathers data about query statistics and individual query text, query plans, and specific queries
    • Captures this data:
      • sys.dmexecquery_stats view
      • The text of selected batches and queries
      • The plan of selected batches and queries
      • The normalized text of selected batche

    Installing and Configuring the Data Collector

    • Create the Management data warehouse (a one time step).
    • Add the Data Collector to each SQL Server instance to be monitored, and point it to the MDW. If you want to monitor 25 servers, then you perform this step 25 times.
    • Once each instance is added, the Data Collector begins to collect data immediately.

    Three key reports (with drill-down):

    • Disk Usage Report
    • Query Statistics Report
    • Server Activity Repor

    http://bradmcgehee.com/wp-content/uploads/presentations/Mastering%20the%20SQL%20Server%202008%20Performance%20Data%20CollectorSQLCONN.pdf

    Replied on Jan 30 2012 8:00AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    9

    Data Collector

    The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule.

    Data Collector is an optional feature of SQL Server 2008 that collects and stores information about SQL Server’s 2008’s status and performance over time, using pre-defined data collectors.

    What are the predefined collectors inside SQL Server which can be used?

    Data Collector includes three data collection sets, which are used to collect data from targets and put it in the management data warehouse

    (1) Disk Usage Collection Set

    Tracks the growth of database and log files and provides file-related statistics on a regular basis.

    Captures this data:

    • Snapshots of data file sizes obtained from sys.partitions and sys.allocation_units.
    • Snapshots of log file sizes obtained from DBCC SQLPERF (LOGSPACE).

    (2) Server Activity Collection Set

    Provides an overview of SQL Server activity, SQL Server resource utilization and SQL Server resource contention

    Captures this data:

    • sys.dmoswaitstats
    • sys.dmoslatchstats
    • sys.dmosschedulers
    • sys.dmexecsessions , sys.dmexecrequests, sys.dmoswaitingtasks(using a joined query)
    • sys.dmosprocessmemory
    • sys.dmosmemory_nodes
    • Additional system and SQL Server performance counters

    (3) Query Statistics Collection Set

    Gathers data about query statistics and individual query text, query plans, and specific queries

    Captures this data:

    • sys.dmexecquery_stats view
    • The text of selected batches and queries
    • The plan of selected batches and queries
    • The normalized text of selected batche

    Three key reports (with drill-down):

    • Disk Usage Report
    • Query Statistics Report
    • Server Activity Repor
    • Snapshots of I/O statistics from sys.dmiovirtualfilestats
    Replied on Feb 4 2012 7:19AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    8

    Data Collections

    The data collector is a component of SQL Server 2008 that collects different sets of data. Data collection either runs constantly or on a user-defined schedule. The data collector stores the collected data in a relational database known as the management data warehouse.

    The data collector is a core component of the data collection platform for SQL Server 2008 and the tools that are provided by SQL Server. The data collector provides one central point for data collection across your database servers and applications. This collection point can obtain data from a variety of sources and is not limited to performance data, unlike SQL Trace.

    The data collector enables you to adjust the scope of data collection to suit your test and production environments. The data collector also uses a data warehouse, a relational database that enables you to manage the data that you collect by setting different retention periods for your data.

    The data collector supports dynamic tuning for data collection and is extensible through its API.

    Collecter Types

    The data collector provides predefined collector types that you can use for data collection. The collector types provide the actual mechanism for collecting data and uploading it to the management data warehouse.

    1. Generic T-SQL Query Collector Type

    The Generic T-SQL Query collector type executes a user-provided Transact-SQL statement as an input parameter, saves the output from the query, and then uploads the output to the management data warehouse. This collector type is registered in the core.supportedcollectortypes view in the management data warehouse.

    This collector has the following input parameters:

    Value - The Transact-SQL query. You can provide more than one query as input.

    OutputTable - Provide the name of the table for saving query results before they are uploaded to the management data warehouse.

    Database - Specify the database or databases to run the query against. You can specify databases by name or use the * wildcard character to specify all the databases on the server. If you do not provide a value for Database, the query is run against all the system databases.

    2. Generic SQL Trace Collector Type

    The Generic SQL Trace collector type uses SQL Trace to monitor the SQL Server Relational Engine. The trace data can come from a default trace or from one or more custom traces. This collector type is registered in the core.supportedcollectortypes view.

    The default trace, controlled by a server-wide setting, runs continuously on the server and captures general events of interest. These events of interest are not related to individual batch execution. This is a low-impact trace. Custom traces can collect any events and can have low or high impact, depending on which events are selected and the server activity at the time that the trace is running. In most cases, custom traces do not run continuously.

    The Generic SQL Trace collector type runs a server-side trace that stores data in a file or set of files. The trace data is obtained from trace files by using the fntracegettable() system function. If configured to do so, the collector processes the data and then uploads the data to the management data warehouse.

    The Generic SQL Trace collector type is configured to remove unused files and to keep a fixed amount of space for stored trace data.

    3. Performance Counters Collector Type

    You can use the Performance Counters collector type to collect specific performance counter information from a computer that is running SQL Server 2008. You can use this data later as a baseline for troubleshooting or for capacity planning. This collector type is registered in the core.supportedcollectortypes view.

    This collector type has the following input parameters:

    Objects. The SQL Server objects running in an instance of SQL Server.

    Counters. The counters associated with a SQL Server object.

    Instances. The instances of the specified object.

    4. Query Activity Collector Type

    The Query Activity collector type is a custom collector type used by the Query Statistics collection set, one of the predefined system data collection sets.

    This collector type is used to collect query statistics and query activity information along with the query plan and query text for queries that meet predefined criteria. If you create your own collection set using this collector type, it will collect the same information as the Query Statistics collection set. Therefore, we recommend that you use the predefined Query Statistics collection set.

    Replied on Feb 13 2012 1:23AM  . 
    ATif-ullah Sheikh
    132 · 1% · 391

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.