查询磁盘和索引使用相关脚本

1、查询各个磁盘分区的剩余空间：

        Exec master.dbo.xp_fixeddrives  

 

2、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）   

     select * from [数据库名].[dbo].[sysfiles]

     转换文件大小单位为MB：

    select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

 

3、查询当前数据库的磁盘使用情况：

    Exec sp_spaceused

 

４、查询数据库服务器各数据库日志文件的大小及利用率

    DBCC SQLPERF(LOGSPACE)

 

--找到 那个死锁的进程

SELECT blocking_session_id '阻塞进程的ID', wait_duration_ms '等待时间(毫秒)', session_id '(会话ID)' FROM sys.dm_os_waiting_tasks

 

--查询当前活动的锁管理器资源的信息

SELECT resource_type '资源类型',request_mode '请求模式',request_type '请求类型',request_status '请求状态',request_session_id '会话ID',resource_subtype 资源类型 FROM sys.dm_tran_locks

 

5.查询各表占用空间

SELECT t.name AS TableName ,

s.name AS SchemaName ,

p.rows AS RowCounts ,

SUM(a.total_pages) * 8/1000000 AS TotalSpaceGB ,

SUM(a.used_pages) * 8/1000000 AS UsedSpaceGB ,

( SUM(a.total_pages) - SUM(a.used_pages) ) * 8/1000000 AS UnusedSpaceGB

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

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE t.name NOT LIKE 'dt%'

AND t.is_ms_shipped = 0

AND i.object_id > 255

GROUP BY t.name ,

s.name ,

p.rows

ORDER BY TotalSpaceGB DESC,p.rows DESC;

 

6.--索引详细情况

DECLARE @tbl NVARCHAR(265)

SELECT @tbl = 'PositionRunStateLog'

SELECT o.name,i.index_id, i.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 stats_date,

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 = @tbl

AND i.type IN (1, 2)

ORDER BY o.name, i.index_id

 

7.--表上索引读写次数

SELECT OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,

i.name AS [Index Name] ,

i.index_id ,

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 ddius WITH ( NOLOCK )

INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON ddius.[object_id] = i.[object_id]

AND i.index_id = ddius.index_id

WHERE OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1

AND ddius.database_id = DB_ID()

AND OBJECT_NAME(ddius.[object_id])='PositionRunStateLog'

AND i.index_id > 1 --非聚集索引

ORDER BY [Difference] DESC ,

[Total Writes]DESC ,

[Total Reads]ASC;

 

8.索引使用情况

SELECT user_seeks * avg_total_user_cost *( avg_user_impact *0.01 ) AS [index_advantage] ,

dbmigs.last_user_seek ,

dbmid.[statement] AS [Database.Schema.Table],

dbmid.equality_columns ,

dbmid.inequality_columns ,

dbmid.included_columns ,

dbmigs.unique_compiles ,

dbmigs.user_seeks ,

dbmigs.avg_total_user_cost ,

dbmigs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )

INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle

WHERE dbmid.[database_id] = DB_ID()

ORDER BY index_advantage DESC;

 

9.--获取某个索引被使用的情况

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT COALESCE(DB_NAME(p.dbid)

,p.query_plan.value('(//RelOp/OutputList/ColumnReference/@Database)[1]','nvarchar(128)'))

AS database_name

,DB_NAME(p.dbid) + '.' + OBJECT_SCHEMA_NAME(p.objectid, p.dbid)

+'.' + OBJECT_NAME(p.objectid, p.dbid) AS OBJECT_NAME,

cast ('索引名' as varchar(64)) AS IndexName

,cp.objtype

,p.query_plan

,cp.UseCounts AS use_counts

,cp.plan_handle

,CAST('' AS XML) AS sql_text

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) p

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS q

WHERE cp.cacheobjtype = 'Compiled Plan'

AND p.query_plan.exist('//Object[@Index = "索引名"]') = 1

ORDER BY UseCounts DESC,COALESCE(DB_NAME(p.dbid), p.query_plan.value('(//RelOp/OutputList/ColumnReference/@

Database)[1]','nvarchar(128)'))

 

10.--查出外键关联的表和列

select

a.name as 约束名,

schema_name(A.schema_id) 架构名,

object_name(b.parent_object_id) as 外键表,

d.name as 外键列,

object_name(b.referenced_object_id) as 主健表,

c.name as 主键列

from sys.foreign_keys A

inner join sys.foreign_key_columns B on A.object_id=b.constraint_object_id

inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id

inner join sys.columns D on B.referenced_object_id=d.object_id and B.referenced_column_id=D.column_id

where object_name(B.referenced_object_id)='PersonDocuments';--表名