Start by explaining which parts of SQL Server memory footprint are made up of. The memory consumed by SQL Server consists primarily of three parts: data caching (database buffer), execution caching (Procedure cache), and SQL Server engine programs. The caching of SQL Server engine programs is generally relatively small, and the primary focus of our memory tuning is on the control of data caching and execution caching. This article mainly describes the implementation of caching tuning. The tuning of the data cache is described in another article.
For reducing the footprint of cache execution, memory footprint can be reduced primarily by using parameterized queries.
1, using parameterized query to reduce the execution of cache consumption
We use the following example to illustrate the effect of using parameterized queries on cache occupancy. To facilitate the experiment, we used a SQL Server with no other load to do the following experiments.
The following script loops through a simple query and executes it 10,000 times.
First, let's clear the cache that SQL Server has already occupied:
DBCC FREEPROCCACHE
Then, execute the script:
Copy Code code as follows:
DECLARE @t datetime
SET @t = getdate ()
SET NOCOUNT on
DECLARE @i int, @count int, @sql nvarchar (4000)
SET @i = 20000
While @i <= 30000
BEGIN
SET @sql = ' SELECT @count =count (*) from p_order WHERE Mobileno = ' + cast (@i as varchar (10))
EXEC sp_executesql @sql, N ' @count INT output ', @count output
SET @i = @i + 1
End
PRINT DATEDIFF (Second, @t, Current_timestamp)
Output:
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
11
Use 11 seconds to complete the 10,000 query.
Let's take a look at the query plan that is occupied in the SQL Server cache:
Select Count (*) cnt,sum (size_in_bytes) totalsize
From Sys.dm_exec_cached_plans
Query results: A total of 2,628 execution plans are cached in SQL Server. The cache they occupy is up to:
92172288 bytes = 90012KB = MB.
We can also use the DBCC MEMORYSTATUS command to check the SQL Server's execution cache and data cache footprint.
The results of the implementation are as follows:
The execution cache takes up 90088KB, 2,629 queries are planned in the cache, and 1489 pages of free memory (8KB per page) can be used by the data cache and other requests.
Let's revise the previous script and then execute the DBCC FREEPROCCACHE again. Execute the modified script again:
Copy Code code as follows:
DECLARE @t datetime
SET @t = getdate ()
SET NOCOUNT on
DECLARE @i int, @count int, @sql nvarchar (4000)
SET @i = 20000
While @i <= 30000
BEGIN
SET @sql = ' Select @count =count (*) from p_order WHERE Mobileno = @i '
EXEC sp_executesql @sql, N ' @count int output, @i int ', @count output, @i
SET @i = @i + 1
End
PRINT DATEDIFF (Second, @t, Current_timestamp)
Output:
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
1
That is, the 10,000 queries were completed in only 1 seconds.
Let's take a look at the query plan in Sys.dm_exec_cached_plans:
Select Count (*) cnt,sum (size_in_bytes) totalsize from Sys.dm_exec_cached_plans
Query Result: A total of 4 execution plans are cached. They share memory: 172032 bytes = 168KB.
If you perform DBCC MEMORYSTATUS, you get the result:
There are 12875 pages of free memory (8KB per page) that can be used by the data cache.
Here, we have seen a fairly clear result of the contrast. In reality, the former in this example is often used as a way of executing a SQL script (for example, by merging a string in a program into a SQL statement and then passing in SQL Server execution via ado.net or ADO).
Explain why:
We know that SQL statements are first compiled and optimized by the query optimization engine prior to execution, resulting in an optimized execution plan and then executed according to the execution plan. SQL Server can reuse execution plans for an overall similarity, just a different SQL statement. For different SQL statements, however, SQL Server cannot reuse the previous execution plan, but rather recompile a new execution plan. Also, SQL Server does not actively purge previously saved query plans when memory is sufficient for use (note: SQL Server periodically cleans up query plans that are no longer used for long periods of time). Thus, the way different SQL statements are executed will significantly affect the number of query plans stored in SQL Server. If you qualify SQL Server's maximum available memory, too many unwanted execution plans are consumed, resulting in a decrease in available memory for SQL Server, which can result in more memory paging with the disk when the query is executed, especially when large queries occur. If the maximum available memory is not qualified, SQL Server consumes more memory because of reduced available memory.
In this case, we can generally implement parameterized queries in two ways: one is to use stored procedures to execute SQL statements as much as possible (which in reality has become a principle of SQL Server DBA), and the second is to use sp_executesql Way to execute a single SQL statement (be careful not to use sp_executesql as the first example above).
In the reality of the same software system, a large number of load types are often similar, the difference is only the specific parameters of each pass in the different values. Therefore, it is necessary and possible to use parameterized queries. In addition, as we can see from this example, because of the use of parameterized queries, not only to optimize the SQL Server memory footprint, but also because of the ability to reuse the previously compiled execution plan, so that the subsequent execution does not need to compile, the final execution of 10,000 queries in total only use 1 seconds.
2. Check and analyze execution plan in SQL Server execution cache
With the above description, we can see the amount of memory the SQL cache occupies. I also know that the contents of the SQL Server execution cache are primarily the execution plans for various SQL statements. To optimize the cache, you can analyze and locate the problem by analyzing the execution plan in the cache to see what is useful and what is a useless execution plan.
By querying DMV:sys.dm_exec_cached_plans, you can understand the caching situation in the database, including the number of times it was used, the type of cache, the amount of memory consumed, and so on.
SELECT usecounts, Cacheobjtype, Objtype,size_in_bytes, plan_handle
From Sys.dm_exec_cached_plans
The plan_handle of the cache plan allows you to query the execution plan details, including the corresponding SQL statement:
SELECT Top Usecounts,
ObjType
P.size_in_bytes,
[SQL]. [Text]
From Sys.dm_exec_cached_plans P
OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) SQL
ORDER BY Usecounts
We can choose to focus our analysis on those SQL statements where the execution plan consumes a larger amount of memory and is less frequently reused . See if the way it is invoked is reasonable. In addition, you can analyze the execution plan's SQL statements that have been reused more frequently to see if the execution plan has been optimized. Further, through the analysis of the query plan, we can find some SQL statements which occupy the most IO, CPU time and the most execution times according to the need , and then make the corresponding tuning analysis. Space is limited, here is not to introduce too much. Readers can refer to the Books Online:sys.dm_exec_query_plan content gets help.
Report:
1: For DBCC MEMORY, you can view Microsoft's Knowledgebase : http://support.microsoft.com/kb/907877/EN-US
2: For Sys.dm_exec_cached_plans and sys.dm_exec_sql_text, see Books Online.
