查询执行中最慢的SQL语句 / 查询表占用空间 / 查询表字段结构设计
SELECT 表名 = case when a.colorder=1 then d.name else '' end, 表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end, 字段序号 = a.colorder, 字段名 = a.name, 标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end, 类型 = b.name, 占用字节数 = a.length, 长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空 = case when a.isnullable=1 then '√'else '' end, 默认值 = isnull(e.text,''), 字段说明 = isnull(g.[value],'') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 --where d.name='Sys_User' --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息 order by a.id,a.colorderSELECT 表名 = case when a.colorder=1 then d.name else '' end, 表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end, 字段序号 = a.colorder, 字段名 = a.name, 标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end, 类型 = b.name, 占用字节数 = a.length, 长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空 = case when a.isnullable=1 then '√'else '' end, 默认值 = isnull(e.text,''), 字段说明 = isnull(g.[value],'') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 --where d.name='Sys_User' --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息 order by a.id,a.colorder
--查询表字段结构设计
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
--where d.name='Sys_User' --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
order by a.id,a.colorder----------------------------------------------------------------------------------------------------------------
--查询表占用空间
-- 查询表占用空间（结果以 MB 为单位）
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
ROUND(SUM(a.total_pages) * 8 / 1024.0, 2) AS TotalSpaceMB,
ROUND(SUM(a.used_pages) * 8 / 1024.0, 2) AS UsedSpaceMB,
ROUND((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0, 2) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.NAME, p.Rows
ORDER BY
TotalSpaceMB DESC; -- 按总空间（MB）降序
----------------------------------------------------------------------------------------------------------------
--查看当前占用 CPU 资源最高的会话和其中执行的语句
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses
order by cpu desc,physical_io desc----------------------------------------------------------------------------------------------------------------
--总耗CPU最多的前20个SQL:
SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],
execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],
max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法],
qt.text [完整语法],qt.dbid, dbname=db_name(qt.dbid),qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC----------------------------------------------------------------------------------------------------------------
--执行最慢的sql语句
SELECT
(total_elapsed_time / execution_count)/1000 AS '平均时间ms',
total_elapsed_time/1000 AS '总花费时间ms',
total_worker_time/1000 AS '所用的CPU总时间ms',
total_physical_reads AS '物理读取总次数',
total_logical_reads/execution_count AS '每次逻辑读次数',
total_logical_reads AS '逻辑读取总次数',
total_logical_writes AS '逻辑写入总次数',
execution_count AS '执行次数',
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 '执行语句',
db_name(st.dbid) AS '数据库名', -- 使用 st.dbid 而不是 qt.dbid
creation_time AS '语句编译时间',
last_execution_time AS '上次执行时间'
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE
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) NOT LIKE 'tch%'
ORDER BY
total_elapsed_time / execution_count DESC;----------------------------------------------------------------------------------------------------------------
--数据库缺失的索引的信息
SELECT
mid.database_id AS [数据库ID],
mid.object_id AS [对象ID],
OBJECT_NAME(mid.object_id, mid.database_id) AS [表名],
ISNULL(mid.equality_columns, 'N/A') AS [等值条件列],
ISNULL(mid.inequality_columns, 'N/A') AS [非等值条件列],
ISNULL(mid.included_columns, 'N/A') AS [包含列],
migs.unique_compiles AS [唯一编译次数],
migs.user_seeks AS [用户查找次数],
migs.user_scans AS [用户扫描次数],
migs.last_user_seek AS [上次用户查找时间],
migs.avg_total_user_cost AS [平均用户总成本],
migs.avg_user_impact AS [平均用户影响百分比],
(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS [改进评分]
FROM
sys.dm_db_missing_index_groups mig
INNER JOIN
sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN
sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE
mid.database_id = DB_ID() -- 只考虑当前数据库
ORDER BY
[改进评分] DESC; -- 按照改进评分排序
------------------------------------------------------------------------------------------------------------------批量编写数据库缺失的索引的信息
DECLARE @CreateIndexScript NVARCHAR(MAX) = '';
SELECT @CreateIndexScript = @CreateIndexScript +
'CREATE NONCLUSTERED INDEX IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' + CAST(ROW_NUMBER() OVER (ORDER BY (migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) DESC) AS VARCHAR(10)) +
' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id)) + '.' + QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id)) +
'(' + ISNULL(mid.equality_columns, '')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL(mid.inequality_columns, '') + ')'
+ ISNULL(' INCLUDE (' + mid.included_columns + ')', '') + ';' + CHAR(13)
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
AND (migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 371367 -- 改进评分阈值
--select @CreateIndexScript
PRINT @CreateIndexScript;
----------------------------------------------------------------------------------------------------------------
--查询碎片
SELECT
DB_NAME(database_id) AS DatabaseName,
OBJECT_NAME(ios.object_id, database_id) AS TableName,
i.name AS IndexName,
ios.index_type_desc,
ios.avg_fragmentation_in_percent,
ios.fragment_count,
ios.page_count
FROM
sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED') ios
INNER JOIN
sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
WHERE
ios.database_id = DB_ID() -- 限制到当前数据库，如果要查询所有数据库，则去掉此行
ORDER BY
ios.avg_fragmentation_in_percent DESC; -- 按碎片百分比降序排列
----------------------------------------------------------------------------------------------------------------
--查询碎片化并重新组织索引
DECLARE @DatabaseName NVARCHAR(50) = 'CallCenter'; -- 指定数据库名称
DECLARE @minFragmentation FLOAT = 5.0; -- 碎片最小百分比，超过此值才会考虑重新组织或重建
DECLARE @reorgFragmentationThreshold FLOAT = 20.0; -- 超过这个百分比将进行重建而非重新组织
-- 获取索引碎片信息和索引列信息
DECLARE @IndexFragments TABLE (
DatabaseName NVARCHAR(128),
SchemaName NVARCHAR(128),
TableName NVARCHAR(128),
IndexName NVARCHAR(128),
AvgFragmentationInPercent FLOAT,
PageCount INT,
IsUnsupportedDataType BIT -- 标记是否有不支持联机重建的数据类型
);
INSERT INTO @IndexFragments (DatabaseName, SchemaName, TableName, IndexName, AvgFragmentationInPercent, PageCount, IsUnsupportedDataType)
SELECT
DB_NAME(database_id) AS DatabaseName,
SCHEMA_NAME(o.schema_id) AS SchemaName,
OBJECT_NAME(ios.object_id, database_id) AS TableName,
i.name AS IndexName,
ios.avg_fragmentation_in_percent,
ios.page_count,
CASE
WHEN EXISTS (
SELECT 1 FROM sys.index_columns ic
INNER 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 c.system_type_id IN (35, 99, 34) -- text, ntext, image types
OR COLUMNPROPERTY(ic.object_id, c.name, 'IsFileStream') = 1 -- FILESTREAM attribute
) THEN 1 ELSE 0 END AS IsUnsupportedDataType
FROM
sys.dm_db_index_physical_stats(DB_ID(@DatabaseName), NULL, NULL, NULL, 'LIMITED') ios
INNER JOIN
sys.indexes i ON ios.object_id = i.object_id AND ios.index_id = i.index_id
INNER JOIN
sys.objects o ON ios.object_id = o.object_id
WHERE
ios.avg_fragmentation_in_percent > @minFragmentation
AND OBJECT_NAME(ios.object_id, database_id) != 'Tfs';
--select * from @IndexFragments
-- 循环遍历每个需要维护的索引
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @AvgFragmentation FLOAT;
DECLARE @PageCount INT;
DECLARE @IsUnsupportedDataType BIT;
DECLARE @SqlCommand NVARCHAR(MAX);
DECLARE indexCursor CURSOR FOR
SELECT SchemaName, TableName, IndexName, AvgFragmentationInPercent, PageCount, IsUnsupportedDataType FROM @IndexFragments;
OPEN indexCursor;
FETCH NEXT FROM indexCursor INTO @SchemaName, @TableName, @IndexName, @AvgFragmentation, @PageCount, @IsUnsupportedDataType;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @IsUnsupportedDataType = 1 OR (@AvgFragmentation < @reorgFragmentationThreshold OR @PageCount < 1000)
SET @SqlCommand = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REORGANIZE;';
ELSE
SET @SqlCommand = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (ONLINE = ON);';
PRINT 'Executing: ' + @SqlCommand;
IF @IsUnsupportedDataType = 0
BEGIN
EXEC sp_executesql @SqlCommand;
END
ELSE
BEGIN
-- 对于包含不支持数据类型的索引，仅打印命令而不执行
PRINT 'Skipping Online Operation for: ' + @SchemaName + '.' + @TableName + '.' + @IndexName;
END
FETCH NEXT FROM indexCursor INTO @SchemaName, @TableName, @IndexName, @AvgFragmentation, @PageCount, @IsUnsupportedDataType;
END
CLOSE indexCursor;
DEALLOCATE indexCursor;
SELECT mid.database_id AS [数据库ID], mid.object_id AS [对象ID], OBJECT_NAME(mid.object_id, mid.database_id) AS [表名], ISNULL(mid.equality_columns, 'N/A') AS [等值条件列], ISNULL(mid.inequality_columns, 'N/A') AS [非等值条件列], ISNULL(mid.included_columns, 'N/A') AS [包含列], migs.unique_compiles AS [唯一编译次数], migs.user_seeks AS [用户查找次数], migs.user_scans AS [用户扫描次数], migs.last_user_seek AS [上次用户查找时间], migs.avg_total_user_cost AS [平均用户总成本], migs.avg_user_impact AS [平均用户影响百分比], (migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS [改进评分] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE mid.database_id = DB_ID() -- 只考虑当前数据库 ORDER BY [改进评分] DESC; -- 按照改进评分排序
浙公网安备 33010602011771号