HOW TO CHECK SQL Server Performance Tuning for Stored Procedures

Hi, I'm creating a stored procedures of an application that. that will generate some bussiness logic then how can i check performance of stored procedures like speed,accuracy etc. Any ideas or direction will be much appreciated

Performance is always relative. There should be some benchmark to compare the performance. Ie. today the sp is performence is slower/better than ..... .
Generally in performance tuning you can check the following steps
(a) Data fragmentation. if fragmented defag it (use DBCC SHOWCONTIG and DBCC DBREINDEX or DBCC INDEXDEFRAG)
(b) Update the statistics
(d) Create proper index according to the query
(d) check for recompilation
these are the very highlevel check list. There are many articles on performance tuning in this site. Please have a look on those
Madhu
SQL Server Blog
SQL Server 2008 Blog

this isa very high level overview of and action plan:
Check the Stored procedure code for obvious issues: Some fonthe common issues I have seen are, Cursors, Mixing of DDL and DML statements (With SQL server 2005, this is al esser problem) usage of UNION instead of UNION ALL, unnecessary DISTINCT, ORDER BY
2. Execute the stored procedure with Sample parameters. (supplying the best parameters is a little tricky at times) Monitor the time taken and IO cost involved. There are various methods to do this: Please refer this article http://sqlserveruniverse.com/content/PERF0600104282008MeasuringPerformanceOfStoredProcedures.aspx
3. Get the execution plan: Instead of Graphycal plan, use STAT STATISTICS PROFILE ON before the Execution of the procedure so that you can get the execution plan in table format. I will look for these columns: Rows, Executes, StmtText, & TotalSubTreeCost. Additionally, I look for high cost operations in the inner loops. (Higher level in the tree structure)
4. On the StmtText, I also check for certain operations: Table Scan, Clustered Index Scan, Too Many RID Lookup /Clustered Index Lookup, Join methods. If I feel that it could be done in a different way with less cost, I would try to rewrite code to do that,
This is only an overview of few things. When you start doing it, you will find issues you can resolve.
Hope this helps

You can profile it to see duration, Reads, writes and CPU. Also when you run query plans, check each components rowcounts and cost. You can decide based on all these whether your SP is performing well.