Solved

10774 - used indexes

Posted on 2014-10-09
4
221 Views
Last Modified: 2014-10-10
Dear Experts.

Please your help with how to solve these 2 following queries:

1. How do I get the least used indexes to then be removed, (dynamic view or Databas engine tuning advisor)
2. How do I get the recommended indices to be created.
0
Comment
Question by:enrique_aeo
4 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 167 total points
ID: 40372217
Hello,

you can get that info from the sys tables

1. sys.dm_db_index_usage_stats - Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server. more info on http://msdn.microsoft.com/en-us/library/ms188755.aspx .
On following link you can find good script on how to get more info on these indexes http://blog.sqlauthority.com/2011/01/04/sql-server-2008-unused-index-script-download/

2. sys.dm_db_missing_index_details - Returns detailed information about missing indexes, excluding spatial indexes. more info on http://msdn.microsoft.com/en-us/library/ms345434.aspx . Here is good script http://basitaalishan.com/2013/03/13/find-missing-indexes-using-sql-servers-index-related-dmvs/
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 167 total points
ID: 40372362
Please read "Limitations of the Missing Indexes Feature"

The missing index feature has the following limitations:
It is not intended to fine tune an indexing configuration.
...
The missing indexes feature is a lightweight tool for finding missing indexes that might significantly improve query performance. It does not provide adequate information to fine tune your indexing configuration. Use Database Engine Tuning Advisor for that purpose. For more information about Database Engine Tuning Advisor, see Tuning the Physical Database Design and Related Query Tuning Features.
0
 

Assisted Solution

by:Martin Madsen
Martin Madsen earned 166 total points
ID: 40372384
For unused index use this query:
SELECT 
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

Open in new window


For index recommendations:

SELECT
mid.statement
  ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,OBJECT_NAME(mid.Object_id),
  'CREATE INDEX [missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle)
  + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
  + ' ON ' + mid.statement
  + ' (' + ISNULL (mid.equality_columns,'')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
    + ISNULL (mid.inequality_columns, '')
  + ')'
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Open in new window


To get usable suggestions the system needs to have a long uptime. Check last restart time with:

select create_date from sys.databases where name = 'tempdb' 

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40373420
A huge amount of index knowledge goes into properly tuning indexes.  No script(s) alone can do that task for you or convey that knowledge to you.  For example, for many indexes, you must also consider number of rows, cardinality of values, whether filtered indexes are applicable, etc..  You can only get that specific understanding and tasks from someone who has that expertise.
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now