Performance Zone is brought to you in partnership with:

Merrick Chaffer is a Software Developer with Russell Reynolds Associates, with over 16 years experience in areas relating to Microsoft Application Development. Merrick is a DZone MVB and is not an employee of DZone and has posted 34 posts at DZone. You can read more from them at their website. View Full User Profile

TSQL Performance Tuning

12.17.2012
| 1709 views |
  • submit to reddit

Below is an example of the TSQL you can use to investigate your stored procedure's performance. Best to put this in a snippet if you're using VS 2012 or greater versions of SQL Management Studio I find.

SET NOCOUNT ON

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

CHECKPOINT

GO

PRINT ' '

PRINT '-- START: ProcName --------------------------------------------------------------------------------------------------------------------------------'

PRINT ' '

SET STATISTICS IO ON

SET STATISTICS TIME ON

EXEC dbo.MyProcNameHere

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

PRINT ' '

PRINT '-- END: ProcName --------------------------------------------------------------------------------------------------------------------------------'

PRINT ' '

GO

Published at DZone with permission of Merrick Chaffer, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)