how do u do Performance tunning ?
• 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 |
How to retrieve range of 10th rows to 20 th rows from total rows from a database table.? (Not from Dataset)
10 Answers Cognizant, Infosys,
What the class forname () does?
What are the lambda triggers?
Is it possible to have clustered index on separate drive from original table location?
How can you start sql server in different modes?
Can we rewrite subqueries into simple select statements or with joins? Example?
Find top Nth employee from each department in terms of salary?
What is the purpose of sql profiler in sql server? : sql server database administration
Where do we use trace frag?
How do triggers work?
How to include text values in sql statements?
How many clustered indexes there can be on table ?