axinthefield: New DMVs in SQL Server 2008 R2 SP1 and SQL 2012 ('Denali') and Performance Analyzer for Microsoft Dynamics

[Blog bot]

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

One of the new features in Performance Analyzer 1.10 for Microsoft Dynamics is support for new DMVs in SQL 2008 R2 SP1 and SQL 2012. In this blog, we’ll explain what the new DMVs are and how they are used in analysis of performance issues in Microsoft Dynamics deployments.



New DMVs:

sys.dm_os_windows_info

This DMV returns information about the Windows operating system such as: Release, Service Pack Level and language version



How it’s used: A new table was added to the DynamicsPerf database that collects the information from this DMV. The benefit of this DMV is so that we can check to make sure that the operating system is patched, current service packs, and that the language matches the SQL server language version. This allows you to make sure that your system is up to date and have the correct versions installed. Out of date systems can certainly be the root cause of performance issues on a SQL Server.



sys.dm_os_volume_stats

This is a new DMV that returns information for each partition that the SQL instance has a database file located on that drive.

How it’s used: A new table was added to the DynamicsPerf database that collects the information from this DMV. This DMV is a great new piece of additional information about the disk drives that host our databases. This DMV allows you to see how much free space is available on that partition. So now while you are looking for performance issues with Performance Analyzer you can also see if you have disk space issues on your database drives. If you run out of disk space you can certainly crash your system and potentially corrupt data(partial write to transaction log file).



sys.dm_server_registry

This DMV contains all the registry entries for SQL server that you normally interact with via the SQL Server Configuration application.

How it’s used: A new table was added to the DynamicsPerf database that collects the information from this DMV. There is some really good pieces of information in this DMV. Probably the most important is what are your startup parameters that you have setup, especially trace flags. Some other useful information is where has SQL Server binaries been installed too.



sys.dm_server_services

This DMV contains information about only the SQL Server services that having been installed on your server.

How it’s used: A new table was added to the DynamicsPerf database that collects the information from this DMV. There are 2 really good pieces of information that comes from this DMV. First, you can see all the SQL services that have been installed such as: Is this the database server and the reporting server and analysis server installed. Having too many services installed could be the cause of performance issues on the server. The second really useful piece of information that is valuable is that you can see the accounts that each service is configured to startup as. Insufficient rights for the service accounts can be the root cause of many different issues.



Modified DMVs:

sys.dm_exec_query_stats

This existing DMV has had new columns added to it: total_rows, last_rows, min_rows, max_rows

How it’s used: One of the challenges in analyzing the performance of queries in the past is do we have a high read count because the query is bad or because the user requested too much data. With these new columns that question can be answered. If we have a high read count and a high number of rows then the user requested a lot of data such as a monthly report. If we have a high read count but a low number or rows then this query needs to be investigated for performance issues.



Performance Analyzer for Microsoft Dynamics is written by the Premier Field Engineer’s for Dynamics team. Please read about us at PFEDynamics. You can download the tool at Performance Analyzer for Microsoft Dynamics.




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