SQL Server Performance

Using the output of SET STATISTICS TIME or IO

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by andrewr, Oct 10, 2008.

  1. andrewr New Member

    How do I store the output of SET STATISTICS TIME or IO in a table?
  2. MohammedU New Member

    Welcome to the forum...
    I don't think there is any way to do this and I will check if there any option...interesting question though [:)]
  3. MohammedU New Member

    Here is the code...but I don't know how useful it will be...
    use tempdb
    go
    create proc pro
    as
    set nocount on
    select * into #test from sysobjects
    go
    create table Tbl (statout Varchar(8000))
    declare @rc Varchar(1000)
    set exec @rc = pro
    insert into Tbl
    select @rc
    select * from Tbl
  4. gurucb New Member

    I tried this code and it gives me an error at set exec @rc = pro. Removed Set and executed the proc resulted in 0 as the output. Also tried with including other set options (profile, IO.. ) in proc but still it resulted in 0. not sure what I am doing wrong..
    In SQL Server 2005, we can use DMV's to achieve the same but if I can get for sql server 2000 that would be great.
  5. andrewr New Member

    Thanks for your help,but I am afraid that I couldnt get the code you posted to work either MohammedU. gurucb, which DMV's are you refering to that could achieve the same result?
    I might have a possible solution to this problem, please let me know if there are any flaws in it...
    SET STATISTICS TIME ON
    DECLARE @CPU_B4 INT, @CPU_After INT
    SELECT @CPU_B4 = cpu FROM sys.sysprocesses WHERE spid = @@spid
    -- **********QUERY TO BE TUNED *************
    SELECT col1 from Table2
    -- *****************************************
    SELECT @CPU_After = cpu from sys.sysprocesses where spid = @@spid
    SELECT @CPU_After-@CPU_B4 AS [CPU time]
    SET STATISTICS TIME OFF

Share This Page