What meant by Performance Tuning,how can we do the
performance tuning on stored procedures and tell some steps
to do the performance tuning
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
How do I know if localdb is running?
Does view occupy space?
Explain table valued parameters in sql server? Why tvp used?
how can you check the level of fragmentation on a table? : Sql server administration
Explain about extended stored procedure?
Where to find ntwdblib.dll version 2000.80.194.0?
How to create an multi-statement table-valued function?
Delete duplicate rows from a table without primary key by using a single query Table Employee empname salary A 200 B 300 A 200 C 400 D 500 D 500 Output should be A 200 B 300 C 400 D 500
What is the default value of an integer data type in sql server 2005?
what are the new features introduced in sql server 2000? : Sql server database administration
How you can move data or databases between servers and databases in sql server?
how to generate XML out of QUERY?