SQL query Performance Analysis

Original: SQL query performance analysis

Original source: http://blog.csdn.net/dba_huangzj/article/details/7623926

The performance of SQL query directly affects the value of the whole database, which must be treated solemnly.

SQL Server provides a variety of tools, and here's a simple introduction:

First, the SQL Profiler tool

SQL Profiler can be used to:

• Graphical monitoring of SQL Server queries;

L Collect query information in the background;

L Analysis performance;

L diagnose such problems as deadlocks;

l Debug Transact-SQL (T-SQL) statements;

L simulated replay SQL Server activity

Note: The most effective way to define a trace is through system stored procedures, but the starting point for learning is through the GUI.

1. 1, Profiler tracking:

Recommended use of standard templates

1. 2. Events:

An event that manifests the various activities performed in SQL Server. Can be simply categorized as: event classes, cursor events, lock events, stored procedure events, and T-SQL events.

For performance analysis, the following sections are primarily concerned:

What kind of CPU usage does SQL activity involve?

How much memory was used?

How many I/O operations are involved?

How long did the SQL activity execute?

How often does a particular query execute?

What kind of errors and warnings do queries face?

Events that trace the end of the query:

event class	Event	Description
Stored p Rocedures	rpc:completed	RPC Completion event
	sp:completed	Stored Procedure completion event
	sp:stmtcompleted	An SQL statement completion event in a stored procedure
TSQL	sql:batchcompleted	T-SQL Batch complete event
	sql:stmtcompleted	A T-SQL language Sentence Completion event

The RPC event indicates that the stored procedure was executed using the remote Procedure Call (RPC) mechanism through the OLE DB command. If a database application executes a stored procedure using a T-SQL EXECUTE statement, it will be converted into a SQL batch instead of a rpc,rpc usually faster than the execute request because they bypass many of the statement parsing and parameter handling in SQL Server.

The T-SQL batch is a set of SQL queries that are submitted together to SQL Server to end with go. Go is not a T-SQL statement, but is a SQLCMD usage program and Management Studio recognition. Symbolizes the end of the batch. A T-SQL batch consists of one or more T-SQL statements. Statements or T-SQL statements are also independent and discrete in stored procedures (hereinafter referred to as SP). Capturing individual statements with sp:stmtcompleted or sql:stmtcompleted events can be expensive. Be very careful when collecting, especially in the production environment.

Events that track query performance:

Event class	Event	Describe
Security Audit (Safety audits)	Audit Login (Login audit)	Log connections to a database when a user connects to SQL Server or disconnects
	Audit Logou (deregistration audit)
Seesions (session)	ExistingConnection (Existing connection)	Represents all users who are connected to SQL Server before the trace starts
Cursors (cursor)	CursorImplicitConversion (cursor implicit conversion)	Indicates that the type of cursor created differs from the requested type.
Errors and Warnings (Errors and warnings)	Attention (note)	Indicates a request interruption due to a client revocation query or database connection corruption
	Exception (Exception)	Indicates an exception occurred in SQL Server
	Execution Warnings (execution warning)	Indicates a warning occurred during query or SP execution
	Hash Warning (Hash warning)	Indicates an error occurred in the hash operation
	Missing column Statistics (row statistics missing)	Indicates that the optimizer requires a loss of column statistics for determining the processing strategy.
	Missing JOIN predicate (Connection assertion lost)	Indicates that the query executes without a connection assertion between the two tables.
	Sort Warnings (Sort warning)	Indicates that the sort operation performed in a query such as select does not have the appropriate memory.
Locks (Lock)	Lock:deadlock (Dead Lock)	Marked the advent of deadlocks
	Lock:deadlock Chain (Dead chain)	Displays the query chain that generated the deadlock
	Lock:timeout (lock timeout)	Indicates that the lock has exceeded its timeout parameter, which is set by the set Lock_timeout timeout_period (MS) command
Stored procedures (Stored procedure)	Sp:recompile (re-compiling)	Indicates that the execution plan used for a stored procedure must be recompiled because the execution plan does not exist, a forced recompilation, or an existing execution plan cannot be reused.
	Sp:starting (Start) Sp:stmtstarting (statement start)	Represents the beginning of an SQL statement in a sp:stmtstarting stored procedure and stored procedure, respectively. They are useful for identifying queries that begin but do not end because an operation causes attention events.
Transactions (business)	SqlTransaction (SQL Transaction)	Provides information about the database transaction, including the start/end time of the transaction and the duration of the transaction.

1. 3. Data column: The nature of the event. such as the class of the event, the SQL statement used for the event, the lock resource cost, and the event source.

data column	Description
EventClass (event class)	Event type, such as Sql:statementc ompleted
TextData	Event SQL statements
CPU	Event CPU overhead (ms)
Reads	is the number of logical read operations performed for an event.
writes	The number of logical write operations performed by an event.
Duration	Event Execution Event (MS)
SPID	The event's process ID
Str Attime	Event Start Event

Logical reads and writes consist of 8KB page activity in memory and may require 0 or more physical I/O. Locate the number of physical I/O operations, using the System monitoring tool.

Second, the automation of tracking

Note: SQL Profiler has a negative impact on performance and is not necessary for long-term use in a production environment.

1. Capturing traces using the GUI:

There are two ways to create scripted traces--manual or GUI:

You can export scripts using the profiler's export feature.

2. Use stored procedures to capture traces:

L sp_trace_create: Create a trace definition.

L Sp_trace_setevent: Add event and Event columns to the trace.

L Sp_trace_setfilter: Applies the filter to the trace.

You can use the built-in function: Fn_trace_getinfo to determine which traces are running:

SELECT * FROM:: fn_trace_getinfo (default);

You can use: Sp_trace_setstatus to stop a specific trace:

EXEC sp_trace_setstatus 1,0

-Stop the trace with ID 1.

After you turn off tracing, you must delete:

EXEC Sp_trace_setstatus

You can re-execute the fn_trace_getinfo function to confirm that it has been closed.

Three, combined tracking and performance Monitor output

You can combine SQL Profiler and Performance Monitor to analyze performance, not much here.

Iv. SQL Profiler Recommendations

Here are some things to consider when using SQL Profiler:

• Limit the number of events and data columns;

L Discard Start-up events for performance analysis;

L limit tracking output size;

L Avoid sorting online data columns;

L Run Profiler remotely

1. Limit events and Data columns:

You should be careful when capturing events such as locks and execution plans, because the output becomes very large and reduces SQL Server performance.

2. Discard the startup events used by the profiling:

Startup events like Sp:stmtstarting do not provide profiling information because only event completion can calculate the I/O volume, CPU load, and duration of the query.

The time to use snapping to start an event is to expect some SQL queries to be unable to end execution because of an error, or to catch a frequent discovery of attention events as they are pressed. Because the attention event generally indicates that the user canceled the query or the query timed out, the query may have been running for too long.

3, limit the tracking output size:

In the Edit Filter dialog box, make the following settings:

L Duration-greater than or equal:2 (continuous event >=2): Queries with persistent events equal to 0 or 1ms cannot be further optimized.

L Reads-greater than or equal:2 (number of read operations >=2): Queries with a logical read quantity equal to 0 or 1 cannot be further optimized.

4. Avoid sorting online data columns:

(1), capture tracking, do not do any sorting or grouping.

(2), save trace output to a trace file.

(3), open the trace file and sort as needed.

5. Run Profiler remotely:

Using system stored procedures is a performance advantage over using a GUI.

6. Restrict the use of certain events: Do not use SHOWPLAN XML events on systems that are already experiencing stress

V. Query performance metrics without profiler

For a system that requires immediate capture, using DMV:sys.dm_exec_query_stats is more effective than profiler, and tracking is still a better tool if you need to query the history of running the machine's individual overhead.

Sys.dm_exec_query_stats: Gets information about the query plan statistics on the server:

Column	Describe
Plan_handle	A pointer to a reference execution plan
Creation_time	Time the schedule was created
Last_execution time	Query the last time the schedule was used
Execution_count	Number of times the plan has been used
Total_worker_time	The CPU time that is planned to be used from the creation
Total_logical_reads	Number of read operations to be used since the creation of the plan
Total_logical_writes	Number of write operations to be used from the creation of the plan
Query_hash	A binary hash that can be used to identify queries with similar logic
Query_plan_hash	A binary hash that can be used to identify plans with similar logic

In order to filter the information, it is necessary to correlate other DMF. such as sys.dm_exec_sql_text to view the query text.

Sys.dm_query_plan displays the execution plan for the query. Thus restricting unnecessary return information.

Vi. queries with greater overhead

For the collection of results, two parts should be analyzed:

• Queries that lead to heavy system resource pressures;

L speed down the most serious queries

1. Identify expensive queries:

For the returned trace data, the CPU and reads columns show the query cost. When performing a read operation, the memory page must be backed up in an action query, written during the first data access, and moved to disk when the memory bottleneck occurs. Excessive page CPUs also add to the burden of managing pages.

Queries that result in a large number of logical reads are usually locked on the corresponding large data set. Even if read, a shared lock on all data is required. A query that blocked other requests for modification. Queries that do not block read data. If the query is long, the other queries continue to be blocked, and the blocked queries further block other queries, causing the blocking chains in the data.

conclusion, identify expensive queries and optimize them first to achieve the following effects:

L improve the performance of the expensive query itself;

l REDUCE the overall pressure on the system resources;

• Reduce database congestion;

There are two types of queries that cost a lot:

L Single execution: query cost is large at one time

L Execute multiple times: The query itself is small, but repeated execution causes pressure on the system resources.

1. Single execution of expensive queries:

You can use SQL Profiler, or query sys.dm_exec_query_stats to identify expensive queries.

(1), capture Profiler tracking that represents a typical workload.

(2), save the trace output to a trace file.

(3), open the trace file for analysis.

(4), open the Properties window for the trace, click the event Selection (Events Selection) tab.

(5), the stand-alone button opens the Organize Columns (Organization Column) window.

(6), group trace output on the reads column.

(7), use grouped tracking.

2. Execute more expensive queries multiple times:

In this case, the profiler traces the output in the following groups: EventClass, TextData, and reads.

L Export Profiler Tracking table. Use the built-in function fn_trace_gettable to import to a trace table.

L Access SYS.DM_EXEC_QUERY_STATSDMV retrieve information from the production server.

Load data into a table in the database

SELECT  *into    trace_tablefrom    ::        fn_trace_gettable (' C:\PerformanceTrace.trc ', DEFAULT)

Execute the following statement to query the total number of read operations performed multiple times:

SELECT  COUNT (*) as totalexecutions,        EventClass,        TextData,        sum (Duration) as duration_total,        sum (CPU) As Cpu_total,        sum (Reads) as Reads_total,        sum (writes) as Writes_totalfrom    Trace_tablegroup by EventClass, C8/>textdataorder by Reads_total DESC

SQL Server does not support the NTEXT the data types are grouped. and TextData is the ntext type, to be converted to Nvarchar( Max)

SELECT  Ss.sum_execution_count,        t.text,        ss.sum_total_elapsed_time,        ss.sum_total_worker_time,        ss.sum_total_logical_reads,        ss.sum_total_logical_writesfrom    (SELECT    s.plan_handle,                    sum ( S.execution_count) Sum_execution_count,                    sum (s.total_elapsed_time) sum_total_elapsed_time,                    sum (s.total_ Worker_time) Sum_total_worker_time,                    sum (s.total_logical_reads) sum_total_logical_reads,                    sum (s.total_ logical_writes) sum_total_logical_writes          from      sys.dm_exec_query_stats s          GROUP by  S.plan_ Handle        ) as SS Cross        APPLY sys.dm_exec_sql_text (ss.plan_handle) Torder by Sum_total_logical_readsdesc

3. Identify slow-running queries:

You need to periodically monitor the execution time of the input SQL queries and find out the response time for slow queries. But not all slow-running queries are due to resource problems. such as blocking those are likely to cause slow queries.

Can be traced on the duration.

Vii. Plan of Implementation

1. Analysis and query plan

The execution plan reads from right to left, from top to bottom. Each step represents the action taken to get the final output of the query. The execution plan has the following characteristics:

If the query consists of batches of multiple queries, the execution plan for each query is displayed in the order in which they are executed. Each execution in the batch will have a relative estimated cost, with a total cost of 100% for the whole batch.

L each icon in the execution plan represents an operator. There is a relative estimate overhead, and the total cost for all nodes is 100%.

A starting operator in an execution plan typically represents a data retrieval mechanism for a database object (table or index).

Data retrieval is usually a table operation or an index operation.

The data retrieval on the index will be an index scan or an index lookup.

The naming convention for data retrieval on indexes is [table name]. [Index name].

The data flows from right to left between two operations, represented by a connecting arrow.

The width of the join arrows between the L operators is a graphical representation of the number of transmitted rows.

The connection mechanism between the two operators in the same column will be a nested loop connection, a hash match or a merge connection.

L Place the cursor on one node of the execution plan and display a pop-up window with some details.

l have a complete set of details about the operator in the Properties window. You can right-click the operator and select Properties.

The L operator details show the type of physical and logical operations at the top. Physical operations represent the actual use of the storage engine, and logical operations are the structures that the optimizer uses to establish an estimate execution plan. If the same, only physical operations are shown. Additional information is displayed: I/O, CPU, and so on.

The argument (parameters) section of the L operator Details pop-up window is particularly useful in analysis because it shows the filter or join conditions used by the optimizer lock.

2. Identify the more expensive steps in the execution plan:

Each node in the execution plan shows the relative overhead in the overall plan, with an overall plan total cost of 100%. Focus on the node with the highest relative overhead.

The execution plan may come from a batch of statements, so you might also need to find the most expensive statement.

L View the width of the connection arrows between nodes. A very wide connection arrow indicates that a large number of rows are transferred between the corresponding nodes. Analyze the nodes to the left of the arrows to understand why you need so many rows, and also check the properties of the arrows. You may see that the estimated rows are different from the actual rows, which can be caused by outdated statistics.

L Find Hash connection operation. For small datasets, nested loops are often the preferred connection technology.

L Look for bookmark lookup operations. Bookmark operations for large result sets can cause a lot of logical reads.

L If there is an exclamation mark warning on the operator, it is an area that needs immediate attention. These warnings can be caused by a variety of problems, including connections that do not have a connection condition, or indexes and tables that have lost statistics.

L need to find the steps to perform the sort operation, which means the data is not retrieved in the correct order.

3. Analyze Index Validity:

To focus on "scanning," the scan represents access to a large number of rows. Index validity can be judged in the following ways:

L Data Retrieval Operations

L Connection operation

Sometimes there is no "assertion" (predicate) in the execution plan, and a lack of assertions means that the entire table (the clustered index is the table) is scanned as input to the merge Join operator.

4. Analyze Connection Validity:

SQL Server uses the 3 connection type:

l Hash Connection;

L Merge Connections

L Nested Loops Join

1. Hash Connection:

1.1, hash connection efficient processing of large, unsorted, non-indexed input.

1.2. Hash connection uses two connection inputs: build input (build) and probe input (probe input). The build input is the one above the execution plan, and the probe input is the following input.

1.3, the most common method of hash connection--in-memory hash join, the entire establishment of input is scanned or computed and then in memory to build a hash table. Each row is inserted into a hash table based on the calculated hash key value (a set of columns in an equality assertion).

Memory Hash Connection:

2. Merge Connection:

2.1. A merge connection requires that two inputs be sorted on the merged column, which is defined in the join condition. If two connections have indexes, then the connection input is sorted by that index. Because each connection input is sorted, the merge sort gets a row from each input and compares the equality. If equal, the matching row is generated. The process is repeated until all rows have been processed.

2.2. If the optimizer discovers that the connection input is sorted on its connection column, the merge connection is selected faster than the hash connection.

3, nested loop connection:

3.1, always access a limited number of rows from a separate table, in order to understand the effect of using a smaller result set, reduce the connection input in the query.

3.2. Use a connection input as an external (outer) input table. Another as an internal (inner) input table. The external table is the input above the execution plan, and the inner table is the input below. The outer loop consumes the external input table row by line. The inner loop executes once for each outer row, searching for the matching rows of the internal input table.

3.3, if the external input is quite small, internal input large but indexed, nested loop connection is very efficient. Connections increase speed by sacrificing other aspects-using memory to get a small data set and quickly comparing it to a second data set. Merge sort is similar to this, using memory and a small portion of tempdb to sort, hash connections using memory and tempdb to build a hash table.

3.4, although the loop connection is faster, but as the data set becomes larger, consumes more memory than hash or merge. So SQL Server uses different schedules for different data sets.

Features of 3 connection types:

Connection type	Index on a connection column	The general size of the join table	Pre-order	Join clause
Hash	Internal table: No index required External tables: Optional Best conditions: Small external tables, large internal tables	Any	Don't need	Equi-join
Merge	Internal/External tables: Must Best condition: Two tables have clustered indexes or overwrite indexes	Big	Need	Equi-join
Nested loops	Internal table: Must External table: preferably with	Small	Options available	All

Note: in hash and nested loops joins, the external table is generally the smaller of the two join tables.

5. Actual execution plan vs estimated Execution plan:

Estimating the execution plan cannot be generated for temporary tables.

6. Plan the cache:

It is generally saved in memory space. You can use the DMV to query:

SELECT  P.query_plan,        t.textfrom    sys.dm_exec_cached_plansr cross        APPLY sys.dm_exec_query_plan ( R.plan_handle) p Cross        APPLY sys.dm_exec_sql_text (r.plan_handle) t

Viii. Query Overhead

1, Customer statistics: The computer as a client of the server, from this point of view to emit capture execution information.

Click "Query" → "Include customer statistics" in SSMs, but this step is not a good collection method. Sometimes you need to reset: "Query" → "Reset Customer statistics"

2. Execution Time:

Both the duration and CPU represent the time factor for the query, and you can use set STATISTICS time to get execution times.

The CPU time of the last line equals the CPU value of the profiler, and the elapsed time represents the duration value. 0 milliseconds of analysis and compilation time illustrates the reuse of the execution plan. Can do: DBCC Freeproccache clears the cache. But do not execute on the production system, because in some cases this is the same as the cost of restarting.

3. STATISTICS IO:

The number of reads from the reads column obtained by the profiler is the most important of the duration, CPU, reads, and writes factors. When interpreting the output of the statistics IO, it is most likely to refer to the "logical read" operation. Sometimes the scan count is also referenced. The physical read operation and the number of read-ahead will not be 0 when the data cannot be found in memory, but once the data is filled into memory, the physical read and pre-read will tend to be 0. During optimization, you can monitor the number of read operations on a single table to ensure that the data access cost of the table is actually reduced.

SQL query Performance Analysis