建主键+聚集索引
ALTER TABLE [dbo].[EmpTypeLimit] ADD PRIMARY KEY CLUSTERED ([Id]) WITH (ONLINE=ON);建聚集索引（不含主键）
CREATE CLUSTERED INDEX CIX_TransactionID ON TransactionHistory(ID);建非聚集索引
CREATE NONCLUSTERED INDEX [Ind_name] ON [dbo].[table_name]
(
col1,col2 desc
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO带include子句的索引
CREATE NONCLUSTERED INDEX [Ind_name] ON [dbo].[table_name]
(
col1,col2 desc
) INCLUDE (col3,col4)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO建唯一索引
CREATE UNIQUE CLUSTERED INDEX idx_MyTable ON dbo.MyTable(Col2) WITH (ONLINE = ON);删除索引
-- 非聚集索引
DROP INDEX [Ind_name] ON [dbo].[table_name];
-- 聚集索引，可online
DROP CLUSTERED INDEX AK_BillOfMaterials ON BillOfMaterials WITH (ONLINE = ON);查询表的索引列
sp_helpindex table_name包括included列
SELECT o.name table_name,i.index_id, i.name index_name, i.type_desc,
substring(ikey.cols, 3, len(ikey.cols))AS key_cols,
substring(inc.cols, 3, len(inc.cols)) AS included_cols,
stats_date(o.object_id, i.index_id) AS statsdate,
i.filter_definition
FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id
CROSS APPLY (SELECT ', ' + c.name +
CASE ic.is_descending_key
WHEN 1 THEN ' DESC'
ELSE ''
END
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')) AS ikey(cols)
OUTER APPLY (SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH('')) AS inc(cols)
WHERE o.name = '表名'
AND i.type IN (1, 2)
ORDER BY o.name, i.index_id;查询重复索引
下面脚本对重复的定义是：除了名字不一样外， 索引字段（不含include列）、字段顺序、字段排序（asc与desc）是一样的
;WITH IndexColumns
AS ( SELECT DISTINCT
SCHEMA_NAME(o.schema_id) AS SchemaName ,
OBJECT_NAME(o.object_id) AS TableName ,
i.name AS IndexName ,
o.object_id AS [Object_ID] ,
i.index_id AS Index_ID ,
i.type_desc AS IndexType ,
( SELECT CASE key_ordinal
WHEN 0 THEN NULL
ELSE '[' + COL_NAME(k.object_id,
column_id) + '] '
+ CASE WHEN is_descending_key = 1
THEN 'Desc'
ELSE 'Asc'
END
END AS [data()]
FROM sys.index_columns k WITH(NOLOCK)
WHERE k.object_id = i.object_id
AND k.index_id = i.index_id
ORDER BY key_ordinal ,
column_id
FOR
XML PATH('')
) AS IndexColumns ,
CASE WHEN i.index_id = 1
THEN ( SELECT '[' + name + ']' AS [data()]
FROM sys.columns (NOLOCK) AS c
WHERE c.object_id = i.object_id
AND c.column_id NOT IN (
SELECT column_id
FROM sys.index_columns (NOLOCK)
AS kk
WHERE kk.object_id = i.object_id
AND kk.index_id = i.index_id )
ORDER BY column_id
FOR
XML PATH('')
)
ELSE ( SELECT '[' + COL_NAME(k.object_id,
column_id) + ']' AS [data()]
FROM sys.index_columns k WITH(NOLOCK)
WHERE k.object_id = i.object_id
AND k.index_id = i.index_id
AND is_included_column = 1
AND k.column_id NOT IN (
SELECT column_id
FROM sys.index_columns kk
WHERE k.object_id = kk.object_id
AND kk.index_id = 1 )
ORDER BY key_ordinal ,
column_id
FOR
XML PATH('')
)
END AS IndexInclude
FROM sys.indexes i WITH(NOLOCK)
INNER JOIN sys.objects o WITH(NOLOCK) ON i.object_id = o.object_id
INNER JOIN sys.index_columns ic WITH(NOLOCK ) ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
INNER JOIN sys.columns c WITH(NOLOCK) ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE o.type = 'U'
AND i.index_id <> 0 -- 0 = 堆
AND i.type <> 3 -- 3 = XML
AND i.type <> 5 -- 5 = 聚集列存储索引(SQL 2014~ SQL 2016)
AND i.type <> 6 -- 6 = 非聚集列存储索引(SQL 2014~ SQL 2016)
AND i.type <> 7 -- 7 = 非聚集哈希索引(SQL 2014~ SQL 2016)
GROUP BY o.schema_id ,
o.object_id ,
i.object_id ,
i.name ,
i.index_id ,
i.type_desc
),
DuplicatesTable
AS ( SELECT ic1.SchemaName ,
ic1.TableName ,
ic1.IndexName ,
ic1.[Object_ID] ,
ic2.IndexName AS DuplicateIndexName ,
ic1.IndexType ,
CASE WHEN ic1.index_id = 1
THEN ic1.IndexColumns + ' (Clustered)'
WHEN ic1.IndexInclude = '' THEN ic1.IndexColumns
WHEN ic1.IndexInclude IS NULL THEN ic1.IndexColumns
ELSE ic1.IndexColumns + ' INCLUDE ' + ic1.IndexInclude
END AS IndexCols ,
ic1.index_id
FROM IndexColumns ic1
JOIN IndexColumns ic2 ON ic1.object_id = ic2.object_id
AND ic1.index_id < ic2.index_id
AND ic1.IndexColumns = ic2.IndexColumns
AND ( ISNULL(ic1.IndexInclude, '') = ISNULL(ic2.IndexInclude,
'')
OR ic1.index_id = 1
)
)
SELECT SchemaName ,
TableName ,
IndexName ,
DuplicateIndexName ,
IndexType,
IndexCols ,
Index_ID ,
Object_ID ,
0 AS IsXML
FROM DuplicatesTable dt
ORDER BY 1 , 2 ,3查XML索引的重复索引
--Use the below T-SQL script to generate the complete list of duplicate XML indexes in a given database:
;WITH XMLTable
AS ( SELECT OBJECT_NAME(x.object_id) AS TableName ,
SCHEMA_NAME(o.schema_id) AS SchemaName ,
x.object_id ,
x.name ,
x.index_id ,
x.using_xml_index_id ,
x.secondary_type ,
CONVERT(NVARCHAR(MAX), x.secondary_type_desc) AS secondary_type_desc ,
ic.column_id
FROM sys.xml_indexes x ( NOLOCK )
JOIN sys.objects o ( NOLOCK ) ON x.object_id = o.object_id
JOIN sys.index_columns (NOLOCK) ic ON x.object_id = ic.object_id
AND x.index_id = ic.index_id
),
DuplicatesXMLTable
AS ( SELECT x1.SchemaName ,
x1.TableName ,
x1.name AS IndexName ,
x2.name AS DuplicateIndexName ,
x1.secondary_type_desc AS IndexType ,
x1.index_id ,
x1.object_id ,
ROW_NUMBER() OVER ( ORDER BY x1.SchemaName, x1.TableName, x1.name, x2.name ) AS seq1 ,
ROW_NUMBER() OVER ( ORDER BY x1.SchemaName DESC, x1.TableName DESC, x1.name DESC, x2.name DESC ) AS seq2 ,
NULL AS inc
FROM XMLTable x1
JOIN XMLTable x2 ON x1.object_id = x2.object_id
AND x1.index_id < x2.index_id
AND x1.using_xml_index_id = x2.using_xml_index_id
AND x1.secondary_type = x2.secondary_type
)
SELECT SchemaName ,
TableName ,
IndexName ,
DuplicateIndexName ,
IndexType ,
Index_ID ,
[Object_ID] ,
1 AS IsXML
FROM DuplicatesXMLTable dtxml
ORDER BY 1 ,
2 ,
3;获取索引读写情况
得到这些信息后就可以做初步分析，对于大部分系统而言，读操作远大于写操作，可以选出【Total Reads】远小于【Total Writes】的那些索引进行优先分析对象
SELECT OBJECT_NAME(S.[object_id]) AS object_name,
i.name AS index_name,
user_seeks,
user_scans,
user_lookups,
user_updates,
user_updates AS total_writes,
user_seeks+ user_scans + user_lookups AS total_reads,
user_updates-( user_seeks + user_scans + user_lookups ) AS difference
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
where OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
--AND OBJECT_NAME(s.[object_id])='表名'查看报表信息
如果想一次查看所有表的索引信息，可以使用sqlserver的标准报表（要在服务端执行，客户端没有该选项）
选中数据库，右键 -> Reports -> Standard Reports -> Index Usage Statistics
按表或索引大小排序
- 在要查询的数据库执行
- 根据实际需求选择按row_count / data / index_size等字段排序
select * from (
SELECT
(row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8/1024 AS reserved_MB,
a1.data/1024 * 8 AS data_MB,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
(a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END)/1024 * 8 AS index_MB,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
(a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8/1024 AS unused_MB
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN
(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT') ttt;查看新建索引进度（2014新特性）
DECLARE @SPID INT = 56;
;WITH agg AS
(
SELECT SUM(qp.[row_count]) AS [RowsProcessed],
SUM(qp.[estimate_row_count]) AS [TotalRows],
MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
[physical_operator_name],
N'<Transition>')) AS [CurrentStep]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort' , N'Index Scan')
AND
qp.[session_id] = @SPID
), comp AS
(
SELECT *,
([TotalRows] - [RowsProcessed]) AS [RowsLeft],
([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
FROM agg
)
SELECT [CurrentStep],
[TotalRows],
[RowsProcessed],
[RowsLeft],
CONVERT(DECIMAL(5, 2),
(([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
[ElapsedSeconds],
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
DATEADD(SECOND,
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
GETDATE()) AS [EstimatedCompletionTime]
FROM comp;
SELECT
node_id,
physical_operator_name,
SUM(row_count) row_count,
SUM(estimate_row_count) AS estimate_row_count,
CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count) as estimate_percent_complete
FROM sys.dm_exec_query_profiles
WHERE session_id=@SPID
GROUP BY node_id,physical_operator_name
ORDER BY node_id desc;查看索引重组进度
从SQL Server 2008开始，引入了DMV视图 sys.dm_exec_requests，percent_complete列表示命令完成的百分比，其中包括索引重组（ALTER INDEX REORGANIZE），但不包括索引重建（ALTER INDEX REBUILD）
SELECT er.session_id ,
er.blocking_session_id ,
er.status ,
er.command ,
DB_NAME(er.database_id) DB_name ,
er.wait_type ,
et.text SQLText ,
er.percent_complete
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) et
WHERE er.session_id = 57
AND er.session_id <> @@SPID;查看索引重建进度
SQL Server 2014开始提供了一个新特性：sys.dm_exec_query_profiles，它可以实时监控正在执行的查询的进度情况，使用前需要通过SET STATISTICS XML ON; 或SET STATISTICS PROFILE ON;开启（一般只需启用会话级别）。
而从SQL Server 2016 (13.x)SP1 开始，可以开启跟踪标志7412或使用query_thread_profile扩展事件。
--Configure query for profiling with sys.dm_exec_query_profiles
SET STATISTICS PROFILE ON;
GO
--Or enable query profiling globally under SQL Server 2016 SP1 or above
DBCC TRACEON (7412, -1);
GO
ALTER INDEX Your_Index_Name ON Your_Table_Name REBUILD;
GO查询语句
DECLARE @SPID INT = 53;
;WITH agg AS
(
SELECT SUM(qp.[row_count]) AS [RowsProcessed],
SUM(qp.[estimate_row_count]) AS [TotalRows],
MAX(qp.last_active_time) - MIN(qp.first_active_time) AS [ElapsedMS],
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
[physical_operator_name],
N'<Transition>')) AS [CurrentStep]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN (N'Table Scan', N'Clustered Index Scan', N'Sort' , N'Index Scan')
AND qp.[session_id] = @SPID
), comp AS
(
SELECT *,
([TotalRows] - [RowsProcessed]) AS [RowsLeft],
([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
FROM agg
)
SELECT [CurrentStep],
[TotalRows],
[RowsProcessed],
[RowsLeft],
CONVERT(DECIMAL(5, 2),
(([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
[ElapsedSeconds],
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
DATEADD(SECOND,
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
GETDATE()) AS [EstimatedCompletionTime]
FROM comp;注意：索引重组时，sys.dm_exec_query_profiles中没有数据，所以不能用来查看索引重组的进度。
查询外键字段没有索引的表，并生成对应索引创建语句
/*
One or more tables found, with foreign key constraint defined but no supporting indexes created on the foreign key columns.
SQL Server doesnt put an index on foreign key columns by default and indexing foreign key fields in referencing tables is not required.
Foreign key columns usage must evaluated to determine whether or not indexing this column will help up increase the current
workloads performance by enhancing join performance, reducing table locking (for full table scans) while cascading updates and
deletes, etc.
*/
;
WITH FKTable
AS ( SELECT SCHEMA_NAME(po.schema_id) AS 'parent_schema_name' ,
OBJECT_NAME(fkc.parent_object_id) AS 'parent_table_name' ,
OBJECT_NAME(constraint_object_id) AS 'constraint_name' ,
SCHEMA_NAME(ro.schema_id) AS 'referenced_schema' ,
OBJECT_NAME(referenced_object_id) AS 'referenced_table_name' ,
( SELECT '[' + COL_NAME(k.parent_object_id,
parent_column_id) + ']' AS [data()]
FROM sys.foreign_key_columns (NOLOCK) AS k
INNER JOIN sys.foreign_keys (NOLOCK) ON k.constraint_object_id = object_id
AND k.constraint_object_id = fkc.constraint_object_id
ORDER BY constraint_column_id
FOR
XML PATH('')
) AS 'parent_colums' ,
( SELECT '[' + COL_NAME(k.referenced_object_id,
referenced_column_id) + ']' AS [data()]
FROM sys.foreign_key_columns (NOLOCK) AS k
INNER JOIN sys.foreign_keys (NOLOCK) ON k.constraint_object_id = object_id
AND k.constraint_object_id = fkc.constraint_object_id
ORDER BY constraint_column_id
FOR
XML PATH('')
) AS 'referenced_columns'
FROM sys.foreign_key_columns fkc ( NOLOCK )
INNER JOIN sys.objects po ( NOLOCK ) ON fkc.parent_object_id = po.object_id
INNER JOIN sys.objects ro ( NOLOCK ) ON fkc.referenced_object_id = ro.object_id
WHERE po.type = 'U'
AND ro.type = 'U'
GROUP BY po.schema_id ,
ro.schema_id ,
fkc.parent_object_id ,
constraint_object_id ,
referenced_object_id
),
/* Index Columns */
IndexColumnsTable
AS ( SELECT SCHEMA_NAME(o.schema_id) AS 'schema_name' ,
OBJECT_NAME(o.object_id) AS TableName ,
( SELECT CASE key_ordinal
WHEN 0 THEN NULL
ELSE '[' + COL_NAME(k.object_id,
column_id) + ']'
END AS [data()]
FROM sys.index_columns (NOLOCK) AS k
WHERE k.object_id = i.object_id
AND k.index_id = i.index_id
ORDER BY key_ordinal ,
column_id
FOR
XML PATH('')
) AS cols
FROM sys.indexes (NOLOCK) AS i
INNER JOIN sys.objects o ( NOLOCK ) ON i.object_id = o.object_id
INNER JOIN sys.index_columns ic ( NOLOCK ) ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
INNER JOIN sys.columns c ( NOLOCK ) ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE o.type = 'U'
AND i.index_id > 0
GROUP BY o.schema_id ,
o.object_id ,
i.object_id ,
i.name ,
i.index_id ,
i.type
),
FKWithoutIndexTable
AS ( SELECT fk.parent_schema_name AS SchemaName ,
fk.parent_table_name AS TableName ,
fk.referenced_schema AS ReferencedSchemaName ,
fk.referenced_table_name AS ReferencedTableName ,
fk.constraint_name AS ConstraintName ,
fk.referenced_columns AS Referenced_Columns ,
fk.parent_colums AS Parent_Columns
FROM FKTable fk
WHERE NOT EXISTS ( SELECT 1
FROM IndexColumnsTable ict
WHERE fk.parent_schema_name = ict.schema_name
AND fk.parent_table_name = ict.TableName
AND fk.parent_colums = LEFT(ict.cols,
LEN(fk.parent_colums)) )
)
SELECT @@SERVERNAME AS InstanceName ,
DB_NAME() AS DatabaseName ,
SchemaName ,
TableName ,
Parent_Columns ,
ReferencedSchemaName ,
ReferencedTableName ,
Referenced_Columns ,
ConstraintName
INTO #ForeignKeyWithOutIndex
FROM FKWithoutIndexTable
ORDER BY DatabaseName ,
SchemaName ,
TableName;
--输出临时表数据
SELECT *
FROM #ForeignKeyWithOutIndex;
--生成外键字段缺少的索引，请抽查、检验，确认后批量执行
SELECT 'CREATE INDEX IX_' + LTRIM(RTRIM(TableName)) + '_'
+ SUBSTRING(Parent_Columns, 2, LEN(Parent_Columns) - 2) + ' ON '
+ LTRIM(RTRIM(SchemaName)) + '.' + LTRIM(RTRIM(TableName)) + '('
+ Parent_Columns + ');'
FROM #ForeignKeyWithOutIndex;
--删除临时表
DROP TABLE #ForeignKeyWithOutIndex;
参考
https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-profiles-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-2017
https://dba.stackexchange.com/questions/139191/sql-server-how-to-track-progress-of-create-index-command
https://support.microsoft.com/zh-cn/help/4053291/fix-sys-dm-exec-query-profiles-dmv-returns-wrong-estimate-row-count-in
https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/12/22/create-index-monitoring-progress/
https://www.cnblogs.com/kerrycode/p/10430929.html
分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每行记录所占用空间） - 桦仔 - 博客园
976
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
