CODEWITHSUNDEEP
sql-server
rmdussa
rmdussa

Reputation: 1557

Check Procedures Performance

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.

Upvotes: 1

Views: 186

Answers (4)

John Sansom
John Sansom

Reputation: 41899

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.

Upvotes: 2

M.Turrini
M.Turrini

Reputation: 738

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

sp_helpstats <procedure_name>

Upvotes: 0

Nick Kavadias
Nick Kavadias

Reputation: 7368

Do you mean SET STATISTICS IO ON ?

Upvotes: 0

thijs
thijs

Reputation: 3465

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

Upvotes: 0

Related Questions