axinthefield: Data Management Views used in Performance Analyzer for Microsoft Dynamics

[Blog bot]

Источник: http://blogs.msdn.com/b/axinthefield...-dynamics.aspx
==============

Performance Analyzer for Microsoft Dynamics collects data from several Data Management Views (DMVs). In this article we’ll discuss what some of those DMVs are and the significance of the information from that DMV. Before starting, here is a link to all the major DMVs in SQL 2008:

• Change Data Capture Related Dynamic Management Views
• Query Notifications Related Dynamic Management Views
• Common Language Runtime Related Dynamic Management Views
• Replication Related Dynamic Management Views
• Database Mirroring Related Dynamic Management Views
• Resource Governor Dynamic Management Views
• Database Related Dynamic Management Views
• Service Broker Related Dynamic Management Views
• Execution Related Dynamic Management Views and Functions
• SQL Server Extended Events Dynamic Management Views
• Full-Text Search Related Dynamic Management Views
• SQL Server Operating System Related Dynamic Management Views
• Index Related Dynamic Management Views and Functions
• Transaction Related Dynamic Management Views and Functions
• I/O Related Dynamic Management Views and Functions
• Security Related Dynamic Management Views
• Object Related Dynamic Management Views and Functions

As can be seen, there are many Data Management Views (DMVs) in SQL Server from which to collect data. Much of this data is exposed through SQL Server Management Studio (SSMS) so why do we need Performance Analyzer for Microsoft Dynamics?

The answer is two-fold and related to each other. The first is that much of the DMV data is volatile thus requiring someone to review the data as the issue is occurring. For example, Query statistics only exist as long as the Execution Plan for that query exists in Procedure Cache. Once a plan is evicted from Procedure Cache, the statistics about how that query’s performed is also removed. This makes it important to periodically collect this data into persistent database. The second reason for Performance Analyzer for Microsoft Dynamics is that in order to do performance analysis, data must be collected over time. Most of the DMVs in are point in time values. To get significant meaning from these values a comparison between 2 points in time must be done. The best example of this is the ROW_COUNT column which is collected from Index DMVs. Looking at ROW_COUNT at any 1 point in time doesn’t provide much value other than how many rows are in 1 table vs. another table in the database. But, if we collect that data 2 times and we know how much time has occurred between those 2 points in time, then we can determine record growth for a table over time. This gives us the ability to accurately project database growth over time.

The following is a list of the DMVs collected in Performance Analyzer for Microsoft Dynamics:

• sys.dm_exec_query_stats
• sys.dm_exec_cached_plans
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_os_wait_stats
• sys.dm_os_buffer_descriptors
• sys.dm_os_sys_info
• sys.dm_os_performance_counters
• sys.dm_io_virtual_file_stats

In addition to these DMVs, there are several system tables that are collected:

• sys.indexes
• sys.sysindexes
• sys.objects
• sys.schemas
• sys.partitions
• sys.databases
• sys.allocation_units
• sys.configurations
• sys.database_files
• msdb.dbo.sysjobs
• msdb.dbo.sysjobsteps steps
• msdb.dbo.sysschedules
• sys.synonyms
• sys.stats_columns
• dbcc show_statistics
• dbcc tracestatus
• xp_readerrorlog

Performance Analyzer for Microsoft Dynamics eases the analysis by organizing all of this data into a single database called DYANMICSPERF. Do to some flexibility issues in SQL Servers Performance Data Warehouse data collectors such as moving data into specific tables to ease analysis and retaining data based upon 2 different data collection sets such as deleting all query plans where the query stats doesn’t exist in another data collection, the DYNAMICSPERF database was developed.




Источник: http://blogs.msdn.com/b/axinthefield...-dynamics.aspx