MySQL has a small number of query hints that can affect performance. There are hints that affect the total query and those that affect how individual table indexes are used. Total Query Hints All of the total query hints occur directly after the SELECT keyword. These options include SQL_CACHE, SQL_NO_CACHE, SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CALC_FOUND_ROWS, and […]
Read More
Working with SQL Server is always a challenge. As developers try to troubleshoot SQL Server performance problems, the first step that take is to look at the queries. This is the most basic step and most important step for most developers. Developers love these challenges of optimization because they can get the maximum visible performance improvements in their […]
Read More
SQL Server plan guides allow you to use a particular optimization hint without changing the syntax of the SELECT statement. Learn how.
Read More
Sometimes the USE PLAN N query hint is used to solve deadlock issues or other data-related problems. This can provide a quick workaround to keep the system running while the root cause of a problem is found and fixed.
Read More
The RECOMPILE query hint is a more granular way to force recompilation in a stored procedure to be at the statement level rather than using the WITH RECOMPILE option.
Read More
The OPTIMIZE FOR hint enables you to specify which values you expect to see most commonly at runtime, and it can result in better performance for the majority of the queries.
Read More
On systems where parallel plans are possible, and in the case of a query where a parallel plan is being generated, using MAXDOP (n) allows the Query Optimizer to use only n workers.
Read More
The JOIN query hint applies to all joins within the query. While this is similar to the join hint that can be specified for an individual join between a pair of tables within a large more complex query, the query hint applies to all joins within the query, whereas the join hint applies only to the pair of tables in the join with which it is associated.
Read More
Use the FAST number_rows query hint when you want to retrieve only the first n rows out of a relatively large result set.
Read More
Query hints are an easy way to influence the actions of query optimization. However, you need to very carefully consider their use, as in most cases SQL Server is already choosing the right plan.
Read More