数据库常用的内部查询

--1 按页面统计表的使用空间

select o.name,
SUM(p.reserved_page_count) as reserved_page_count,
SUM(p.used_page_count) as used_page_count,
SUM(case when (p.index_id<2) then (p.in_row_data_page_count+p.lob_used_page_count +p.row_overflow_used_page_count)
         else (p.lob_used_page_count+p.row_overflow_used_page_count)   end) as DataPages,
SUM(case when (p.index_id<2) then p.row_count else 0 end ) as rowCounts
from sys.dm_db_partition_stats p inner join sys.objects o
on p.object_id=o.object_id
group by o.name

--2  经常做重编译的存储过程

select top 20  sql_text.text , a.plan_generation_num,  a.execution_count,a.creation_time,a.last_execution_time,
a.total_elapsed_time,db_name(sql_text.dbid),  sql_text.objectid
from sys.dm_exec_query_stats a     cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num >1 --在重新编译后区分不同计划实例的序列号
order by plan_generation_num desc

--3 查看数据库当前“脏页”的数量

select DB_NAME(database_id) as 'Database',COUNT(page_id) as 'Dirty Pages'
from sys.dm_os_buffer_descriptors
where is_modified=1
group by DB_NAME(database_id)
order by COUNT(page_id) desc

--4 按DB 查看SQL Server使用的物理内存(数据页缓存)，所有的和为SQLServer:Buffer Manager Database Pages

SELECT  CASE database_id
        WHEN 32767 THEN 'ResourceDb'
        ELSE db_name(database_id)
        END AS Database_name,
        cast(count(row_count)*8.0/1024.0 as numeric(28,2)) as [size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
order by database_id