SQL Server Optimization Tips, optimal performance

SQL Server Optimization Tips

SQL Server Optimization Tips are as follows:
Restrict Query Result for performance optimization
Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic.

Restrict columns in the SQL query
Never try to return all the columns of the table when you require only a few of them. This helps in reducing network traffic.

Use stored procedure instead of heavy duty queries
Stored procedure exists as compiled objects on disk. Moreover, SQL server also keeps optimized execution plan for stored procedure. This really saves time and speeds up execution.

Avoid SQL cursor for optimal performance
You should avoid using SQL cursor since it has adverse effect on SQL server’s performance. It fetches the records row by row which results in repeated network round trips.

Use constraints instead of trigger
Trigger runs every time data gets updated even when it is not required. This adds overhead on the system.

Use table variable instead of temporary tables.
When you create a temporary table (#TABLE) it physically creates the table in tempdb. It is an added overhead to create table. A table variable doesn’t need to be created in the database, it can stay in memory and go out of scope immediately after the batch ends, just like regular variables.

Avoid using Having clause
Try to use where as far as possible instead of Having clause.

Include SET NOCOUNT ON in the stored procedure.
This prevents stored procedure to send messages indicating number of rows affected thus saves network traffic.

SQL Server Optimization Tips

SQL Server optimization tips:
- Provide appropriate resources to schema design.
- Separate OLAP and OLTP workloads.
- Normalize and then denormalize later for performance.
- Define clearly all primary and foreign key relationships.
- Define all constraints;unique and check both.
- Choose datatypes wisely.
- Partition tables horizontally as well as vertically.
- Monitor and tune major queries
- Return specific and needed rows and columns from queries.
- Avoid complex operators like NOT LIKE
- Use locking and isolation level hints to minimize locking.
- Use stored procedures efficiently
- Use temporary tables efficiently.
- Created indexes based on use
- Clustered indexed keys should be small.
- Create index on all foreign keys
- Remove unused indexes if any.
- Keep transactions short.
- Avoid table/index scans

Database Optimization question that can be asked during interview
1. What is nested join, hash join and merge join in SQL Query plan?
2. What is an Execution plan?
3. What are indexes? What are types of indexes?
4. Define B-Trees.
5. How do you see the SQL plan in textual format?
6. Is it a good database design to create indexes on the table in which lot of inserts occurs?
7. What are “Table Scan’s” and “Index Scan’s”?
8. What is “FillFactor” concept in indexes? What is the best value for “FillFactor”?
9. Define “Index statistics”.
10. How can we see statistics of an index?
11. Define Fragmentation. How can we measure Fragmentation?
12. What are the criteria to be considered while selecting an index?
13. Define “Index Tuning Wizard”.