Check Procedures Performance

Question

I would like to know the procedure which will give the out put about Name of the procedures and no.of reads in a perticular database,So that I can work on most read procuderes to improve the performance. Not with sql profiler,need tsql query to return all procedures and number of reads.

Answer 1

Try installing the Sql Server Performance dashboard, that will give you a nice idea about what is happening on your server.

For Sql 2005: http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en For Sql 2008: http://blogs.technet.com/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx

Of course, you should also look at the execution plan of your procedure. http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx

Answer 2

Are you referring to sp_who?

Answer 3

Do you mean SET STATISTICS IO ON ?

Answer 4

I'm not quite sure I fully understood your question, but have you tried with

sp_helpstats <procedure_name>

Answer 5

The following article provides instruction on how to write a T-SQL query and also provides a stored procedure for identifying the poorest performing SQL Server queries.

http://www.databasejournal.com/features/mssql/article.php/3802936/Finding-the-Worst-Performing-T-SQL-Statements-on-an-Instance.htm

This solution uses the Dynamic Management Views(DMV's) which are available only in SQL Server 2005 onwards.

I hope this answers your query.