--获取有关按平均CPU 时间排在最前面的五个查询的信息 SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.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 ORDER BY total_worker_time/execution_count DESC;
--返回按批执行的SQL 查询的文本，并提供有关它们的统计信息。 --返回按批执行的SQL 查询的文本，并提供有关它们的统计信息。 SELECT top 20 s2.dbid, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY (total_worker_time/execution_count) desc,execution_count desc;
--为变更数据捕获日志扫描会话中遇到的每个错误返回一行 select * from sys.dm_cdc_errors
--返回AdventureWorks 数据库中Person.Address 表的所有索引和分区的信息。执行此查询至少需要对Person.Address 表具有CONTROL 权限 DECLARE @db_id smallint; DECLARE @object_id int; SET @db_id = DB_ID(N'AdventureWorks'); SET @object_id = OBJECT_ID(N'AdventureWorks.Person.Address'); IF @db_id IS NULL BEGIN; PRINT N'Invalid database'; END; ELSE IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL); END; GO
--返回所有表和索引的信息 SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);
--自动重新组织或重新生成数据库中平均碎片超过10％的所有分区 -- Ensure a USE <databasename> statement has been executed first. SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor. OPEN partitions;
-- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' + @command; END;
-- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions;
-- Drop the temporary table. DROP TABLE #work_to_do; GO
--表及其索引的全部分区的所有计数 SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.dc_info_backup');
--返回有关在服务器上打开时间超过指定时间（小时）的游标的信息。 SELECT creation_time, cursor_id, name, c.session_id, login_name FROM sys.dm_exec_cursors(0) AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE DATEDIFF(hh, c.creation_time, GETDATE()) > 36;
--查找连接到服务器的用户 SELECT login_name ,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name;
--查找长时间运行的游标 USE master; GO SELECT creation_time ,cursor_id ,name ,c.session_id ,login_name FROM sys.dm_exec_cursors(0) AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;
--查找具有已打开事务的空闲会话 SELECT s.* FROM sys.dm_exec_sessions AS s WHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );
--返回前五个查询的SQL 语句文本和平均CPU 时间。
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.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 ORDER BY total_worker_time/execution_count DESC;
--返回按批执行的SQL 查询的文本，并提供有关它们的统计信息。 SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;
--返回每个数据库的缓存页计数
SELECT count(*)AS cached_pages_count ,CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS Database_name FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY cached_pages_count DESC;
--返回当前数据库中每个对象的缓存页计数
SELECT count(*)AS cached_pages_count ,name ,index_id FROM sys.dm_os_buffer_descriptors AS bd INNER JOIN ( SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3) UNION ALL SELECT object_name(object_id) AS name ,index_id, allocation_unit_id FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2 ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id WHERE database_id = db_id() GROUP BY name, index_id ORDER BY cached_pages_count DESC;
--确定群集服务器实例上的节点 SELECT * FROM sys.dm_os_cluster_nodes
--sql 性能计数 Select * from sys.dm_os_performance_counters
--获取某个数据库表的字段数、记录数、占用空间和索引空间大小 set nocount on exec sp_MSForEachTable @precommand=N' create table ##( id int identity, 表名sysname, 字段数int, 记录数int, 保留空间Nvarchar(10), 使用空间varchar(10), 索引使用空间varchar(10), 未用空间varchar(10))', @command1=N'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ''?'' update ## set 字段数=(select count(*) from syscolumns where id=object_id(''?'')) where id=scope_identity()', @postcommand=N'select * from ## order by 记录数desc drop table ##' set nocount off
--获取数据表的信息 ALTER proc [dbo].[usp_dc_info_tableinfo] as declare @a nvarchar(50),@b nvarchar(max) declare cur cursor for select name from sys.databases where database_id>=5 and database_id not in (23) open cur fetch cur into @a while (@@fetch_status=0) begin set @b=' use '+@a+' declare @s varchar(1000) begin create table #ip(id int identity(1,1),re varchar(200)) set @s=''ping ''+left(@@servername,charindex(''\'',@@servername+''\'')-1)+'' -a -n 1 -l 1'' insert #ip(re) exec master..xp_cmdshell @s
create table #statistic (Tablename nvarchar(50),rows int,reserved nvarchar(50),data nvarchar(50),index_size nvarchar(50),unused nvarchar(50),InsertDate datetime default getdate()) Insert into #statistic(Tablename,rows,reserved,data,index_size,unused) execute sp_msforeachtable ''sp_spaceused''''?'''''' Insert into DBcenter..dc_info_tableinfo Select * from (select IP=stuff(left(re,charindex('']'',re)-1),1,charindex(''['',re),''''),DatabaseName=db_name() from #ip where id=2 ) a cross join #statistic b end
drop table #statistic drop table #ip ' execute (@b) print @b fetch cur into @a end close cur deallocate cur
--显示锁信息 SELECT resource_type, resource_associated_entity_id, request_status, request_mode,request_session_id, resource_description FROM sys.dm_tran_locks --WHERE resource_database_id = 6
--显示阻塞信息 SELECT t1.resource_type, t1.resource_database_id, t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.blocking_session_id FROM sys.dm_tran_locks as t1 INNER JOIN sys.dm_os_waiting_tasks as t2 ON t1.lock_owner_address = t2.resource_address;
--显示磁盘可用空间 EXEC master..xp_fixeddrives
