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
| (1) sqlserver索引失效查看
SELECT
I.name AS IndexName,
S.user_seeks AS [User Seeks],
S.user_scans AS [User Scans],
S.user_lookups AS [User Lookups],
S.user_updates AS [User Updates]
FROM
sys.indexes AS I
INNER JOIN
sys.dm_db_index_usage_stats AS S
ON
I.object_id = S.object_id AND I.index_id = S.index_id
WHERE
I.object_id = OBJECT_ID('dbo.lis_reqmain');
(2) 查看sqlserver索引缺失
-- Captures the Total CPU time spent by a query along with the query plan and total executions
SELECT
qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
q.[text],
p.query_plan,
qs_cpu.execution_count,
q.dbid,
q.objectid,
q.encrypted AS text_encrypted
FROM
(SELECT TOP 500 qs.plan_handle,
qs.total_worker_time,
qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE p.query_plan.exist('declare namespace
qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:MissingIndexes')=1
SELECT [TotalCost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0),
avg_user_impact,user_seeks,user_scans,
TableName = statement,
[EqualityUsage] = equality_columns,
[InequalityUsage] = inequality_columns,
[Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
where statement like '%表名%'
ORDER BY user_seeks DESC;
(3)查看是否存在冗余索引
SELECT OBJECT_NAME(i.object_id) ,i.name indexname,user_seeks,user_scans,last_user_seek,last_user_scan
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE database_id = db_id('库名')
and not i.name is null
and not i.name like 'PK%'
ORDER BY user_seeks (4)
(4) 索引使用量
select db_name(database_id) as N'数据库名称',
object_name(a.object_id) as tbname,
b.name N'索引名称',
user_seeks N'用户索引查找次数',
user_scans N'用户索引扫描次数'
from sys.dm_db_index_usage_stats a
left join sys.indexes b on a.index_id = b.index_id and a.object_id = b.object_id
where database_id=db_id('库名')
and object_name(a.object_id)='表名'
order by object_name(a.object_id) , user_seeks
(5) 索引空间
SELECT tn.[name] AS [Table name], ix.[name] AS [Index name]
,SUM(sz.[used_page_count]) * 8 AS [Index size (KB)]
FROM sys.dm_db_partition_stats AS sz
INNER JOIN sys.indexes AS ix ON sz.[object_id] = ix.[object_id]
AND sz.[index_id] = ix.[index_id]
INNER JOIN sys.tables tn ON tn.OBJECT_ID = ix.object_id
WHERE tn.[name]='表名'
GROUP BY tn.[name], ix.[name]
ORDER BY tn.[name]
|
