SQL Server 2012 : Measuring SQL Server Performance (part 3) - Dynamic Management Objects, Data Collector

2/20/2014 8:53:46 PM

3. Dynamic Management Objects

Introduced in SQL Server 2005, dynamic management objects (DMOs) are mechanisms for looking into the underlying structures and processes of the SQL Server 2012 system and, to a lesser degree, into the operating system. Of particular interest for looking at performance counters is the dynamic management view (DMV) sys.dm_os_performance_counters. This shows a list of SQL Server performance counters within the results of a query. It does not show the operating system performance counters. The performance counters for the operating system are not as easily queried as are those for SQL Server. Querying sys.dm_os_performance_counters is as simple as querying a table:

FROM    sys.dm_os_performance_counters ;

This query will return all the performance counters at this instance in time. To see a specific counter or instance of a counter, you just need to add a WHERE clause to the query so that you return only the counter you’re interested in, like this:

FROM    sys.dm_os_performance_counters AS dopc
WHERE   dopc.counter_name = 'Batch Requests/sec' ;

This will return a data set similar to that shown in Figure 5.


Figure 5. Results from query against sys.dm_os_performance_counters

The column, cntr_value, shows the value for the counter being selected. If there were no other operations on the server and you were to run the query again, in this instance the counter would go up by 1 to become 44974 because even the query against the DMV counts as a batch request. Other values for other counters may go up or down or even remain the same, depending on what each of the counters and instances is recording. You can use this data in any way you like, just like a regular T-SQL query, including storing it into a table for later access. The main strength of the sys.dm_os_performance_counters DMV is that you can access the data in T-SQL and use the data it displays with the T-SQL tools that you’re used to using.

Performance counters are not the only way to tell what is occurring within SQL Server. Another method of looking at performance data is the plethora of other DMOs. The DMVs within SQL Server can be roughly grouped as either server DMOs or database DMOs. There are 17 different divisions of DMOs. We won’t list them all, but we will list the groups directly used to access performance data about the system or the database:

  • Database: These are primarily concerned with space and the size of the database, which is important information for understanding performance.
  • Execution: The DMVs and dynamic management functions (DMFs) in this group are very much focused on the performance and behavior of the queries against the database. Some of these will be covered in the section “Tuning Queries.”
  • Index: Like the database-related DMOs, these are mostly about size and placement, which is useful information. You can also track which indexes are used and whether there are missing indexes.
  • I/O related: These DMOs are mainly concerned with the performance of operations against disks and files.
  • Resource Governor: These DMOs are not directly related to performance but are a means of addressing the settings, configuration, and behavior of the Resource Governor, which is directly related to performance. This is covered in detail in the section “Limiting Resource Use.”
  • SQL Server operating system:  Information about the operating system, such as memory, CPU, and associated information around the management of resources is available to the DMVs and DMFs grouped here.
  • Transaction: With the DMOs in this group, you can gather information about active transactions or completed transactions, which is very useful for understanding the performance of the system.

The ability to query all this information in real time or to run queries that gather the data into permanent tables for later analysis makes these DMVs a very important tool for monitoring the performance of the system. They’re also useful for later tuning that performance because you can use them to measure changes in behavior. But real-time access to this data is not always a good idea, and it doesn’t let you establish a baseline for performance. To do this, another way to collect performance data is needed. This is the Data Collector.

4. Data Collector

Introduced with SQL Server 2008, the data collector is a means of gathering performance metrics, including performance counters, from multiple SQL Server systems (2008 and above) and collecting all the data in one place, namely, the management data warehouse. The data collector will gather performance data, including performance counters, procedure execution time, and other information. Because it exposes a full application program interface (API), you can customize it to collect any other kind of information that you want. For our purposes, we’ll focus on the three default collections: Disk Usage, Query Activity, and Server Activity.

The data collector is a great way to look at information over a long period of time so that you can provide information for tuning purposes. For example, you would want to start collecting data on a new application right away. This initial set of data is known as a baseline. It gives you something to compare when someone asks you whether the system is running slowly or whether the databases are growing quickly. You’ll also have the ability to collect performance data before and after you make a change to the system. So if you need to know whether adding a new index, changing a query, or installing a hotfix changed the performance in the system, you’ll have data collected that allows you to compare behavior before and after the change you introduced to the system. All of this makes the data collector a vital tool in your performance-monitoring and tuning efforts.

images Caution While experimenting with the data collector, use development, QA, or other test servers and instances. Don’t take a chance on your production systems until you feel confident you know what you’re doing. Although collecting performance data is important, collecting too much performance data can actually cause performance problems.

  •  SQL Server 2012 Security : How Hackers Attack SQL Server
  •  SQL Server 2012 Security : Partially Contained Databases
  •  SQL Server 2012 : SQL Server Audit (part 3) - Viewing Audited Events,Querying Audit Catalog Views
  •  SQL Server 2012 : SQL Server Audit (part 2) - Auditing Server Events, Auditing Database Events
  •  SQL Server 2012 : SQL Server Audit (part 1) - Creating an Audit Object, Recording Audits to the File System
  •  SQL Server 2012 : Encryption Support (part 4) - Transparent Data Encryption - Enabling TDE, Backing Up the Certificate
  •  SQL Server 2012 : Encryption Support (part 3) - Transparent Data Encryption - Creating Keys and Certificates for TDE
  •  SQL Server 2012 : Encryption Support (part 2) - Encrypting Data at Rest
  •  SQL Server 2012 : Encryption Support (part 1) - Encrypting Data on the Move
  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th