|
??
--获取有关按平均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);
--自动重新组织或重新生成数据库中平均碎片超过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(),'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?
???,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,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],?
??????? ((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,?
??? 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?
???,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???
???????????,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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
