1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
| IF OBJECT_ID('tempdb..#StatIndex') IS NOT NULL
DROP TABLE #StatIndex
CREATE TABLE #StatIndex (
[DB] SYSNAME
, FG SYSNAME NULL
, [Schema] SYSNAME
, [Table] SYSNAME
, [Index] SYSNAME NULL
, [Type] NVARCHAR(60)
, [is_disabled] BIT
, [NumPartition] INT NULL
, [NbReads] BIGINT
, [NbWrites] BIGINT
, [Seeks] BIGINT
, [Scans] BIGINT
, [Lookups] BIGINT
, [Inserts] BIGINT
, [Updates] BIGINT
, [Deletes] BIGINT
, [NumRows] BIGINT
, [Reserved_KB] BIGINT
, [Used_KB] BIGINT
, last_user_seek DATETIME
, last_user_scan DATETIME
, last_user_lookup DATETIME
, last_user_update DATETIME
)
DECLARE List CURSOR FOR
SELECT Name
FROM sys.databases db
DECLARE @ReqBase VARCHAR(MAX)
DECLARE @Name SYSNAME
OPEN List
FETCH NEXT FROM List
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ReqBase = '
USE ' + @name + '
SELECT ''' + @Name + '''
, p.fg
, s.name
, t.name
, i.name
, i.type_desc
, i.is_disabled
, p.partition_number
, ISNULL(ius.user_seeks,0) + ISNULL(ius.system_seeks,0)
+ ISNULL(ius.user_scans,0) + ISNULL(ius.system_scans,0)
+ ISNULL(ius.user_lookups,0) + ISNULL(ius.system_lookups,0)
AS Reads
, ISNULL(ius.user_updates,0) + ISNULL(ius.system_updates,0) AS Writes
, ISNULL(ius.user_seeks,0) + ISNULL(ius.system_seeks,0)
, ISNULL(ius.user_scans,0) + ISNULL(ius.system_scans,0)
, ISNULL(ius.user_lookups,0) + ISNULL(ius.system_lookups,0)
, ISNULL(ios.leaf_insert_count,0) + ISNULL(ios.nonleaf_insert_count,0)
, ISNULL(ios.leaf_update_count,0) + ISNULL(ios.nonleaf_update_count,0)
, ISNULL(ios.leaf_delete_count,0) + ISNULL(ios.nonleaf_delete_count,0)
, p.rows
, p.total_pages * 8192 / 1024
, p.used_pages * 8192 / 1024
, ius.last_user_seek
, ius.last_user_scan
, ius.last_user_lookup
, ius.last_user_update
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN (
SELECT DISTINCT f.name AS fg, p.partition_number, p.object_id, p.index_id, f.is_read_only, p.rows, a.total_pages, a.used_pages
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON (p.hobt_id = a.container_id AND a.type IN (1,3))
OR (p.partition_id = a.container_id AND a.type IN (2))
INNER JOIN sys.filegroups f
ON a.data_space_id = f.data_space_id
) p ON p.object_id = t.object_id
AND p.index_id = i.index_id
LEFT JOIN sys.dm_db_index_operational_stats (DB_ID(), NULL,NULL,NULL) ios
ON ios.object_id = i.object_id
AND ios.index_id = i.index_id
AND ios.partition_number = p.partition_number
LEFT JOIN sys.dm_db_index_usage_stats AS ius
ON ius.object_id = i.object_id
AND ius.index_id = i.index_id
AND ius.database_id = DB_ID()'
PRINT @ReqBase
INSERT INTO #StatIndex
EXEC(@ReqBase)
FETCH NEXT FROM List
INTO @Name
END
CLOSE List;
DEALLOCATE List;
SELECT sqlserver_start_time AS [LastBoot]
, DATEDIFF(DAY,sqlserver_start_time,GETDATE()) AS [NbDaysLastReboot]
FROM sys.dm_os_sys_info
SELECT FG, [Table], [Index], [Type], [is_disabled], NbReads, NbWrites, NumRows, Reserved_KB, Used_KB
, Seeks, Scans, Lookups, Inserts, Updates, Deletes
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
, 'DROP INDEX [' + [Index] + '] ON [' + [Schema] + '].[' + [Table] + ']'AS req_drop
FROM #StatIndex |
