The prior article discussed the Oracle library cache, the difference between a hard parse and a soft parse, and a bit on bind variable processing as well. To review, Oracle hard parsing reads in statistics, utilizes index information and creates an execution plan. Soft parsing already has the execution plan and doesn’t need to revisit the statisticsand […]
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
SQL Server optimization hints are optional parts in a SELECT statement that instruct the query optimizer to execute one specific behavior. Learn when to use them and why to use with caution.
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
Statistics provide critical information needed by SQL Server when performing query optimization, including details about the data and what the data looks like in each table within the database.
Read More