September 13, 2011 at 7:41 am
Hi,
I use SQL Server 2005 and SQL 2008 and I need a script that will tell you which indexes are not at all used on a table level so that I can delete that particular index which is not at all used.
I really appreciate for any help.
September 13, 2011 at 7:44 am
There's 1.87 millions scripts out there that do this (actual number).
September 13, 2011 at 7:45 am
espanolanthony (9/13/2011)
Hi,I use SQL Server 2005 and SQL 2008 and I need a script that will tell you which indexes are not at all used on a table level so that I can delete that particular index which is not at all used.
I really appreciate for any help.
It's eassy one.
Use the DMV SYS.DM_DB_INDEX_USAGE_STATS join the SYS.INDEXES Write a query yourself.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
September 13, 2011 at 7:51 am
If I knew I would not have posted here. I would very much appreciate if someone shed some light on it please.
September 13, 2011 at 7:56 am
espanolanthony (9/13/2011)
If I knew I would not have posted here. I would very much appreciate if someone shed some light on it please.
Have a look :
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
September 13, 2011 at 7:58 am
espanolanthony (9/13/2011)
If I knew I would not have posted here. I would very much appreciate if someone shed some light on it please.
I know a lot, but google knows more than I ever will!
September 13, 2011 at 8:14 am
Thank you very much to help me out.
September 13, 2011 at 8:19 am
I suspect that you are too busy to have had any opportunity to read any of the numerous blog posts on this subject that litter the internet.
So for your convenience, here is a ready provided script.
SELECT DB_NAME() AS DatabaseName,
OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName
FROM sys.indexes I
WHERE -- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- find all indexes that exists but are NOT used
AND NOT EXISTS (SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- limit our query only for the current db
AND database_id = DB_ID()
AND (user_seeks >0 OR user_scans > 0))
AND I.NAME IS NOT NULL
AND I.IS_PRIMARY_KEY = 0 AND I.IS_UNIQUE = 0
ORDER BY SchemaName, ObjectName, IndexName
September 13, 2011 at 8:57 am
Thank you very much...
September 13, 2011 at 10:49 am
I suspect that you are too busy to have had any opportunity to read any of the numerous blog posts on this subject that litter the internet.
:laugh:
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
September 13, 2011 at 5:20 pm
This is an excellent one from Michael Campbell
WITH indexstats ([Table],[Index],[Reads],[Writes],[Rows])
AS
(
SELECT
usr.[name] + '.' + obj.[name] [Table],
ixs.[name] [Index] ,
usage.user_seeks + usage.user_scans + usage.user_lookups [Reads],
usage.[user_updates] [Writes],
(SELECT SUM(sp.[rows]) FROM sys.partitions sp WHERE usage.OBJECT_ID = sp.object_id AND sp.index_id = usage.index_id) [Rows]
FROM
sys.dm_db_index_usage_stats usage
INNER JOIN sys.indexes ixs ON usage.[object_id] = ixs.[object_id] AND ixs.[index_id] = usage.[index_id]
INNER JOIN sys.objects obj ON usage.[object_id] = obj.[object_id]
INNER JOIN sys.sysusers usr ON obj.[schema_id] = usr.[uid]
WHERE
usage.database_id = DB_ID() -- current DB
AND usage.index_id > 0 --
AND OBJECTPROPERTY(usage.[object_id], 'IsUserTable') = 1
)
SELECT
*
FROM
indexstats
WHERE
Reads = 0
ORDER BY
[Rows] DESC,
[Index]
Thank You,
Best Regards,
SQLBuddy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply