Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of sql server performance tuning iPot O overhead, which is very detailed and has certain reference value. Friends who are interested must read it!

one。 Overview

IO memory is the most important resource of sql server. Data is loaded from disk to memory, then cached from memory, and output to the application side, which is introduced in sql server memory exploration. After understanding the principle of sqlserver memory, we can better analyze the cost of Icano and improve the overall performance of the database. One week after the sqlserver service of the database in the production environment is started, it can be analyzed and optimized through dmv. In this part, we can analyze it from the two aspects of physical IAccord O and memory I / O. The key analysis should be on the memory IAccord O, which may be analyzed from many dimensions, such as the total cost analysis of historical IAccord O since the startup of the sql server service, the total number of execution times since the compilation of the execution plan, the analysis of the average number of iUnix O, and so on.

Sys.dm_exec_query_stats: returns the cached query plan, and each query statement in the cache plan corresponds to a row in this view. When the sql server workload is too heavy, the dmv can also be statistically incorrect. If the sql server service restarts the cached data will be wiped out. This dmv includes too much information such as the number of memory scans, memory space, cpu time, etc., view the msdn document specifically.

Sys.dm_exec_sql_text: the returned SQL text batch, which is specified by sql_handle, where the text column is the text of the query.

1.1 ranking the top 50 by the number of pages physically read

SELECT TOP 50 qs.total_physical_reads,qs.execution_count, qs.total_physical_reads/qs.execution_count AS [avg I/O], qs. Creation_time, qs.max_elapsed_time, qs.min_elapsed_time, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset=-1 THEN LEN (CONVERT (NVARCHAR (max), qt.text)) * 2 ELSE qs.statement_end_offset END-qs.statement_start_offset) / 2) AS query_text, qt.dbid,dbname=DB_NAME (qt.dbid), qt.objectid, qs.sql_handle Qs.plan_handle from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt ORDER BY qs.total_physical_reads DESC

As shown in the following figure:

Total_physical_reads: the total number of physical reads scheduled to be performed during execution since compilation.

Execution_count: the number of times the plan has been executed since the last compilation.

[avg Icano]: the average number of physical times read (number of pages).

Creation_time: the time the plan was compiled.

Query_text: execute the sql script corresponding to the plan

The following includes the database ID:dbid, database name: dbname

1.2 ranking the top 50 according to the number of pages logically read

SELECT TOP 50 qs.total_logical_reads, qs.execution_count, qs.max_elapsed_time, qs.min_elapsed_time, qs.total_logical_reads/qs.execution_count AS [AVG IO], SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset=-1 THEN LEN (CONVERT (NVARCHAR (max), qt.text)) * 2 ELSE qs.statement_end_offset END-qs.statement_start_offset) / 2) AS query_text, qt.dbid Dbname=DB_NAME (qt.dbid), qt.objectid, qs.sql_handle, creation_time, qs.plan_handle from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) AS qt ORDER BY qs.total_logical_reads DESC

As shown in the following figure:

Briefly analyze the logical memory screenshot above:

In terms of the total amount of memory scans, the largest number of page scans is 8311268 page scans, and the t-sql script has been run 358 times since the compilation was executed. The time spent here is in milliseconds, including the maximum time consuming and the minimum time consuming. The average time consuming is 232115 times (page). The text of the statement is a update modification. The table has a large amount of data that is not completely indexed (after tradeoff, the statement is not indexed), but the number of execution is less. And each execution time is non-working time, although the scanning overhead is high, but it does not affect the use of customers during the day.

From the number of execution is 43188, the total number of memory scans ranks 39th. Although there are only 815 statements, it is executed many times, such as the server has pressure to optimize, generally, the statement does not go to the index. Take out the text as follows

SELECT Count (*) AS TotalCount FROM [MEM_FlagshipApply] WITH (NOLOCK) Where ([Status] = 2) AND ([IsDeleted] = 1)) AND ([MemType] = 0) AND ([MEMID] 6)

One of the following two figures is an analysis of the execution plan of the statement. Sqlserver indicates that the index is missing, and the other shows that iUnix has scanned 80 times.

Come and have a look after creating a new index.

CREATE NONCLUSTERED INDEX ix_1ON [dbo]. [MEM_FlagshipApply] ([Status], [IsDeleted], [MemType], [MEMID])

The above is all the contents of the article "sample Analysis of sql server performance tuning Iamp O overhead". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.