Using SQL Profiler to handle expensive queries

Original: Using SQL Profiler to handle expensive queries

When the performance of SQL Server becomes worse, the following two things are most likely to occur:

• First, some queries produce a lot of pressure on system resources. These queries affect the performance of the entire system because the server is not able to serve other SQL queries quickly enough.
• Additionally, expensive queries block other queries that request the same database resources, further reducing the performance of these queries. Optimizing expensive queries not only improves the performance of their own, but also reduces database congestion and SQL Server resource pressure, which improves the performance of other queries.

identify queries with greater overhead

The goal of SQL Server is to return the result set to the user within the shortest time. To do this, the SQL Server query optimizer generates a cost-effective query execution plan. The query optimizer calculates the weights of many factors, including the CPU, memory, and disk I/O usage required to execute the query-all from statistics generated by index maintenance or procedures. Typically, the lowest-cost plan has the fewest I/O, because I/O operations are costly.

The logical read supply indicates the memory pressure generated by the query. It also provides the disk pressure indicator because 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. The larger the number of logical reads of a query, the greater the likelihood of disk pressure. Excessive logical pages also increase the load that the CPU uses to manage these pages.

Queries that result in a large number of logical reads are usually locked on the corresponding large data set. Even reading requires a shared lock on all data. These queries block other queries that request modifications to the data, but do not block queries that read the data. Because these queries are inherently expensive and take a long time to execute, they continue to block other queries. The blocked query further blocks the query, introducing a blocking chain in the data.

Identifying expensive queries and optimizing them has the following meanings:

• Improve the performance of the expensive query itself;
• Reduce the overall pressure on the system resources;
• less database blocking;

The more expensive queries can be divided into the following two categories:

• Word execution: The single execution cost of the query is large;
• Multiple executions: The query itself costs little, but the repeated execution of the query leads to pressure on the system resources;

　　1, a single execution of large-cost query

You can parse the SQL Profiler trace output file to identify expensive queries. For example, if you are interested in identifying queries that perform a large number of logical reads, you should sort on the reads data column of the trace output.

• Captures profiler traces that represent a typical workload;
• Save the trace output to a trace file;
• Open the trace file for analysis;
• From the Events Selection tab, click the Organize Columns button to group trace output on the reads column.

　　

The trace output is as follows:

　　

In some cases, the high pressure on the CPU may be recognized from the System Monitor output. The pressure on the CPU may be due to a lot of CPU-intensive operations such as stored procedure recompilation, total functions, data ordering, hash joins, and so on. In this case, the profiler trace output should be sorted on the CPU column to identify queries that use a large number of processor cycles.

　　2. Execute expensive queries multiple times

Sometimes a query may not cost much, but the cumulative effect of multiple executions of the same query can cause pressure on system resources. Sorting on the reads column is not helpful for identifying this type of query. If you want to know the total number of reads that are performed by multiple executions of a query, it is unfortunate that the profiler does not provide assistance directly here, but you can still get this information in the following ways.

• Trace output in profiler is grouped by the following: EventClass, TextData, and reads. For groups of the same EventClass and TextData, the sum of all corresponding reads is calculated manually.
• Select File = "Save As =" in Profiler the trace table will be output to a trace table. You can also use the built-in function fn_trace_gettable and Profiler's trace file output to import into a trace table.
• Access the sys.dm_exec_query_stats DMV to retrieve information from the production server. This hypothesis is intended to deal with an immediate problem and is not concerned with historical issues.

After the trace input is saved to a file, the trace data is imported into a single table:

SELECT *  into tracetable  from:: fn_trace_gettable ('D:\123.trc',default)

Then execute the following statement:

SELECT COUNT(*) asTotalexecutions,eventclass,CAST(TextData as NVARCHAR(MAX)) TextData,SUM(Duration) asDuration_total,SUM(CPU) asCpu_total,SUM(Reads) asReads_total,SUM(writes) asWrites_total fromtracetableGROUP  byEventClass,CAST(TextData as NVARCHAR(MAX)) ORDER  byReads_totalDESC

The Totalexecutions column in the script indicates the number of times the query was executed, and the Reads_total column indicates the total number of read operations performed by the query multiple times. Note NTEXT does not support group by, so convert the type.

This method identifies more expensive queries than the profiler recognizes for a single execution with a larger query to better indicate the load. For example, a query that requires 50 read operations may execute 1000 times. The query itself is considered to be economical enough, but the execution of the read operation is always 50,000, which cannot be considered economic. Optimize this query to reduce the number of read operations, even if each execution is reduced by 10, the number of read operations will be reduced 10,000 times. This is more advantageous than optimizing a query that optimizes a 5,000-time read operation.

Getting the same information from the Sys.dm_exec_query_stats view requires only one query:

SELECTss.sum_execution_count,t.TEXT, Ss.sum_total_elapsed_time,ss.sum_total_worker_time,ss.sum_total_logical_reads,ss.sum_total_logical_writes  from(SELECTS.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 fromsys.dm_exec_query_stats sGROUP  bys.plan_handle) asSS CrossAPPLY sys.dm_exec_sql_text (ss.plan_handle) TORDER  bySum_total_logical_readsDESC

This is much easier than all the work required to collect trace data, so why use tracking data? The main reason for using tracing is accuracy. The sys.dm_exec_query_stats view is a flow total when a given plan already exists in memory, and the point of time is not accurate. On the other hand, tracking is the history of any time period that is running. You can even include traces in the database, and you have a series of data that can generate totals more accurately than you would rely on a given moment. However, the understanding of the positioning performance problem is the time when the query is running slowly, which is an irreplaceable occasion for sys.dm_exec_query_stats.

　　3. Identify slow-running queries

If the response time of a slow-running query becomes unacceptable, you should analyze the cause of performance degradation. But not all slow queries are caused by resource problems, and other factors that need to be cared for, such as blocking, can also cause slow queries.

To discover slow-running queries, group trace output on the Duration column.

　　

The trace output is as follows:

　　

For a system that is running slowly, you should pay attention to the continuous query time that runs slowly before and after the optimization process. After applying optimization techniques, the overall performance on the system should be calculated. Optimization steps can negatively affect other queries and make them slower.

Using SQL Profiler to handle expensive queries