AUTO_SHRINK is a database option in SQL Server. When you enable this option for a database, this database becomes eligible for shrinking by a background task. This background task evaluates all databases that satisfy the criteria for shrinking and shrink the data or log files.
You have to carefully evaluate setting this option for the databases in a SQL Server instance. Frequent grow and shrink operations can lead to various performance problems.
If multiple databases undergo frequent shrink and grow operations, then this will easily lead to file system level fragmentation. This can have a severe impact on performance. This is true whether you use the automatic settings or whether you manually grow and shrink the files frequently.
After AUTO_SHRINK successfully shrinks the data or log file, a subsequent DML or DDL operation can slow down significantly if space is required and the files need to grow.
The AUTO_SHRINK background task can take up resources when there are many databases that need shrinking.
The AUTO_SHRINK background task will need to acquire locks and other synchronization that can conflict with other regular application activity.
Consider setting databases to a required size and pre-grow them. Leave the unused space in the database files if you think the application usage patterns will need them again. This can prevent frequent shrink and growth of the database files.
for any database, auto shrink should not be activated based on the following reasons:
Executing the SQL Server auto shrink algorithms without any purpose will definitely going to squander the resources in a massive quantity.
Whether you perform SQL Server auto shrink or manual shrink, it will obviously be going to cause the index fragmentation, and this eventually executes the data files shrinking also.
If the server is also forcing the boundaries of the IO subsystem, running shrink may push it over, resulting in long disk queue lengths and possibly IO timeouts, this will eat up Server IO and CPU resources in a massive quantity.
System performance will be retarded by file system disk-level fragmentation, which is again the direct effect of doing shrinking and growing data files often.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small, compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This option might help to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that aren't reused. However, enabling this option might affect your ability to troubleshoot single-use plans.
The compiled plan stub allows the Database Engine to recognize that this ad hoc batch was compiled previously, and only stores a compiled plan stub. When this batch is invoked (compiled or executed) again, the Database Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.
You can find compiled plan stubs by querying the sys.dm_exec_cached_plans catalog view and looking for "Compiled Plan" in the cacheobjtype column. The stub has a unique plan_handle. The compiled plan stub doesn't have an execution plan associated with it, and querying for the plan handle doesn't return a graphical or XML showplan.
Recommendations
Avoid having a large number of single-use plans in the plan cache. Common causes include:
Data types of query parameters that aren't consistently defined. This particularly applies to the length of strings but can apply to any data type that has a maxlength, a precision, or a scale. For example, if a parameter named @Greeting is passed as nvarchar(10) on one call and nvarchar(20) on the next call, separate plans are created for each parameter size.
Queries that aren't parameterized. If a query has one or more parameters for which hard-coded values get submitted to the Database Engine, a large number of query plans could exist for each query. Plans could exist for each combination of query parameter data types and lengths that were used.
If the number of single-use plans take a significant portion of SQL Server Database Engine memory in an OLTP server, and these plans are ad hoc plans, use this server option to decrease memory usage with these objects.
If the optimize for ad hoc workloads option is enabled, you can't view execution plans for single-use queries, because only the plan stub is cached.
SELECT objtype AS [PlanCacheType],
COUNT_BIG(1) AS [NumOfPlans],
SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [PlanSizeInMB]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY NumOfPlans desc
GO
No comments:
Post a Comment