Krishna

• Nov 19th, 2007

 

Inorder to increse the performance and reduce the response time.

sreyas kvm

 Profile Answers by sreyas kvm Questions by sreyas kvm

• Feb 3rd, 2008

 

Stored procedure is used to solve a business logic and also it reduce the amount of execution time in compared to query.
Stored procedures are pre-compiled procedure that resides in a server.
So we can reduce the compile time.

To create a SP,


Create procedure procedurename(@different paraneters  datatype) as
begin
//body
end

subhashsubramanyam

 Profile Answers by subhashsubramanyam 

• Apr 17th, 2008

 

Optimizing Stored Procedure involves

1) Identify long runnning queries and rectify issues associated with it

2) Identify whether they are resource intensive (Eg: I/O intensive ? Replace Cursors with set based solutions. Disk Space Intensive (Temp DB Issue)?  Increase Tempdb Space quota, Change the Recovery mode, Shrink DB etc). Memory Intensive ? DMVs to check the memory consumed by the stored procedures to run parallel.  

3) Identifying Deadlocks.. Detecting and deleting Long running stored procedures that cause deadlocks.

Thanks
Subhash Subramanyam

anjan_maity

 Profile Answers by anjan_maity 

• Aug 7th, 2008

 

There is no hard and fast rule. It depends on you SQL statements.
There some usefull guideline.

Turn on Execution plan.

Avoid cursor

Use Cluster index (try to keep it small).
If possible use 'If Exit'

Try to avoid Rebuild option.