SQL 索引相关

--索引使用次数①
--db_id()：返回当前数据库的id号，也可自填参数。
--sys.dm_db_index_usage_stats：返回不同类型索引操作的计数以及上次执行的事件
--sys.indexes：每个表格对象（例如，表、视图或表值函数）的索引或堆都包含一行。
declare @dbid int
select @dbid = db_id()
select objectname=
(indexUsageStats.object_id), indexUsageStats.object_id, indexname=sysIndex.name, sysIndex.index_id
            , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats indexUsageStats,sys.indexes sysIndex
where database_id = @dbid 
and objectproperty(indexUsageStats.object_id,'IsUserTable') = 1 and sysIndex.object_id = indexUsageStats.object_id and sysIndex.index_id = indexUsageStats.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc


--索引使用次数②，索引使用多的排前面
--sys.objects：在数据库中创建的每个用户定义的架构范围内的对象在该表中均对应一行。
--sys.databases：为 SQL Server 实例中的每个数据库都包含一行
--sys.dm_db_partition_stats
SELECT  sysObject.name ,sysDatabase.name ,sysIndexe.name ,user_seeks ,
        user_scans ,user_lookups ,partitionStats.row_count
FROM    sys.dm_db_index_usage_stats sysStats
LEFT JOIN sys.objects sysObject ON sysStats.object_id = sysObject.object_id
LEFT JOIN sys.databases sysDatabase ON sysDatabase.database_id = sysStats.database_id
LEFT JOIN sys.indexes sysIndexe ON sysIndexe.index_id = sysStats.index_id AND sysStats.object_id = sysIndexe.object_id
LEFT JOIN sys.dm_db_partition_stats partitionStats ON sysStats.object_id = partitionStats.object_id AND sysIndexe.index_id = partitionStats.index_id
WHERE   1 = 1
--AND databases.database_id = 7
AND sysObject.name IS NOT NULL 
AND sysIndexe.name IS NOT NULL
AND user_scans>0
ORDER BY user_scans DESC ,sysStats.object_id ,sysIndexe.index_id



--索引提高了多少性能
--sys.dm_db_missing_index_details：返回有关缺失索引的详细信息，不包括空间索引。
--sys.dm_db_missing_index_groups：此 DMV 返回有关特定索引组中除空间索引中缺少的索引的信息。
--sys.dm_db_missing_index_group_stats：返回缺失索引组的摘要信息，不包括空间索引。
SELECT
missingIndexGroupStats.avg_user_impact AS average_improvement_percentage,  
missingIndexGroupStats.avg_total_user_cost AS average_cost_of_query_without_missing_index,  
'CREATE INDEX ix_' + [statement] +  
ISNULL(equality_columns, '_') + 
ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  
' (' + ISNULL(equality_columns, ' ') +  
ISNULL(inequality_columns, ' ') + ')' +  
ISNULL(' INCLUDE (' + included_columns + ')', '')  AS create_missing_index_command 
FROM sys.dm_db_missing_index_details missingIndexDetails 
INNER JOIN sys.dm_db_missing_index_groups missingIndexGroups ON missingIndexDetails.index_handle = missingIndexGroups.index_handle 
INNER JOIN sys.dm_db_missing_index_group_stats missingIndexGroupStats ON  missingIndexGroups.index_group_handle = missingIndexGroupStats.group_handle 
--WHERE avg_user_impact > = 40
order by avg_user_impact desc


--最占用CPU，执行事件最长命令
--数据库中的每一个对象有唯一的ID值。object_name(id)可以获取该对象ID的名称。object_id(name)可以获得该对象名称的ID
--但只能返回用户创建对象的ID，如系统表时无法返回的
--varchar(n)：长度为n个字节的可变长度且非Unicode编码的字符数据，n必须是介于1和8000之间的数值。存储大小为输入数据的字节的实际长度，而不是n个字节。
--nvarchar(n)：包含n个字符的可变长度Unicode字符数据，n必须是介于1和4000之间，字节的存储大小是所输入的字符个数的两倍。
--所以varchar(2)最多可以存储2个字母，或者是1个汉字；而nvarchar(2)最多可以存储2个字母，或者两个汉字，就是说nvarchar(2)包含两个字符=4个字节。
--采用Unicode编码，即varchar类型的字符串时，DataLength()和Len()的区别： Len() 字符串表达式的字符数，不计尾部空格，但计头部空格和中间的空格；DataLength() 任何表达式的字节数，包括空格。
--采用非Unicode编码，即nvarchar类型的字符串时，DataLength()和Len()的区别： 与以上相同，不过DataLength()的长度为2倍。
--语句可测：declare @a nvarchar(max) set @a = 'AAA' select LEN(@a) AS a_len,DATALENGTH (@a) AS a_datalength
--sys.dm_exec_query_stats：返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行，并且行的生存期与计划本身相关联。在从缓存删除计划时，也将从该视图中删除对应行。
--该视图存放的就是当前所有执行计划的详细信息，比如某条执行计划共占CPU多少等等。因为该视图对编译次数、占用CPU资源总量、执行次数等都进行了详细的记录
--sys.dm_exec_sql_text(sql_handle | plan_handle)：根据(sql_handle|plan_handle)返回相应的sql语句|执行计划内容
SELECT execution_count,
total_logical_reads /execution_count AS [Avg Logical Reads],
total_elapsed_time /execution_count AS [Avg Elapsed Time],
db_name(execSQLText.dbid) as [database name],
object_name(execSQLText.dbid) as [object name],
object_name(execSQLText.objectid) as [object name 1],
SUBSTRING(execSQLText.text, (execQueryStats.statement_start_offset / 2) + 1, 
((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(execSQLText.text) ELSE execQueryStats.statement_end_offset END - execQueryStats.statement_start_offset)/ 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS execQueryStats 
CROSS APPLY sys.dm_exec_sql_text(execQueryStats.sql_handle) AS execSQLText
--WHERE execution_count > 100
ORDER BY 1 DESC



 --执行时间最长的命令
 --COALESCE：将空值替换成其他值,返回第一个非空值
 --apply
 --使用apply就像是先执行左语句，然后将左语句执行结果中的每一行代入右语句执行一次。所有左语句结果行代入右语句的执行结果的集合即为结论。
 --其中左语句结果行代入右语句执行时，无符合条件的结果返回。若使用cross apply则最终结果不返回该行。若使用outer apply则最终结果返回该行，且右语句属性显示为NULL
 SELECT COALESCE(DB_NAME(st.dbid),DB_NAME(CAST(pa.value as int))+'*','Resource') AS DBNAME,
SUBSTRING(text,
-- starting value for substring
CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset/2 + 1 END,
-- ending value for substring
CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1 OR statement_end_offset IS NULL THEN LEN(text) ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset/2  END + 1)  AS TSQL,
total_logical_reads/execution_count AS AVG_LOGICAL_READS
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid'
ORDER BY AVG_LOGICAL_READS DESC



--缺失索引
SELECT
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, 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
ORDER BY [Total Cost] DESC;



--查询数据库中使用了多少索引
SELECT
tab.name AS [表名],
idx.name AS [索引名称],
col.name AS [列名]
FROM sys.indexes idx 
JOIN sys.index_columns idxCol ON (idx.object_id = idxCol.object_id AND idx.index_id = idxCol.index_id)
JOIN sys.tables tab ON (idx.object_id = tab.object_id) 
JOIN sys.columns col ON (idx.object_id = col.object_id AND idxCol.column_id = col.column_id)