SQL Server - How do you optimize stored procedures ?

GeekInterview.com > Interview Questions > Database > SQL Server

Go To First | Previous Question | Next Question

SQL Server | Question 73 of 94

How do you optimize stored procedures ?

Total Answers and Comments: 4

Last Update: August 07, 2008 Asked by: Raghuram

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

November 19, 2007 01:55:07

Krishna

RE: How do you optimize stored procedures ?

Inorder to increse the performance and reduce the response time.

Is this answer useful? Yes | No

February 03, 2008 07:59:40

sreyas kvm

Member Since: February 2008 Contribution: 10

RE: How do you optimize stored procedures ?

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

Is this answer useful? Yes | No

0 1

Overall Rating: -1

April 17, 2008 06:06:43

subhashsubramanyam

Member Since: April 2008 Contribution: 1

RE: How do you optimize stored procedures ?

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

Is this answer useful? Yes | No

August 07, 2008 08:07:51

anjan_maity

Member Since: April 2008 Contribution: 6

RE: How do you optimize stored procedures ?

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.

Is this answer useful? Yes | No

Go To Top