其實這最近寫的幾篇文檔記跟標題的性能都有點不太占邊，具體的性能分析，用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
