Gathering SQL Execution Statistics

by Joe Havelick 5. February 2008 12:36

While troubleshooting SQL scripts that run inconsistently, it is often helpful to understand the time it takes to compile a query plan, assess the it, and understand how many index seeks and/or scans are required to complete it. The following code encapsulating your query will allow you to do so.

SET STATISTICS IO  ON
SET STATISTICS TIME  ON
SET STATISTICS PROFILE  ON
--SQL Statement
SET STATISTICS IO  OFF
SET STATISTICS TIME  OFF
SET STATISTICS PROFILE  OFF

Tags:

Tech Tips

Clearing Cached Query Plans

by Joe Havelick 27. January 2008 19:24

The following commands will clear any cached query plans in SQL 2005. This will allow you to better troubleshoot errors related to query plan generation.


DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Tags:

Tech Tips

Auto-formatting SQL Scripts

by Joe Havelick 7. January 2008 22:06

The importance of well formatted code can be appreciated by any seasoned professional.  This is transient across any language I've dealt with, including SQL.  Visual Studio has functionality for auto-formatting your XHTML, C#, and VB.NET code (CTRL+K, CTRL+D), but SQL Management Studio, a derivative of Visual Studio, doesn't have such a luxury for SQL/TSQL code.

Instant SQL Formatter does have a functional online formatter for neatening up your SQL script for free.  Additionally, for a price, you can download an add-in to Visual Studio, or SQL Management Studio for embedded functionality.More...

Tags:

Tech Tips

About Me

Joe Havelick is a reasonable facsimile of this photo.


profile for Joe on Stack Exchange, a network of free, community-driven Q&A sites

Recommendations