SQLServerCentral.com Articles tagged DMV

Tune Your Indexing Strategy with SQL Server DMVs

Tue, 23 Oct 2012 06:00:00 UT

SQL Server Indexes need to be effective. It is wrong to have too few or too many. The ones you create must ensure that the workload reads the data quickly with a minimum of I/O. As well as a sound knowledge of the way that relational databases work, it helps to be familiar with the Dynamic Management Objects that are there to assist with your indexing strategy.

Targeted Index Performance Improvements

Fri, 03 Aug 2012 06:00:00 UT

This article identifies which indexes are used when T-SQL code runs, and proposes ways of improving these targeted indexes, leading to better performing code.

Get Performance Tips Directly From SQL Server

Fri, 22 Jun 2012 06:00:00 UT

This article describes a utility that allows you to inspect cached plans and extract information that can be useful in improving the performance of your SQL.

Targeted Index Performance Improvements

Fri, 03 Aug 2012 06:00:00 UT

This article identifies which indexes are used when T-SQL code runs, and proposes ways of improving these targeted indexes, leading to better performing code.

Investigating Transactions Using Dynamic Management Objects

Fri, 22 Oct 2010 06:00:00 UT

There can be a great difference in the performance of a particular routine in a test database, and in a fully loaded production system. When you hit performance problems in a database under load, and there is excessive locking and blocking, how can you determine exactly where the problems lie, in order to fix them?

DMVs: are they worth the effort?

Mon, 23 Aug 2010 06:00:00 UT

Many of the SQL Server DMVs still have a wild, unfinished feel but they are an incredibly useful tool for DBA, well-worth the sweat and toil required to learn and query them effectively.

Using Dynamic Management Objects to Monitor I/Os Against an Instance of SQL Server

Wed, 11 Aug 2010 06:00:00 UT

To ensure that queries run as efficiently as possible, the database administrator needs to monitor performance to find inefficient queries. This article focuses on exploring how a DBA can monitor the I/Os against an instance of SQL Server, using Dynamic Management Objects, or more commonly know as Dynamic Management Views (DMVs) and Functions (DMFs).

‘Disturbing Development’
Grant Fritchey & the DBA Team present the latest installment of the Top 5 hard-earned lessons of a DBA – read it now

Understanding Dynamic Management Views and Functions in SQL Server 2008

Tue, 06 Apr 2010 06:00:00 UT

Performance monitoring and optimization is an inseparable part of a DBA's activities. To optimize a poorly performing system/query or to troubleshoot the performance issues you need to know the root cause. Prior to SQL Server 2005, we had several system tables to monitor the state of the system.

Get Performance Tips Directly From SQL Server

Fri, 22 Jun 2012 06:00:00 UT

This article describes a utility that allows you to inspect cached plans and extract information that can be useful in improving the performance of your SQL.

SQL Server DMVs in Action - Sample Chapter

Thu, 04 Mar 2010 07:00:00 UT

Read the first chapter of this new book on DMVs.

Quick Tip Of The Day

Fri, 19 Feb 2010 07:00:00 UT

Over and over again we are told that the DMV’s only hold data since your last reboot. So, how do...

SQL Server DMV Green Paper

Tue, 02 Feb 2010 07:00:00 UT

Get a free green paper, an early access look at a new DMV book from longtime author Ian Stirk.

What SQL Statements Are Currently Executing?

Fri, 11 Dec 2009 07:00:00 UT

This article describes a utility that allows you to identify what SQL statements are currently executing.

SQL Server Monitoring Scripts with the DMVs

Fri, 23 Oct 2009 06:00:00 UT

If you do not have a third party monitoring tool in place, the only way is to constantly monitor SQL Server. To do this without being overly intrusive, we need to rely on the SQL Server DMVs.

Targeted Index Performance Improvements

Fri, 03 Aug 2012 06:00:00 UT

This article identifies which indexes are used when T-SQL code runs, and proposes ways of improving these targeted indexes, leading to better performing code.

Troubleshooting Performance Problems in SQL Server 2008

Fri, 31 Jul 2009 06:00:00 UT

This white paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools.

Retaining historical index usage statistics for SQL Server - Part 2

Wed, 24 Jun 2009 06:00:00 UT

We look at performing the same task for the sys.dm_db_index_operational_stats Dynamic Management Function (or DMF.) The process is identical, the code is however quite different due to the structural differences between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.

Get Performance Tips Directly From SQL Server

Fri, 22 Jun 2012 06:00:00 UT

This article describes a utility that allows you to inspect cached plans and extract information that can be useful in improving the performance of your SQL.

Finding the Worst Performing T-SQL Statements on an Instance

Mon, 02 Mar 2009 07:00:00 UT

Greg Larsen introduces some Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) to help monitor your application's T-SQL performance. In addition, he provides a Stored Procedure that utilizes the DMVs and DMFs to produce a report that quickly identifies poorly performing T-SQL statements.

Using DMVs to improve SQL index effectiveness

Wed, 18 Feb 2009 07:00:00 UT

Dynamic management views measure the effectiveness of indexes and discover fragmented indexes that may slow down SQL Server.

Gaining the benefits of DMVs for SQL Server

Wed, 04 Feb 2009 07:00:00 UT

Dynamic management views help monitor SQL Server health and tune performance. Learn how to use DMVs to retrieve critical data on the internal workings of SQL Server.

What SQL Statements Are Currently Executing?

Fri, 11 Dec 2009 07:00:00 UT

This article describes a utility that allows you to identify what SQL statements are currently executing.

Identifying Queries Running Slower Than Normal

Thu, 14 Aug 2008 06:00:00 UT

The attached article describes a utility that allows you to quickly identify which queries are running slower than expected. Note this article was updated to correct the formatting issues.

Identifying Queries Running Slower Than Normal

Thu, 14 Aug 2008 06:00:00 UT

The attached article describes a utility that allows you to quickly identify which queries are running slower than expected. Note this article was updated to correct the formatting issues.

SQL Server System Configuration Values

Tue, 05 Aug 2008 06:00:00 UT

Often times we need to review the SQL Server instance configuration values while troubleshooting performance problems. What alternatives are available to capture this information? With all of the new views in SQL Server 2005, is one available with the configuration values, a description and the list of possible values?

Monitoring Stored Procedure Performance with sys.dm_exec_cached_plans

Thu, 13 Mar 2008 06:00:00 UT

This article will show you how to use the sys.dm_exec_cached_plans DMV to monitor the performance of stored procedures

SQL Server: Uncover Hidden Data to Optimize Application Performance

Fri, 11 Jan 2008 07:00:00 UT

Many application performance problems can be traced to poorly performing database queries; however, there are many ways you can improve database performance. SQL ServerTM 2005 gathers a lot of information that you can use to identify the causes of such performance issues.

Measure TSQL Statement Performance

Tue, 08 Jan 2008 07:00:00 UT

Every developer needs to ensure that each TSQL statement is optimized. This article will give you a few different ideas on how to identify slow running queries and provide you with some tips on monitor your query performance while you make iterative changes to each query to try and improve performance.

Index related DMVs and DMFs - sys.dm_db_index_usage_stats

Mon, 12 Nov 2007 07:00:00 UT

Examining statistics of indexes is useful for optimizing the performance of queries. Statistics help us determine the usage and worth of indexes - one simple method is using the index-related dynamic management view; sys.dm_db_index_usage_stats