Time Test your Sprocs

Hey kids! Have you had issues with your queries running slow, or you aren't sure how to time them? Maybe you don't know exactly where your issues are or if the changes you made are helping.

If you want to time the execution results of a specific sproc, a great way to do this is to add SET STATISTICS TIME ON; before your query runs.

USE [Database]
GO
SET STATISTICS TIME ON;

DECLARE	@return_value int

EXEC	@return_value = [dbo].[Test_Sproc]
		@Id = 4532

SELECT	'Return Value' = @return_value

GO

When you add this line, you can see additional information about the CPU elapsed time. If you click on the 'Messages' tab, you can see compile and run times for your query.

This can help give you an idea of how long your query is truly taking to run.

You can also turn on the 'Include Actual Execution Plan' and see how your query is performing at each join, action, or creation in your sproc. If you turn this on, the CPU statistics we talked about above will most likely raise, so don't time test and check execution at the same time.

Your execution plan will show up on a new results tab, next to 'Messages'. This will give you percentages of where your 'cost' is going. You can determine where you may have bottlenecks and can consider reworking the joins or maybe re-indexing your tables or keys.

Once you have been able to make changes to your query, you can clear the prior results and run them again cleanly. This can give you a better idea if your changes made a difference in the execution and/or time values.

NOTE: Do not do this in Production, ONLY IN TEST

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Hopefully some of these suggestions can help you get more efficient SQL queries, and speeding up your data return!