 |
Return Create A Forum - Home |
 |
--------------------------------------------------------- |
|
ExcelSoft Database Professionals |
 |
https://esdbp.createaforum.com |
|
--------------------------------------------------------- |
|
***************************************************** |
|
Return to: Scripts |
|
***************************************************** |
|
#Post#: 121-------------------------------------------------- |
|
Possible Bad NC Indexes (writes > reads) |
|
By: srinivasma_exceldbp Date: December 25, 2014, 4:20 am |
|
--------------------------------------------------------- |
|
-- Possible Bad NC Indexes (writes > reads) |
|
-- Look for indexes with high numbers of writes and zero or very |
|
low numbers of reads |
|
-- Consider your complete workload, and how long your instance |
|
has been running |
|
-- Investigate further before dropping an index! |
|
-- Select Database in dropdown in SSMS and run |
|
SELECT OBJECT_NAME(s.[object_id]) AS [table Name], i.name AS |
|
[Index Name], i.index_id, |
|
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor, |
|
user_updates AS [Total Writes], user_seeks + user_scans + |
|
user_lookups AS [Total Reads], |
|
user_updates - (user_seeks + user_scans + user_lookups) AS |
|
[Difference] |
|
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) |
|
INNER JOIN sys.indexes AS i WITH (NOLOCK) |
|
ON s.[object_id] = i.[object_id] |
|
AND i.index_id = s.index_id |
|
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 |
|
AND s.database_id = DB_ID() |
|
AND user_updates > (user_seeks + user_scans + user_lookups) |
|
AND i.index_id > 1 |
|
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] |
|
ASC OPTION (RECOMPILE); |
|
***************************************************** |
