Solved

10774 - used indexes

Posted on 2014-10-09
4
239 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 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