Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Unused Indexes in your databases Expand / Collapse
Author
Message
Posted Tuesday, August 23, 2011 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:54 AM
Points: 106, Visits: 363
It is also helpful to know how much space is used by the index. Here is how I did that:
CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsed

EDIT: I should note that I used sys.sysindexes rather than sys.indexes. However, sys.sysindexes may not be supported in future versions of SQL Server.
Post #1164061
Posted Tuesday, August 23, 2011 8:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:53 AM
Points: 33, Visits: 117
I am getting an error when I add that in (invalid column name dpages). Can you post the complete SQL?
Post #1164077
Posted Tuesday, August 23, 2011 9:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 8:54 AM
Points: 106, Visits: 363
@Randall: Understand that this is using the sys.sysindexes view that may not be supported in future versions.

SELECT	LEFT(OBJECT_NAME (i.id), 50)	AS TableName,
LEFT(ISNULL(i.name,'HEAP'), 50) AS IndexName,
CASE WHEN u.object_id IS NULL THEN 'No Data' ELSE 'No Usage' END AS Determination,
i.dpages AS PagesUsed,
CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsed
FROM sys.objects o
JOIN sys.sysindexes i
ON i.id = o.object_id
LEFT JOIN sys.dm_db_index_usage_stats u
ON i.indid = u.index_id
AND u.object_id = i.id
WHERE o.type = 'U' -- Exclude system tables.
AND i.name IS NOT NULL -- Exclude heaps
AND i.name NOT LIKE '_WA_Sys%' -- Exclude statistics
AND ( u.object_id IS NULL -- Either no usage stats, or...
OR ( u.user_seeks = 0 -- all usage stats are zero.
AND u.user_scans = 0
AND u.user_lookups = 0
)
)
ORDER BY i.dpages DESC

Post #1164093
Posted Tuesday, August 23, 2011 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:53 AM
Points: 33, Visits: 117
Thank you. That took care of my problem.
Post #1164100
Posted Saturday, March 30, 2013 6:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 22, 2013 5:54 AM
Points: 1, Visits: 11
Why used Union all, if run query after Union all separately it gives 0 rows in result. However only some additional clauses are in where clause of query after Union all. Cannot it be done in a single query without using Union all. Please explain
Post #1437197
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse