How to check SQL Server performance tuning for stored procedures

Question

I am creating stored procedures for an application that will generate some business logic in sql-server . How to check the effectiveness of stored procedures such as speed, accuracy, etc. Any ideas or direction would be highly appreciated.

Answer 1

Personally, when I test sproc speed, I use something similar to the following:

DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS; GO SET STATISTICS TIME ON; SET STATISTICS IO ON; GO EXEC <my sproc> [args] 

DBCC FREEPROCCACHE

Use DBCC FREEPROCCACHE to clear cache procedures. Releasing the procedure cache will result in re-compilation rather than re-use of the cache. If you are observing through SQL Profiler, you can observe how Cache Remove events occur when DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE invalidates all stored procedure plans that the optimizer caches in memory and forces SQL Server to compile new plans the next time these procedures are started.

DBCC DROPCLEANBUFFERS

Use DBCC DROPCLEANBUFFERS to check queries with a cold buffer cache without stopping and restarting the server. DBCC DROPCLEANBUFFERS is used to flush the data cache. Any data loaded into the buffer cache due to a previous query execution is deleted.

Over the shameless stolen from: this blog post

STATISTICS SETTING TIME ON

Displays the number of milliseconds required for parsing, compiling, and executing each statement.

Further reading (MSDN)

SETTING IO ON STATISTICS

Causes SQL Server to display the amount of disk activity generated by Transact-SQL statements.

Further reading (MSDN)

Regarding the accuracy of the request, this is what you, as a developer, should look at. I don’t think there is an automated way of checking accuracy.

Hope you get started.

Note: I do this inside SSMS