Sql Server 性能分析3 –数据库大小，数据库表大小综合性分析
Sql Server 性能分析3 –数据库大小，数据库表大小综合性分析
其实这最近写的几篇文档记跟标题的性能都有点不太占边，具体的性能分析，用Sql Server 的Profiler 将trace 信息保存到表，做具体的分析，应该会很详细的。由于数据库的增长的很快，这几天再做数据库的监控工作，主要是数据库和表的大小。
记录当天的数据库、表大小信息，并与 昨天的数据库 和 表做比较。具体Sql code 与下：
------获取数据库大小-----
/* 注意 做database 单位换算的时候1024. 的小数点不能掉，如果没有小数点，换算后以结果小点后面的值可能会被舍弃*/
Create procedure [dbo].[Proc_DBA_Database_Size]
insert into TB_DBA_Database_Size
select -- fileid,
@l_datetime as Date_Time,
'InsiteProdDG' [Database Name] ,
cast(sum(size)*8/1024. as decimal(10,2)) [DB Size],
'MB' Size_Type
-- name logic filename,
-- filename physics filename
from Srv_MES.InsiteProdDG.dbo.sysfiles
------获取数据库表记录条数-----
/* Author: wei_zhu at 2009-08-27
Description: Proc_DBA_MES_Table_Count
Log: ----
*/
ALTER procedure [dbo].[Proc_DBA_MES_Table_Count]
as
declare @l_date varchar(20)
set @l_date=convert(varchar(20),getdate(),120)
begin
insert into tb_dba_mes_table_count
select @l_date as Date_Time,
b.name as Table_Name,
max(a.rows) as Row_Count
from Srv_MES.InsiteProdDG.dbo.sysindexes a,
Srv_MES.InsiteProdDG.dbo.sysobjects b
where a.id = b.id
group by b.name
order by 3 desc
end
------获取数据库表大小----
/* 这里的数据库表是接合上面的Store Procedure 获取的表来查询的 */
Create procedure [dbo].[Proc_DBA_MES_Table_Size]
as
Declare @tb_name varchar(50)
Declare @tb_list table
(
tb_name varchar(50)
)
declare @tb_size table
(
name varchar(50),
rows varchar(50),
Reserved varchar(50),
Data varchar(50),
Index_Size varchar(50),
Unused varchar(50)
)
declare cur_tb_list cursor for
select Table_Name
from TB_DBA_MES_Table_Count
where convert(varchar(10),date_time,120) =convert(varchar(10),DATEADD(day,-1,getdate()),120)
open cur_tb_list
fetch Next from cur_tb_list
into @tb_name
while @@fetch_status=0
begin
insert into @tb_size exec Srv_MES.InsiteProdDG.dbo.sp_spaceused @tb_name
fetch Next from cur_tb_list
into @tb_name
end
insert into TB_DBA_Database_Table_Size
select convert(varchar(20),getdate(),120) as date_time,*
from @tb_size
close cur_tb_list
deallocate cur_tb_list
------获取数据库增长大小----
/* 当前的数据库大小与昨天的数据库大小做比较得来*/
Create Procedure [dbo].[Proc_DBA_Database_Size_Grow]
as
select date_time,
database_name,
db_size,
size_type
from TB_DBA_Database_Size
where database_name = 'InsiteProdDG'
and convert(varchar(10),date_time,120)=convert(varchar(10),getdate(),120)
union all
(select date_time,
database_name,
db_size*-1,
size_type
from TB_DBA_Database_Size
where database_name = 'InsiteProdDG'
and convert(varchar(10),date_time,120)=convert(varchar(10),dateadd(day,-1,getdate()),120)
)
------获取数据库表增长大小----
/* 当前的数据库表大小与昨天的数据库表大小做比较得来*/
Create Procedure [dbo].[Proc_DBA_MES_Table_Grow]
as
select convert(varchar(20),getdate(),120) as date_time,
name,
sum(rows) as rows,
sum(reserved) as reserved,
sum(data) as data,
sum(index_size) as index_size,
sum(unused) as unused,
'KB' as Size_Type
from
(select * from
(select top 10
name,
cast(rows as int) as rows,
cast(left(reserved,len(reserved)-2) as int) as reserved,
cast(left(data,len(data)-2)as int) as data,
cast(left(index_size,len(index_size)-2) as int) as index_size,
cast(left(unused,len(unused)-2) as int) as unused
from TB_DBA_Database_Table_Size
where convert(varchar(10),date_time,120)=convert(varchar(10),getdate(),120)
order by cast(rows as int) desc) t
union
select
name,
rows*-1 as rows,
left(reserved,len(reserved)-2) *-1 as reserved,
left(data,len(data)-2) *-1 as data,
left(index_size,len(index_size)-2) *-1 as index_size,
left(unused,len(unused)-2) *-1 as unused
from TB_DBA_Database_Table_Size
where convert(varchar(10),date_time,120)=convert(varchar(10),dateadd(day,-1,getdate()),120)
and name in(
select top 10 name
from TB_DBA_Database_Table_Size
where convert(varchar(10),date_time,120)=convert(varchar(10),getdate(),120)
order by cast(rows as int) desc)
) x
group by name
order by 3 desc
