Why is query execution important for SQL Server performance?

SQL Server performance monitoring depends on the performance goals. The first step in monitoring is to determine optimal performance for your server and create performance trends based on the captured metric information. If performance is not satisfactory, it’s necessary to diagnose performance problems and find their origin. This involves testing how different queries and applications affect performance

Whatever the performance goals are, they all have one thing in common – to ensure optimal performance. It doesn’t have to be the best performance possible, sometimes you have to tradeoff between needs and cost. To achieve this, analyze available hardware resources and their performance first. Then, analyze how the resources are used. Poor designed databases, inefficient queries, bad indexing, etc. can significantly slow down the whole system

One of the first issues you notice when working with a SQL Server database is the response time. If you execute a stored procedure, query, or search for specific data, you’d like the results to appear quickly. If not, is there anything that can be done to make the query run faster?

The SQL Server query execution plan can answer this question and help you diagnose the problem, if it exists. The execution plan can also help you write efficient queries, create the right indexes that quickly provide only the rows you need, instead of searching through millions of records, etc.

What is a SQL Server query execution plan?

A query plan, execution plan, or query execution plan is an algorithm showing a set of steps in a specific order that is executed to access data in a database

A query plan shows how a query was executed, or how it will be executed which is significant for troubleshooting query performance issues. Executing a SELECT statement to find out its query plan and effect on SQL Server performance can be acceptable, but executing UPDATEs to find that out is not an option. The plan is calculated by a SQL Server component Query Optimizer using minimum of server resources. When creating the SQL Server query execution plan, the number of database objects involved, joins, indexes and their availability, number of output columns, and more is considered

When a new query is executed, Query Optimizer evaluates the query plan, optimizes and compiles it, and stores it in the plan cache. The plan cache is a part of SQL Server buffer where data and query plans are stored (buffered), so they can be reused later

When a query is executed, Query Optimizer first searches the plan cache looking for a query plan that can be reused, thus making the execution faster. If there’s no query plan that can be reused, a new one has to be created, which takes time and therefore makes query execution last longer

A very useful characteristic of query plans is that when a stored procedure is executed, the query plan is created for the stored procedure name and the same query plan will be reused whenever the stored procedure is executed, despite the values specified for procedure parameters. When it comes to executing ad hoc queries, query plans are created based on complete code, so different parameters or any change in code will prevent reuse of the existing plan. This clearly indicates what should be done to make your code run faster – wrap it up as stored procedures or functions, and the existing query plans will be reused and therefore code will be executed much faster

The slow execution of ad hoc queries can be mitigated by using the Optimize for ad hoc workloads option, introduced in SQL Server 2008. The option optimizes the plan cache use, as it solves the situation when query plans executed only once fill up the plan cache. As the buffer cache is used for both data and plan buffering, and the percentage of cache used for each changes in time depending on the current situation, it’s necessary to use the cache wisely. Instead of buffering the whole plan, when the option is set to “True”, only a fragment of the plan is buffered when the query is executed for the first time. When an ad hoc query is executed for the second time, its complete plan is buffered in the cache

The default value is False. The option in available in the SQL Server instance properties

1. Right-click the SQL Server instance in the SQL Server Management Studio Object Explorer
2. Select the Advanced tab

3. In the Miscellaneous options list, set the Optimize for ad hoc workloads option to True

   

Another way to change this option is to use T-SQL

However, keep in mind that this is an advanced option, therefore recommended to be modified only by advanced users

How to remove query plans in SQL Server?

Query plans are automatically removed from the plan cache when the SQL Server instance is restarted or memory pressure appears. Not reused plans and the ones that can be easily recompiled are first removed to resolve the memory pressure situation

To remove all query plans from cache manually, use the following statement

Using this statement is recommended for advanced users only and it should be never be used on a production server before previous analysis of the instance state, as it will cause recompilation of stored procedures and thus slowdown the system performance

How to recompile query plans in SQL Server?

Query plans are automatically recompiled whenever a cached plan becomes invalid. A plan can become invalid (or obsolete) if there is a database change. The most common causes are changes of the objects used by the stored procedure: the stored procedure itself, the referenced table or view, indexes used by the plan, statistics, significant key data changes, etc. Next time the same stored procedure is executed, Query Optimizer will find an invalid plan in the cache, and will recompile a new one. The good news is that starting with SQL Server 2005, only the statements that need recompilation are recompiled, not the whole batch

A SQL Server query plan can also be recompiled explicitly when the stored procedure is executed using the WITH RECOMPILE clause, or the sp_recompile stored procedure is executed

In this article we introduced SQL Server query execution plans – explained what they are, how they are created, and how they affect SQL Server performance. As shown, a query plan reuse makes the time needed for execution shorter, therefore it’s recommended to save the frequently used queries as stored procedures. In next part of this series, we’ll present various methods for viewing the query execution plans