Interested to Buy Any Domain ? << Click Here >> for more details...
how do u do Performance tunning ?
- 1 Answers
- 5055 Views
- Infodat Technologies, Satyam, I also Faced
- E-Mail Answers
Answer Posted / bhaskar
• Table should have primary key
• Table should have minimum of one clustered index
• Table should have appropriate amount of non-
clustered index
• Non-clustered index should be created on columns of
table based on query which is running
• Following priority order should be followed when
any index is created a) WHERE clause, b) JOIN clause, c)
ORDER BY clause, d) SELECT clause
• Do not to use Views or replace views with original
source table
• Triggers should not be used if possible,
incorporate the logic of trigger in stored procedure
• Remove any adhoc queries and use Stored Procedure
instead
• Check if there is atleast 30% HHD is empty - it
improves the performance a bit
• If possible move the logic of UDF to SP as well
• Remove * from SELECT and use columns which are only
necessary in code
• Remove any unnecessary joins from table
• If there is cursor used in query, see if there is
any other way to avoid the usage of this (either by SELECT
… INTO or INSERT … INTO, etc)
There are few hardware upgrades can be considered as well
like separating index on different disk drive or moving
tempdb to another drive. However, I am not suggesting them
here as they are not quick way to improve the performance
of query.
| Is This Answer Correct ? | 6 Yes | 1 No |
Post New Answer View All Answers
How to retrieve error messages using mssql_get_last_message()?
Write a sql query to display the current date?
How can I change procedure name in sql server?
What is the filtered index?
What are the difficulties faced in cube development? : sql server analysis services, ssas
How to compose an inquiry to demonstrate the points of interest of an understudy from students table whose name begins with k?
How to encrypt Strored Procedure in SQL SERVER?
How to perform backup for certificates in sql server? : sql server security
Explain Reporting Life Cycle?
How to provide default values to stored procedure parameters?
Explain nested stored procedure. Syntax and an example for create nested stored procedure?
Explain log shipping?
What is the difference between rank and dense_rank?
What is database replication?
Thanks to some maintenance being done, the sql server on a failover cluster needs to be brought down. How do you bring the sql server down?
