SQL Server Storage Best Practices:
Choose narrow columns for indexes
Ensure selectivity of the candidate column is high
Prefer columns with the integer data type
Place columns with higher selectivity first in multi-column indexes
Use include to create covering index
Pay attention to columns in where clause and joins
Consider benefits of clustered vs non-clustered indexes
Clustered Index guidelines:
Keep clustered indexes as narrow as possible. The non-clustered index size will increase with the size of the clustered index.
Create the clustered index first and then the non-clustered indexes
If required rebuild the clustered indexes using the DropExisiting keyword. Otherwise, the non-clustered indexes will be rebuilt twice.
Do not create he clustered index on a frequently updated column
When applicable, such as when doing aggregations, consider using a columnstore index.
Use the command SET NoCount ON
Explicitly define the owner of an object
Avoid using nonsargable search conditions
Avoid arithmetic operators and functions on WHERE clause columns
Avoid optimizer hints
Avoid nesting views and user functions. The execution plans can become very expensive. The optimizer can’t eliminate tables
Ensure there are no implicit data type conversions
Minimize logging overhead:
1) use table variables for small datasets instead of temp tables
2) Batch a number of action queries in a single transaction
3) Reduce the logging of certain operations by using the Bulk-logged recovery model
Adopt best practices for reusing execution plans
1) caching execution plans effectively
a) Avoid using non-parameterized ad hoc queries
b) enable “Optimize for Ad Hoc Workload” option
c) use the same environment settings in every connection. The execution plan is dependent upon the environment settings of the connection.
d) Qualify the owner of the objects in the queries
2) Minimize recompilation of execution plans
a) Don’t interleave DML and DDL in SPs. Put all DDL at the top of the SP.
b) In a SP, avoid using temporary tables created outside the SP.
c) Avoid recompilation
Adopt best practices for database transactions
Eliminate or reduce the overhead of database cursors