sql server 好用的一些管理指令

--找出最有用的索引和建立索引的方法

SELECT
avg_user_impact AS average_improvement_percentage,
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 a INNER JOIN
sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats c ON
b.index_group_handle = c.group_handle
WHERE avg_user_impact > = 40



--查出目前系統"從開機"到目前為止，查詢中沒有使用到Index的TABLE(也就是使用Table Scan的查詢有那些)

SELECT Top 10
    statement AS [database.scheme.table],
    column_id , column_name, column_usage,
    migs.user_seeks, migs.user_scans,
    migs.last_user_seek, migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
    CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)
    INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON mig.index_handle = mid.index_handle
    INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON mig.index_group_handle=migs.group_handle
ORDER BY migs.avg_user_impact DESC

--找出未使用的INDEX

SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
       I.NAME AS IndexName
FROM sys.indexes I
WHERE
-- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
-- find all indexes that exists but are NOT used
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
               AND I.index_id = index_id
               -- limit our query only for the current db
               AND database_id = DB_ID())
ORDER BY SchemaName, ObjectName, IndexName


--找出不常使用的INDEX

declare @dbid int
select @dbid = db_id()

select objectname=object_name(s.object_id), s.object_id,
          indexname=i.name, i.index_id,
          user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s, sys.indexes i
where database_id = @dbid and
      objectproperty(s.object_id,'IsUserTable') = 1
      and i.object_id = s.object_id
      and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc


--找出執行次數最多的SQL命令

SELECT TOP 100 execution_count, total_logical_reads /execution_count AS [Avg Logical Reads], total_elapsed_time /execution_count AS [Avg Elapsed Time], db_name(st.dbid) as [database name], object_name(st.dbid) as [object name], object_name(st.objectid) as [object name 1], SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE execution_count > 100 ORDER BY 1 DESC;


--找出執行時間最長的SQL命令

SELECT TOP 10 COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value as int))+'*', 'Resource') AS DBNAME
, SUBSTRING(text,
CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset/2 + 1 END,
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 ;


--找出資料庫中缺少的INDEX
SELECT TOP 10 [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;