June 6, 2012 at 10:54 am
hi,
I have a query which finds the lists of the unused indexes in the table.
However, can that query be further modified to get the list of the "Most Costly unused indexes"?
----------------------------------------------------
SELECT 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())
ORDER BY SchemaName, ObjectName, IndexName
Thanks.
June 6, 2012 at 11:01 am
What defines "Most Costly"?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2012 at 11:03 am
which takes up more resources like, CPU/memory/less use count. I assumed in this way
Thanks.
June 6, 2012 at 11:13 am
Sourav-657741 (6/6/2012)
which takes up more resources like, CPU/memory/less use count. I assumed in this way
Well indexes do not use CPU.
By memory I assume you mean physical storage space? You can use the undocumented MS stored proc sp_MSIndexSpace to retrieve index sizing.
You have already determined these have never been used.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 7, 2012 at 5:44 am
If an index isn't being used, you should see a lack of scans & seeks in the index usage stats DMO. So you want to limit that sub-select to those indexes.
Just remember, that DMO is not a complete record for all time. It only covers since the server was started or the database was attached or brought online.
"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
June 8, 2012 at 10:24 am
SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName, I.type_desc as IdxType, ips.record_count,
(ips.record_count * ips.avg_record_size_in_bytes)/1024/1024 as 'Size(MB)'
FROM sys.indexes I
INNER JOIN sys.dm_db_index_physical_stats ( DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
ON I.OBJECT_ID = ips.OBJECT_ID AND I.index_id = ips.index_id
WHERE
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
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
AND database_id = DB_ID())
AND ips.record_count > 0
ORDER BY SchemaName, ObjectName, IndexName
-- you can get a decent idea of space being used by using the avg record size in bytes.
June 8, 2012 at 10:46 am
;WITH IndexSpaceUsed
as (
SELECT OBJECT_NAME(object_id) as ObjName
,SUM(reserved_page_count) as ResPgCnt
,SUM(used_page_count) as UsedPgCnt
,SUM(CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) as Pages
,SUM(CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END) as RowCnt
FROM sys.dm_db_partition_stats
GROUP BY object_id
)
SELECT ObjName, Rows = RowCnt, reserved = LTRIM (STR (ResPgCnt * 8, 15, 0)),
data = LTRIM (STR (Pages * 8, 15, 0)),
index_size = LTRIM (STR ((CASE WHEN UsedPgCnt > Pages THEN (UsedPgCnt - Pages) ELSE 0 END) * 8, 15, 0) ),
unused = LTRIM (STR ((CASE WHEN ResPgCnt > UsedPgCnt THEN (ResPgCnt - UsedPgCnt) ELSE 0 END) * 8, 15, 0))
FROM IndexSpaceUsed;
-- this SQL code is used to check space increases from new indexing being pushed into production, it might be modified to support further breakdown detail for your index analysis
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply