none
Definitive time to run a batch

    Question

  • I am calling old stored procedure and their newly modified equivalents in SSMS.  I would like to see the exact time it takes to execute each.  However the time in the lower right corner of thequery results is insufficiently detailed as in many cases it shows both the old and the new to take 0 seconds.  I would like to be able to see the exact number of milliseconds it takes to run each.  I am sure I read about a means to do that but I not be able to locate the document I read.

    Does anyone know how to do this.


    Edward R. Joell MCSD MCDBA

    Monday, July 15, 2013 3:11 PM

Answers

All replies

  • "Include Client Statistics"?

    Tibor Karaszi, SQL Server MVP | web | blog

    Monday, July 15, 2013 6:22 PM
    Moderator
  • "Include Client Statistics"? 

    Please be more explicit.


    Edward R. Joell MCSD MCDBA

    Monday, July 15, 2013 7:38 PM
  • Please be more explicit.

    In you query editor do a right mouse click => "Include Client Statistics"

    See also http://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/


    Olaf Helper

    Blog Xing

    Tuesday, July 16, 2013 6:37 AM
  • I will test this out and see.

    Edward R. Joell MCSD MCDBA

    Tuesday, July 23, 2013 3:38 PM
  • What version of SQL Server are you using?  You can use Use Profiler or Extended Events to track individual proc times.  Extended Events is easier to use in SQL 2012 however Jonathan Keheyias has released an SSMS add-in to help with this in the past and more recently an add-in for SSMS 2012 to manage older instances.  See here.

    Whichever method you choose, add a filter for the spid / session_id you are running the tests in to reduce the amount of data you are tracing.  Be aware you may need to clear the buffer pool (DBCC DROPCLEANBUFFERS) between each test to get truly comparable results - but do not do this on a production environment!

    Tuesday, July 23, 2013 5:22 PM
  • SS 2008 R2.


    Edward R. Joell MCSD MCDBA

    Tuesday, July 23, 2013 6:54 PM
  • SET STATISTICS TIME ON

    Tuesday, July 23, 2013 10:44 PM
  • "Include Client Statistics" statistics worked. 

    I am curious about some ofthe findings though.  Not what I've been given to expect.   I was told that you should always set a field equal to a value in the Where clause to allow thetable to use an index seek instead of a table scan.  But in my stored procedures I am seeing the SP with DataPart functions on both sides of the = sign in the where clause, get processed faster than those evaluating a datetime field with a between using dataAdd functions. 

    However this thread is not the place for this discussion  For those interested see http://social.msdn.microsoft.com/Forums/en-US/d0f85b66-7459-4481-af85-e7eeb6832fc1/who-wrote-this-article-on-sql-server-best-practices


    Edward R. Joell MCSD MCDBA

    Wednesday, July 24, 2013 12:50 PM