The Last Pickle

There are a myriad of statistics SET options in sql 2005 but it looks like the first one I looked at is the simplest and the possibly the oldest: STATISTICS TIME.

It does little more than show the cpu and total time required to both parse and compile the batch and to execute it. When performance tuning a query it would provide a quick mechanism to understand the impact changes have on IO, wait and other none cpu activities.

Brad McGehee provides a more detailed look at the output but below is the result of my playing with it. It’s not the interesting, just handy.

  use AdventureWorks
GO

set statistics time on

-- simple select from address table
select * from Person.Address

/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 2 ms.

(19614 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 917 ms.
 */

 -- try two selects
 select * from Person.Address
 select * from Person.Contact

 /*
 Get a single parse and compile time and exec times
 for each of the two statements.

 SQL Server parse and compile time:
   CPU time = 16 ms, elapsed time = 86 ms.

(19614 row(s) affected)

SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 893 ms.

(19972 row(s) affected)

SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 1563 ms.
*/

-- use a correlated sub query

select
    co.FirstName,
    co.LastName
from
    Sales.SalesPerson as sp
inner join
    HumanResources.Employee as e
on
    sp.SalesPersonID = e.EmployeeID
inner join
    Person.Contact as co
on
    e.ContactID = co.ContactID
where
    exists
    (
        select
            SalesOrderID
        from
            Sales.SalesOrderHeader as orh
        where
            orh.SalesPersonID = sp.SalesPersonID
        and
            orh.TotalDue > 180000
    )  

/*
single parse + compile and a single execution time

SQL Server parse and compile time:
   CPU time = 94 ms, elapsed time = 171 ms.

(4 row(s) affected)

SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 33 ms.
*/

-- call a table value function

select
    ci.*
from
    (
        select top 100
            ContactID
        from
            Person.Contact as co
    ) as co
cross apply
    dbo.ufnGetContactInformation(co.ContactID) as ci

/*
    again execution time collapsed to a single value

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 3 ms.

(100 row(s) affected)

SQL Server Execution Times:
   CPU time = 422 ms,  elapsed time = 653 ms.
  */

  set statistics time off