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 automatically create a log when an exception is being received into SQL Server?
What is the difference between a local and a global variable?
Define candidate key, alternate key, composite key.
13 Answers Infosys, Software India,
write query for fourth maximum salary from employee table
14 Answers Mind Tree, SP Software,
i want table name basis on column name.
How to recompile stored procedure at run time?
Explain what are partitioned views and distributed partitioned views?
Why do we use sql limitations? Which constraints can we use while making a database in sql?
Working with TLogs
What do you mean by an execution plan? How would you view it?
What is difference between Datepart() and Datename() in SqlServer?
what is a traditional network library for sql servers? : Sql server database administration