December 15, 2011 at 2:12 am
Hi all,
Am working on my company product .
In our data base from long back my seniors created many indexes on many tables.
IS there any query find out un wanted index and duplicate index and un used index ?
And i wanted to know size of index , b'coz it should not cross 900 bytes.
so that i can clean my data base. and i can boost the performance !!!!
Please help me regarding this.
Regards,
Ravi.
December 15, 2011 at 2:30 am
Try looking at sys.dm_db_index_usage_stats
Although a word of warning - although an index may not be used 'frequently', it doesnt mean that it's not valid!!
Do you have the data model of your database? This might be a good start to identifying where indexing is required, and for what purpose.
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
December 15, 2011 at 4:20 am
Cleaning up unused indexes is an OK use for your time, but, you need to be aware, sys.dm_db_index_usage_stats only records information since the last time the server was restarted (and several other events) so while it will show you indexes that have not been used, you need to be very aware of the time frame under which it's operating. You might see an index that hasn't been accessed for three weeks, but that's because it's an index for the monthly report that the boss runs or something along those lines. You can't simply run a script, identify & drop indexes. You need to test each drop to validate it doesn't adversely affect the system. You need to have monitoring in place on the system so you can see if dropping an index has adversely affected the system.
Also, if you're really concerned with excessive indexes, you should be monitoring your inserts & updates to see if they are slowing down because of indexes. Reads won't be affected by having too many indexes to choose from.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2011 at 12:43 pm
Reply to your first que to find out unused index.
found from Technet library
you can try this
SELECT SCH.name + '.' + OBJ.name AS ObjectName
,OBJ.type_desc AS ObjectType
,IDX.name AS IndexName
,IDX.type_desc AS IndexType
FROM sys.indexes AS IDX
LEFT JOIN sys.dm_db_index_usage_stats AS IUS
ON IUS.index_id = IDX.index_id
AND IUS.object_id = IDX.object_id
INNER JOIN sys.objects AS OBJ
ON IDX.object_id = OBJ.object_id
INNER JOIN sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.is_ms_shipped = 0
AND OBJ.type IN ('U', 'V')
AND IDX.type > 0
AND IDX.is_disabled = 0
AND IDX.is_primary_key = 0
AND IDX.is_unique = 0
AND IUS.object_id IS NULL
ORDER BY ObjectName
,IndexName;
December 15, 2011 at 12:53 pm
To know the size of Index you can try this for particular Database
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
December 15, 2011 at 9:07 pm
Thank you very much all for your valuble suggestions and solutions 🙂 .
Regards,
Ravi.
December 16, 2011 at 6:15 am
Be carefull using the example above which usese the "detailed" option. As this looks at each leaf node perfromance can be incredibly slow. Changing this to simple whilst less accurate will return results much faster and use less resources.
MCITP SQL 2005, MCSA SQL 2012
December 16, 2011 at 8:31 am
Hope this helps:
http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/
http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
December 16, 2011 at 8:44 am
Sapen (12/16/2011)
http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/
http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/%5B/quote%5D
This is far more complex than just running scripts, please watch the 3 vids I posted.
December 18, 2011 at 9:42 pm
Okay
Thank you very much
Ravi.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply