what mechanism can be used on sql servers in order to lock them down?

April 07, 2022 Post a Comment

By: | Updated: 2018-07-23 | Comments | Related: More > Query Plans

Trouble

When an application submits a query to the SQL Server database engine for the first time, a query plan is prepared and complied in order to execute it and so the plan is stored in memory. In subsequent executions of the query, SQL Server will try to re-use this optimized plan from the query plan cache. When SQL Server tries to use this query plan it might discover issues or changes that require a recompilation. Due to the recompilation, performance can be impeded. There are a number of reasons why a query recompilation can occur which I will describe in this tip.

Solution

To improve empathize recompilations, offset nosotros will expect at the compilation and caching machinery that SQL Server uses.

SQL Server Query Programme Compilation

At a high level, the query processor goes through multiple phases before producing a query plan in SQL Server. First it parses and normalizes the SQL Server statements then information technology compiles and optimizes the statements. Afterward that, as a function of the query execution process, the query plan is generated and stored in memory.

When a query program is cached and there are subsequent executions of the same query, the optimizer decides whether to create a new plan or apply the existing plan. There are several reasons why a new programme could be generated such as changes in statistics, cardinality interpretation, recompilation, etc.

SQL Server Retention Allocation in Programme Cache

Basically, four types of objects are stored in the Plan Enshroud and SQL Server decides the advisable allocation of memory for the Program Cache from the Buffer Pool.

Object Plans
- Stored Procedures, Functions, Triggers
SQL Plans
- Buried Plans, Prepared Plans, Auto-Parameterized Plans
Bound Trees
- Views, Constraints and Defaults which are internal structures
Extended Stored Procedures
- Extended Procedures are system procedures like xp_cmdshell and sp_executesql

Nigh of the memory allocated to the buffer-pool is used for data caching. Despite how the memory is allocated to the query program cache, the total memory usage depends on which SQL Server version is used and also the operating organisation. It might exist possible to exceed the memory limits due to internal or external memory pressure level and this could crusade the cache to be flushed.

Equally per MSDN, below are the SQL Server version memory cache limits.

SQL Server Version	Enshroud Pressure Limit
SQL Server 2012 SQL Server 2008 SQL Server 2005 SP2	75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
SQL Server 2005 RTM SQL Server 2005 SP1	75% of visible target retentiveness from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB
SQL Server 2000	SQL Server 2000 4GB upper cap on the programme enshroud

SQL Server Recompilation

When a T-SQL/Batch executes, before SQL Server begins executing the query, the database engine fist checks the validity and optimizes the query plan. If any check fails, then the T-SQL argument is compiled once more or the corresponding batch will be compiled once more and perchance a different query plan can be produced. Such types of compilations are known as recompilations. The Recompilation Threshold (RT) limit also plays a part. Besides, the utilize of temporary tables and the amount of data in temporary tables could cause a recompilation to occur.

Recompilations can be a headache for DBAs, although most recompilations in SQL Server exercise not touch on performance, sometimes recompilations slow down query execution due to frequent recompilations and cause operation bottlenecks.

SQL Server Recompilation Reasons

There are multiple reasons why a recompilation can occur. A recompile of an execution programme could be due to database level configuration changes, schema changes, index changes, etc.

The following query shows the dissimilar recompilation reasons. This was executed in SQL Server 2017 Developer Edition.

statement recompile cause

We tin can get data about why a recompile occurred by using the sys.dm_os_performance_counters SQL Server DMV and filter on counter_name for the database. We tin also use Extended Events and Profiler.

Capturing Recompilation Events Using SQL Server Profiler

In SQL Server Profiler, nosotros desire to capture events SP:Recompile and SQL:StmtRecompile every bit shown below.

SQL Server Profiler which is capturing event of Query Recompilation.

Using SP:Recompile we can capture procedure level recompilation events and using SQL:StmtRecompile we tin capture statement level recompilation events. Here, I accept set both options and started a trace.

sql server query recompilation 003

I accept found recompilation events with the reason three - Deferred compile. In brusque, nosotros can capture these events with the assistance of multiple options, but we need to have activity if the server has a large workload, so we don't impact performance while collecting additional data using Profiler.

Recompilations can be beneficial and generate a better query plan that has different parameters, because this can assistance avert issues like parameter sniffing or cause plan cache bloat. Developers tin besides effect a recompile if needed during testing. Recompilations that occur oftentimes can cause a performance bottleneck, then I will cover some workaround solutions.

Workaround Solutions for SQL Server Recompilations

Here I volition embrace some workarounds for SQL Server recompilations.

Machine-parameterized queries and other options

With advertizing-hoc queries where similar types of queries are executed multiple times; for select, insert, update, delete and merge, if in that location is not a parameter the query program may not exist prepared well. The text does non match due to case and space sensitivity. Nosotros could configure auto-parameterized queries, simply nosotros need to make certain the server does take an ad-hoc query workload and queries are running fine from a performance perspective. We tin can also use a Program Guide which allows the query optimizer to use a specific programme for the query by using the query level hint Utilise Program with the query.

Change execution methods in dynamic T-SQL lawmaking

Using stored procedure design methods is a key factor for better beliefs than dynamic-SQL. However, if you demand to use dynamic T-SQL code, then sp_executeSQL is more efficient than using EXECUTE to execute a SQL statement built as a cord when changing parameter values are the only variation. Because the T-SQL argument itself remains constant and the only change is to the parameter values, the optimizer can reuse the query plan which is built on the first execution.

Changing Set options tin can also bear upon the plan cache

There are sure behavior changes in the query caching based on how the SET options are applied. For instance, when using Showplan_? or No_Exec, the plan is compiled, merely not buried. This could also affect argument level caching likewise, which means it needs to know the caching beliefs when it is used at the query level.

Need to properly apply query level recompilation hints

At that place are a number of ways to use recompilation hints at the stored procedure and query level. At that place are certain options bachelor, for an example: creating a stored procedure with the recompile option, employ exec with the recompile option, or using the hint option(recompile). If yous use these options, you should do extensive testing before rolling out to product.

Apply of temporary objects in SQL Server

You may find deferred compile frequently when using temporary tables and there are a number of query level hints available to reduce such blocker events acquired by the data modification counter, the recompilation threshold or update of statistics. Some of the options y'all can use are:

KEEP Program - The keep plan query hint (i.e. OPTION (Keep Program)) changes recompilation thresholds for temporary tables. At that place is no guarantee to keep the statement in the plan cache when a recompilation issue is plant while statistics update.
KEEPFIXED PLAN - To avoid program recompilation due to plan optimality related reasons (statistics update). Use the Pick (KEEPFIXED Plan) hint.
OPTIMIZED FOR - The Optimized For unknown (or for a specific value) gives the optimizer hint directly to the query optimizer to use the standard algorithms. The optimizer will await at all available data statistics to decide the value of variables used to generate a query plan. This means using this hint could be more intuitive when there are excessive recompilation events.

Server wide and database level configuration

Server and database wide configurations could as well impact recompilations. For example, the max server retentiveness configuration is used server wide and the caching mechanism is directly connected with the retention settings. SQL Server retentiveness is used based on the SQL Server version, OS and max server memory value. It is possible to have retention pressure internally or externally on the server. Due to that pressure level, it is possible to flush the stored query plan from SQL Server. It could affect server wide or database wide changes also. Trace flags can also impact the caching mechanism for example Trace Flag 8032 reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows the cache to be larger. Query execution configurations such every bit MAXDOP (parallel execution) and price threshold for parallelism could also be impacted due to internal caching mechanisms.

Next Steps

Test these diverse options on a test or development server prior to rollout in product.
Read these related articles:
- Plan caching in SQL Server
- Troubleshooting Excess Compilations
- Analyzing the SQL Server Plan Cache
- SQL Server 2016 memory pressure leads to the programme cache clearing

Bohannon Ento1995