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
Right-click the SQL Server instance in the SQL Server
Management Studio Object Explorer
Select the Advanced tab
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
sp_CONFIGURE 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO |
|
|
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
1
|
DBCC FREEPROCCACHE WITH NO_INFOMSGS
|
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
- See more at:
http://www.sqlshack.com/sql-server-query-execution-plans-basics
