Database Reference
In-Depth Information
repository */
SELECT GETDATE() AS [captureDate]
,i.serverName
,i.schemaName
,i.databaseName
,i.tableName
,i.indexName
,i.indexType
,i.isFiltered
,i.isPartitioned
,i.numberOfRows
,ddius.user_seeks AS [userSeeksSinceReboot]
,ddius.user_scans AS [userScansSinceReboot]
,ddius.user_lookups AS [userLookupsSinceReboot]
,ddius.user_updates AS [userUpdatesSinceReboot]
,(i.totalPages * 8)/ 1024 AS [indexSizeInMB]/* pages
are 8KB */
,dosi.sqlserver_start_time AS [lastReboot]
FROM @Indexes AS i
JOIN sys.dm_db_index_usage_stats AS ddius
ON i.databaseID = ddius.database_id
AND i.objectID = ddius.object_id
AND i.indexID = ddius.index_id
CROSS APPLY sys.dm_os_sys_info AS dosi
WHERE /* exclude system databases */
i.databaseName NOT
IN('master','msdb','tempdb','model')
/* exclude unique indexes; assume they are serving
a business function */
AND i.isUnique = 0
/* exclude primary keys; assume they are serving
a business function */
AND i.isPrimaryKey = 0
/* no seeks have been performed since the last server
reboot */
AND ddius.user_seeks = 0;
.
Search WWH ::
Custom Search