SQL Server 2014 Transact-SQL Optimization Tips (Part 1)
Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure
or view name (perhaps with some parameters) instead of large heavy-duty queries text. This
can be used to facilitate permission management also, because you can restrict user access
to table columns they should not see.
Avoid using optimizer hints in your queries.
Because SQL Server query optimizer is very clever, it is very unlikely that you can optimize your
query by using optimizer hints, more often, this will hurt performance.
You can use inline specification of CLUSTERED and NONCLUSTERED indexes.
The inline specification of CLUSTERED and NONCLUSTERED indexes is allowed only for disk-based
tables. Creating a table with inline indexes is equivalent to issuing a create table followed
by corresponding CREATE INDEX statements.
Consider creating indexes on the very large temporary tables.
The temporary tables provides much more efficient way to access their data in comparison with
general tables, but if you work with very large temporary tables, using indexes can greatly
improve performance.
Try to design your tables in such a way as to maximize the number of rows that can
fit into one data page.
To maximize the number of rows that can fit into one data page, you should specify the narrowest
columns you can. The narrower the columns are, the lesser the data that is stored, and the faster
SQL Server is able to read and write data.
Consider using the CONTAINS MEMORY_OPTIMIZED_DATA option with the CREATE DATABASE
statement.
This option was first introduced in SQL Server 2014 and specifies that the filegroup stores
memory_optimized data in the file system.
Note. Only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database.
Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When
you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of
grouped rows and aggregates their values, and then the HAVING clause eliminates undesired
aggregated groups. In many cases, you can write your select statement so, that it will contain
only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of
your query.
Use the MAXDOP option of the CREATE INDEX statement to limit the number of
processors used in a parallel plan execution during the index creation.
Using this option with the CREATE INDEX statement overrides the “max degree of parallelism”
configuration option for the duration of the index creation. Because creating very large indexes
are resource intensive and can cause insufficient resources for other applications and database
operations, you can manually limit the number of processors to use for the index creation by
using the MAXDOP option of the CREATE INDEX statement.
Include SET NOCOUNT ON statement into your stored procedures to stop the message
indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating
the number of rows affected by a T-SQL statement.
Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you
need to return only the first n rows.
This can improve performance of your queries, because the smaller result set will be returned.
This can also reduce the traffic between the server and the clients.
Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb
is on a different set of disks than the user database.
When you create an index with the SORT_IN_TEMPDB option, SQL Server 2014 uses the tempdb database,
instead of the current database, to sort data during the index creation. Using this option can
reduce the time Database Engine takes to create an index, but increases the amount of disk space
used to store an index data.
Use the ONLINE option of the ALTER INDEX statement to reorganized or rebuilt indexes.
To rebuilt index online, use the REBUILD WITH ONLINE = ON option of the ALTER INDEX statement.
By using this statement, indexes can be rebuilt online and partitioned indexes can be reorganized
or rebuilt on a single partition basis.
Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look
for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.
If you need to update some rows, try to update them in a single UPDATE statement
instead of running the UPDATE statement multiple times.
Using a single UPDATE statement to perform the same task is generally much more efficient than
using multiple UPDATE statements.
Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost performance. So, you should use
constraints instead of triggers, whenever possible.