17th-Configure SQL Server (3)-Configure "Optimize for immediate load"

Original: 17th--Configure SQL Server (3)--Configure "optimize for immediate load"

Objective:

The execution plan is created and stored in the process cache memory of SQL Server the first time the query or stored procedure is executed. In many cases, we execute simple programs that execute only once, and creating stored procedures for these queries is a waste of memory resources. Due to insufficient memory, you may be able to overflow your cache, which can affect performance. Before 2005, this was a big problem, in order to correct the problem. Microsoft introduced an optimization feature for the immediate query payload in SQL Server 2008. This feature is still available in 2012. is based on the instance level.

Many developers run and test queries directly in the production environment, and if they do not get the expected results, they change the query and execute again, which can be stressful for the process cache. So try not to do so.

Preparatory work:

Before you begin, empty the cache on the test server, but remember not to do so in a production environment:

1. First look at 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 results are as follows:

2. Empty the cache and buffer pool:

DBCC Freeproccache GO

3. If you want to check if the empty 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 if there is a plan cache after running the above statement, and execute the query plan cache before executing the 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 amount of data found: You can also use Ctrl+alt+a to turn on activity Monitor to find long-running queries.

4. Now to set the optimize for Ad hoc workloads to 1:

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

5. Then empty 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 if there is a new cache entry:

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. You will find no data inside, now execute 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 appeared the following:

Analysis:

When the new query executes, the Query_hash value is generated in memory, not the entire execution plan, and when the same query executes for the second time, SQL Server finds out if the Query_hash already exists, and if it does not, the execution plan is saved in the cache. This makes the query that executes only once will not save the execution plan to the cache. Therefore, it is highly recommended to open this configuration. This configuration does not have any negative effects, but it can save space for the plan cache.

In general, when you execute a query, the execution plan is generated and saved in the procedure cache, so when you perform step 1 of the query, you see that the server has a lot of scheduled caches, but when the query after the sixth step is executed, it is discovered. For ad hoc queries, why do you need a cache if you only execute it once?

Some systems have a planned cache of more than GB, which may reduce the space by half when turned on. In addition, if you are curious about how much space an ad hoc query occupies, you can use the following statement:

SELECT  SUM (size_in_bytes) as Totalbyteconsumedbyadhocfrom    sys.dm_exec_cached_planswhere   objtype = ' Adhoc ' and        usecounts = 1

17th-Configure SQL Server (3)-Configure "Optimize for immediate load"