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 to get a list of columns in a view using the "sp_help" stored procedure?
wht is normalization?can u explain me in detail?
Do you know what is a with(nolock)?
What is a schema in ms sql server 2005?
how you can get the list of largest tables in a database? : Sql server administration
one of my database size is 2gb and Unrestricted Growth for Data file up to 10%.But every day after day I am getting Primary Data file is full 99.999 please take appropriate actions.Why it is? Even disk space is also not full,but still I am getting the alerts.
Explain go command in sql server?
Write the SQL query to drop, truncate and delete table.
0 Answers HPCL, Hughes Systique Corporation, Ittiam Systems,
What are the main differences between #temp tables and @table variables and which one is preferred?
Can the query output be sorted by multiple columns in ms sql server?
write down the sql query? Table Name : emp1 empid empname 1 bala 2 guna Table Name : emp2 empid empname 1 <Null> 2 <Null> Solution : emp1 names are updated in emp2, write a query?
When cursors are useful?