文档中没有介绍的SQL Server DBCC命令

文档中没有介绍的SQL Server DBCC命令

http://www.searchdatabase.com.cn/showcontent_11822.htm

http://www.searchdatabase.com.cn/showcontent_11823.htm

http://www.searchdatabase.com.cn/showcontent_11824.htm

--------------------------------------------------------------------------------------------

--以下代码为保存在测试过程中的CPU,IO,MEM相关 损耗情况。

--dbcc sqlperf(THREADS)

--dbcc sqlperf(waitstats)

 ALTER PROCEDURE [dbo].[MP_TRACE_LOG] AS
 BEGIN
    SET NOCOUNT ON
    declare @LgReads bigint  
    select @LgReads=cntr_value from master.dbo.sysperfinfo where counter_name='Page lookups/sec'   
    if object_id(N'tempdb.dbo.#waits') is not null GOTO InsertTb   
    if object_id(N'tempdb.dbo.#waits') is null GOTO CreateTb  
      
    declare @thread_qty varchar(2),@stime varchar(6)
    set @stime=substring(convert(varchar(20),getdate(),120),12,5)
    


      
      
    CreateTb:  
        create table #waits (type varchar(128), req bigint, waittime bigint, signal bigint) GOTO InsertTb  
    InsertTb:  
        insert into #waits exec('dbcc sqlperf(waitstats)')  
        insert into TST_WaitsLog   (DT,CPU,Locks,Reads,Writes,Network,PhReads,PhWrites,LgReads,threads_desc)  
            select   
                getdate() AS DT,  
                CAST(@@CPU_BUSY * CAST(@@TIMETICKS AS FLOAT) / 1000 AS BIGINT) as CPU,  -- in milliseconds  
                sum(convert(bigint, case when type like 'LCK%'   
                  then waittime else 0 end)) as Locks,  
                sum(convert(bigint, case when type like 'LATCH%'  or type like 'PAGELATCH%' or type like 'PAGEIOLATCH%'  
                  then waittime else 0 end)) as Reads,  
                sum(convert(bigint, case when type like '%IO_COMPLETION%' or type='WRITELOG'  
                  then waittime else 0 end)) as Writes,  
                sum(convert(bigint, case when type in ('NETWORKIO','OLEDB')  
                  then waittime else 0 end)) as Network,  
                @@TOTAL_READ AS PhReads, @@TOTAL_WRITE AS PhWrites, ISNULL(@LgReads, 0) AS LgReads
                  
            from #waits  
    
    CreateStateTb:  
                    
        create table #io(spid int,thread_id int,t_status varchar(100),
        t_loginname varchar(100),t_io int,t_cpu int,t_mem int)

    InsertStateTbData:
        insert into #io exec('dbcc sqlperf(THREADS)')  

        insert into TST_Statelog
        select GETDATE(),t_loginname,SUM(t_io) as tio,
        SUM(t_cpu) as tcpu,SUM(t_mem) as tmem
         from #io
        group by t_loginname
        union all
        select GETDATE(),'All Total',SUM(t_io) as tio,
        SUM(t_cpu) as tcpu,SUM(t_mem) as tmem
         from #io
    

            
END