SQLServer 性能分析语句

系统资源占用SQL

利用系统视图（[sys].[dm_exec_query_stats]）,查询计划的聚合性能统计信息
官方说明:
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2012/ms189741(v=sql.110)?redirectedfrom=MSDN

USE database_name
GO

SELECT TOP(50) 
	([a].[total_logical_reads]/[a].[execution_count]) avg_logical_reads,
	([a].[total_logical_writes]/[a].[execution_count]) avg_logical_writes,
	([a].[total_physical_reads]/[a].[execution_count]) avg_physical_reads,
	[a].[execution_count],
	[a].[last_execution_time] '上次执行时间',
	[a].[total_elapsed_time]/1000 '执行总耗时(ms)',
	[a].[total_worker_time]/1000 '编译计划CPU耗时(ms)',
	[a].[last_worker_time]/1000 '上次执行CPU耗时(ms)',
	[b].[text] [text],
	[c].[query_plan]
FROM [sys].[dm_exec_query_stats] a
	CROSS APPLY [sys].[dm_exec_sql_text]([a].[sql_handle]) b
	CROSS APPLY [sys].[dm_exec_query_plan]([a].[plan_handle]) c
	ORDER BY ([a].[total_logical_reads]+[a].[total_logical_writes])/[a].[execution_count] DESC;
	

表容量

查询数据库每个表的总数及空间使用情况

USE database_name
GO

IF OBJECT_ID('tempdb..#TablesSizes') IS NOT NULL
 DROP TABLE #TablesSizes
CREATE TABLE #TablesSizes(
       [TableName] sysname,
       [Rows] BIGINT,
       [reserved] VARCHAR(100),
       [data] VARCHAR(100),
       [index_size] VARCHAR(100),
       [unused] VARCHAR(100)
)
DECLARE @sql VARCHAR(MAX)
	SELECT @sql=COALESCE(@sql,'')+'INSERT INTO #TablesSizes execute sp_spaceused'''+QUOTENAME(TABLE_SCHEMA,'[]')+'.'+QUOTENAME(Table_Name,'[]')+''''
FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_TYPE]='BASE TABLE'
PRINT(@sql)
EXECUTE(@sql)
SELECT * FROM [#TablesSizes] ORDER BY [Rows] DESC;

分区文件

查询表分区文件信息

USE database_name
GO

SELECT CONVERT(VARCHAR(50), [b].[name]) AS partition_scheme,
	[e].[partition_number],
	CONVERT(VARCHAR(50),[d].[name]) AS 'FileGroup',
	CONVERT(VARCHAR(19),ISNULL([g].[value], ''), 120) AS range_boundary,
	STR([e].[rows],9) AS [rows]
FROM [sys].[indexes] a
	JOIN [sys].[partition_schemes] b ON [b].[data_space_id]=[a].[data_space_id]
	JOIN [sys].[destination_data_spaces] c ON 
	[c].[partition_scheme_id]=[a].[data_space_id]
	JOIN [sys].[data_spaces] d ON [d].[data_space_id]=[c].[data_space_id]
	JOIN [sys].[partitions] e ON [e].[partition_number]=[c].[destination_id] AND [e].[object_id]=[a].[object_id] AND [e].[index_id]=[a].[index_id]
	JOIN [sys].[partition_functions] f ON [f].[function_id]=[b].[function_id]
	LEFT JOIN [sys].[partition_range_values] g ON [g].[function_id]=[f].[function_id] AND [g].[boundary_id]=[e].[partition_number] - [f].[boundary_value_on_right]
	WHERE [a].[object_id]=object_id('table_name') --分区表名
	AND [a].[index_id] IN(0,1) ORDER BY [e].[partition_number];