What meant by Performance Tuning,how can we do the performance tuning on store

What meant by Performance Tuning,how can we do the
performance tuning on stored procedures and tell some steps
to do the performance tuning

Question Posted / bhaskar

3 Answers
9872 Views
TCS, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What meant by Performance Tuning,how can we do the performance tuning on stored procedures and tel..

Answer / john

PERFORMANCE TUNING:

Shortly : The performance tuning is reduce the load of the
database.

stored procedure:
--create a sample performance tuning procedure

CREATE PROCEDURE dbo.spTestDelegator (@query bit) AS
BEGIN

--NOCOUNT is use to reduce the load
--if its off the procedure get (full memory) of execution

SET NOCOUNT ON;

IF @query = 0
EXEC spTestFromAuthors
ELSE
EXEC spTestFromPublishers
GO

CREATE PROCEDURE dbo.spTestFromAuthors AS
SELECT * FROM authors
GO

CREATE PROCEDURE dbo.spTestFromPublishers AS
SELECT * FROM publishers
GO

STEPS:
1.Good code ideas
2.Reduces use more query in same procedure
3.use clear structure of code
4.always done stored procedure is good. because,Transact-Sql
get load of execution

Is This Answer Correct ?

6 Yes

1 No

What meant by Performance Tuning,how can we do the performance tuning on stored procedures and tel..

Answer / ravi yanamala

1).Check the indexes,table scans and the statics
2)check whether any stored procedures are recompiles excessively
3)check whether SET NOCOUNT is OFF for any stored procedures or triggers
4)check the query whether written poorly r not.check the database normalization as too much normalization effects performance,check the usage of the cursors and temperory tables.

Is This Answer Correct ?

4 Yes

0 No

What meant by Performance Tuning,how can we do the performance tuning on stored procedures and tel..

Answer / parmanand

Agree with John, Just to add this is a way to make our query
fast and to reduce the over load on server.

The RECOMPILE option
SET NOCOUNT ON/OFF
Creating Indexes on tables are few points to fine tune our
query.

Is This Answer Correct ?

1 Yes

0 No

Post New Answer