如何监视SQL Server的性能

请查看这个SQL Script

 

--insert into tblock
select getdate() DT,a.spid,a.kpid,a.blocked,a.waittime
,a.hostprocess,a.dbid,DB_NAME(a.dbid) [Database Name]
,a.Status,a.uid,a.Hostname,a.program_name,a.cmd,a.Loginame
,[text] 
from master..sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle)
where spid>50 and (kpid>0 or waittime>0 or blocked>0)
 

查看table使用的空间大小，

CREATE TABLE [dbo].#tableinfo(
name [varchar](50),
[rows] [int] NULL,
reserved [varchar](50), 
data [varchar](50),
index_size [varchar](50),
unused [varchar](50)
)
insert into #tableinfo(name,[rows],reserved,data,index_size,unused)
exec sp_MSforeachtable "exec sp_spaceused '?'"

update #tableinfo set reserved=replace(reserved,'KB',''),data=replace(data,'KB',''),index_size=replace(index_size,'KB',''),unused=replace(unused,'KB','')

select * from #tableinfo
order by convert(int,data) desc

drop table #tableinfo