Chapter 3 configure SQLServer (3) Configure "optimization of Real-Time Load"

During the first query or stored procedure, an execution plan is created and stored in the SQL server process cache memory. In many cases, we will execute some simple programs only once, and creating a stored procedure for these queries is a waste of memory resources. Insufficient memory may cause your cache to overflow, thus affecting performance.

During the first query or stored procedure, an execution plan is created and stored in the SQL server process cache memory. In many cases, we will execute some simple programs only once, and creating a stored procedure for these queries is a waste of memory resources. Insufficient memory may cause your cache to overflow, thus affecting performance.

Preface:

When the first query or stored procedure is executed, an execution plan is created and stored in the SQL server process cache memory. In many cases, we will execute some simple programs only once, and creating a stored procedure for these queries is a waste of memory resources. Insufficient memory may cause your cache to overflow, thus affecting performance. Before January 2005, this was a big problem to correct. In SQLServer 2008, Microsoft introducedInstantQuery the loadOptimizationFunction. This function is still available in 2012. Is based on the instance level.

Many developers run and test the query directly in the production environment. If the expected results are not obtained, the query is changed and then executed again, which puts a lot of pressure on the process cache. So try not to do this.

Preparations:

Before starting, clear the cache on the test server, but remember not to do this in the production environment:

1. First, check how much data is stored in the cache:

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0GO

The result is as follows:

2. Clear cache and buffer pool:

DBCC FREEPROCCACHE GO

3. If you want to check whether the clearing is successful, you can execute the statement in step 1 again:

Steps:

1. Execute the following statement:

USE AdventureWorksGOSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 43659GO

2. check whether there is a planned cache after the preceding statement is run, and then execute the previously queried plan cache statement again:

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0GO

3. The following is the result. Of course, you can also use like in The where condition to reduce the data volume to be searched: you can also use ctrl + alt + a to enable the activity monitor to find long-running queries.

4. Set Optimize for Ad hoc Workloads to 1:

 EXEC sp_configure 'optimize for ad hoc workloads', 1RECONFIGUREGO

5. Then clear the cache again:

DBCC FREEPROCCACHE GO

6. Execute the statement again:

USE AdventureWorksGOSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 43659GO

7. You can execute the following statement to check whether a new cache is entered:

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0        AND ST.text LIKE '%SELECT  *  FROM    Sales.SalesOrderDetail  WHERE   SalesOrderDetailID = 43659  %'        AND CP.cacheobjtype = 'Compiled Plan'GO

8. If no data exists, run the following statement again:

USE AdventureWorksGOSELECT  *FROM    Sales.SalesOrderDetailWHERE   SalesOrderDetailID = 43659GO

9. Use the following query check:

SELECT  CP.usecounts AS CountOfQueryExecution ,        CP.cacheobjtype AS CacheObjectType ,        CP.objtype AS ObjectType ,        ST.text AS QueryTextFROM    sys.dm_exec_cached_plans AS CP        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS STWHERE   CP.usecounts > 0        AND ST.text LIKE '%SELECT  *  FROM    Sales.SalesOrderDetail  WHERE   SalesOrderDetailID = 43659  %'        AND CP.cacheobjtype = 'Compiled Plan'GO

10. This time the following occurs:

Analysis:

When a new query is executed, the query_hash value is generated in the memory instead of the entire execution plan. When the same query is executed for the second time, SQLServer will check whether the query_hash already exists, if it does not exist, the execution plan is saved in the cache. In this way, the execution plan will not be saved to the cache for only one query. Therefore, we strongly recommend that you open thisConfiguration. ThisConfigurationIt does not have any negative impact, but it can save the space for caching the plan.

Generally, when you execute a query, an execution plan will be generated and saved in the process cache. Therefore, when you perform the query in step 1, you will see that the server has a lot of plan caches, however, when the query after step 6 is executed, no results are found. For ad hoc queries, if they are executed only once, why cache?

Some systems plan to cache more than GB, and may reduce the space by half after enabling. In addition, if you are curious about the space occupied by the ad hoc query, you can use the following statement:

SELECT  SUM(size_in_bytes) AS TotalByteConsumedByAdHocFROM    sys.dm_exec_cached_plansWHERE   objtype = 'Adhoc'        AND usecounts = 1