Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Learn about SQL Server DBA, SQL Server database performance,SQL Server optimisation,database tuning,t-sql,ssis
Browse by Tags · View All
DBA Scripts 51
performance 37
SQL Server 29
Object Management 24
#SQLServer 24
Backup and Restore 20
Security Management 20
Powershell 17
Indexes 14
DBA 14

Archive · View All
June 2011 38
January 2011 33
May 2011 32
August 2011 27
July 2011 26
January 2012 24
February 2011 19
April 2011 19
March 2011 17
March 2012 17

Jack Vamvas's Blog

Top 5 SQL Server DMV for Index Analysis

May 27 2011 6:01AM by Jack Vamvas   

SQL Server DMV and functionsare one of my favourite SQL Server features when tuning SQL Server Index performance.

I use the following 5 SQL Server DMVs for general overview

1. sys.dm_db_index_usage_stats

Maintains counts for the range of index activity and the last performed time. Also displays statistics ob how an index is used against a query.

select 
	object_schema_name(indexes.object_id) 
		+ '.' 
		+ object_name(indexes.object_id) as objectName,
	indexes.type_desc,
	ius.user_seeks, 
	ius.user_scans,
	ius.user_lookups, 
	ius.user_updates 
from sys.indexes
left outer join sys.dm_db_index_usage_stats ius
on indexes.object_id = ius.object_id
and indexes.index_id = ius.index_id
and ius.database_id = db_id()

2. sys.dm_db_missing_index_details

Returns detailed information about each missing index on a table. Information is lost at SQL Server recycle.

SELECT * FROM sys.dm_db_missing_index_details

3. sys.dm_db_missing_index_columns

4. sys.dm_exec_query_stats

Performance statistics for cached plans. The information is only available while the plan remains in the cache.

select qs.sql_handle, qs.execution_count
     , qs.total_elapsed_time, qs.last_elapsed_time
     , qs.min_elapsed_time, qs.max_elapsed_time
     , qs.total_clr_time, qs.last_clr_time
     , qs.min_clr_time, qs.max_clr_time
from sys.dm_exec_query_stats as qs

5. sys.dm_db_index_operational_stats

Returning IO, locking, latching and access activity. Useful for identifying index hotspots, waits for read\writes to a table. Will give information about insert, update and delete.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

To assist in long term SQL Server index analysis record the output of the SQL Server DMVs , output the recordsets to a data depository . As the data is lost when SQL Server restarts

Returns database columns missing indexes. Information is lost at SQL Server recycle.

select * from sys.dm_db_missing_index_columns(1)

Normally, I use them as a general approach and then drill into a specific SQL Server performance problem.

Republished from http://www.sqlserver-dba.com.


Republished from SQL Server DBA [65 clicks].  Read the original version here [32134 clicks].

Jack Vamvas
5 · 26% · 8528
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]