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:

As you can see, 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: 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 two 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 one table vs. another table in the database, but, if we collect that data two times and we know how much time has occurred between those two 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.  Due to some flexibility issues in SQL Servers Performance Data Warehouse, data collectors moving data into specific tables to ease analysis, and retaining data based upon two 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://Code.msdn.com/dynamicsperf Downloadable link